Find Unique cell in a column.

Teamciccone30576

Hi @Genevieve P.

This is strange!

 I have studied this formular for a very long time and I cannot see any difference.

Hope you can spot something!

See the screen print.

Notice it does not box in the source column and it does not box in the Help cell. The variables are not color coded either.

When I type the formular in, I get red line under source range and under the HELP@row.

=IFERROR(INDEX(DISTINCT(COLLECT(Source:Source, Source:Source, @Cell, <>"")), HELP@row), "")

(The red line disappears after copy – so I made the text bold where the line was.)

That why I tried to put row numbers after the column names and I put a space between Help and @, – then no red line, but it still did not execute.

I also tried to type the formular in again – the Color code on the source range goes away after I <> and the red line under the source range comes back.

Hope you can spot something.

Kurt

Genevieve P.

Hi @Teamciccone30576

It's the @cell reference!

@cell needs to be all lower case for it to work. I see that you have @Cell with a capital C, which will break a formula.

There also should not be any space between Help and @row, so you've done it correctly:

Help@row

As a final note, check where your commas are placed. There should not be a comma after the @cell because you're making a statement - the cell is not blank.

=IFERROR(INDEX(DISTINCT(COLLECT(Source:Source, Source:Source, @cell <>"")), Help@row), "")

Genevieve P.

To make this easier, we can actually remove out the COLLECT completely, and get rid of all those pesky, small details.

=IFERROR(INDEX(DISTINCT(Source:Source), Help@row), "")

Teamciccone30576

Hi @Genevieve P.

Thanks, your help was great!

I guess, I am not observant enough – looking at that formular over and over again and I did not spot the ”C” vs “c”.

Cost me a lot of time.

I thank you for all the assistance and I thank you for the new short, and elegant solution you just proposed.

I tested it in my test sheet – works great!

I then tested it on the actual two sheets set-up for the first 20 entries using references to the Main sheet and it works great!

 Now I just need to find an easy way to add 300 sequential numbers in the Help column and I will be set.

 But I will figure that out.

Again, your help was great. Thanks

 

Kurt

Genevieve P.

Hey @Teamciccone30576

I’m so glad to hear it worked for you!

For the numbers, add a 1 in row 1 and a 2 in row two. Then select both cells and use drag-fill to drag down the numbers down the column. It will automatically fill in the column with sequential numbers!

Cheers,

Genevieve

Teamciccone30576

Hi @Genevieve P.

I originally, I thought I could not use that method, because if we add a street on the main sheet, I thought the numbers would be corrupted.

But when I tried this for an hour ago, to my surprise, it does work.

When I add a street with a few addresses, it automatic corrected the “Help” numbers and automatic added the “Street name” and the count of addresses on that street.

 Great

Smartsheet is not as stupid as I thought.

Kurt