INDEX consecutive row indexing

Mike TV

I've had this issue come up a bunch for me when trying to write formulas. Say I've got a column of Order Numbers that have a lot of repeated numbers in it. I can write a formula to pull out distinct numbers into another sheet such as this formula:

=INDEX(DISTINCT({Order Number}), 1)

However, that's only going to pull out the first one into my new sheet. What if I have over 100 distinct order numbers I want to pull into a new sheet? Currently I only know of one way to do it. By changing the row index number at the end to 2, then 3, then 4, and it can take an hour or more to put that formula into over 100 rows.

Is there a faster way to pull out the distinct numbers aside from manually telling SmartSheet to give me the first result, then the second, then the third, all manually?

Paul Newcome

You can insert a text/number column and then manually enter

1

2

From there you can dragfill down as far as you need to go and replace the hardcoded number in the formula with a cell reference to this new column.

Mike TV

@Paul Newcome

@Andrée Starå

Hi guys. Do either of you know of an easy solution for this type of referencing large lists?

Paul Newcome

You can insert a text/number column and then manually enter

1

2

From there you can dragfill down as far as you need to go and replace the hardcoded number in the formula with a cell reference to this new column.

Mike TV

@Paul Newcome

Thanks Paul. I'll try that next time I have a need to pull in a long list like this. Yesterday I just did it manually.

Mike TV

@Genevieve P.

Um...where's the option to mark a response as the answer. I'm not seeing that anywhere...

Genevieve P.

Hey @Mike TV

Thanks for the tag! It looks like you had originally posted this as a Discussion, which does not have accepted answers. We've changed this to be a Question type of post, so you should now see the "yes" and "no" at the bottom. 🙂

Cheers,

Genevieve

Mike TV

@Genevieve P.

Thanks. I've only posted a few posts myself and didn't know there was a difference.

Genevieve P.

No problem! You found the difference 😎

Questions will appear in the "Unanswered" queue as well, whereas Discussions don't require a solution.

Paul Newcome

Happy to help. 👍️