Find Unique cell in a column.

Teamciccone30576

We are trying to move some Excel sheets into Smartsheet!

In Excel, we have an address list in a sheet named “Main” and another sheet named “Street“.

We extract the unique street names from “Main” and list them in “Street” and we count the number of duplicates of the streets name.

My little sample has 7 addresses – the actual sheet has 4000.

(Street Number and Street Names are in separate columns.

In Excel sheet “Main” we have an address list:

     Column A      Column B

1 A St.

2 A St.

1 B Dr.

2 B Dr.

1 C Way

2 C Way

3 C Way

In another sheet in the same workbook, named “Streets” we include the following formular to find the Unique Street names. That is easy in Excel:

In “A1” cell we have:

=UNIQUE(Main!B1:B7)

When hits Return, it will list all street names.

But I cannot figure out how to duplicate the UNIQUE function in Smartsheet.

I am playing around with a combination of DISTINGT, MATCH and INDEX but I have not been able to get anything that will work.

Can anyone help?

The second half was easier.

That is where we count the number of addresses on each street.

In Excel, in the A1 cell type:

=COUNTIF(Main!A1:A11,A1)

That will count the residence on the first street.

Now we replicate this formular all the way to the last street name.

                =COUNTIF(Main!A1:A11,A11)

In Smart Sheet this works OK:

                               =COUNTIF({Reference Range 1}, (Street1))

And then replicate this formular through the column.

Hope someone can point me in the right direction on the first part

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

Scott Peters

Helo @Teamciccone30576 - You may find this post useful:

https://community.smartsheet.com/discussion/87186/list-all-unique-values-from-a-column-within-one-smartsheet-in-a-separate-smartsheet

Teamciccone30576

Thanks for replying to my question!

But the article you refer to does not, as far as I can see, solve the problem.

For example, using the Join statement produces a count of the unique street and that is not what I am looking for.

And I cannot use reports either, since the sheet is used for other purposes as well.

I seem unreal that we can  duplicate the function from Excel – “=Unique(Main!B1:Bx)”

I solved the problem temporary by export the Main to Excell and create a new street list using the Excel formular and finally import the street list back to Smartsheet.

But any changes in “Main” will not be reflected in the “Street” list so any changes will render the street list useless.

So, it is really not a solution to my problem.

Anyone have any ideas?

Scott Peters

I agree, and I wish for the same solution in Smartsheet. Our company has not upgraded to the newer version of Excel, so we do not have the UNIQUE function, either. I would recommend submitting a Smartsheet Enhancement Request

Paul Newcome

You will need a helper column in the destination sheet that is prefilled with the numbers 1 through however many you will need. Then you would use this to pull in the unique list:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Column To Pull}, {Source Sheet Column To Pull}, @cell <> "")), [Helper Column]@row), "")

Teamciccone30576

Attention: Paul Newcome

Looks like it is an easy way to solve the problem.

I tried to follow your suggestion - I cannot get this to work -What do I do wrong?

I created a new small test sheet with 3 columns.

·        Source – Help – Unique

Ø Source =Source Sheet Column to Pull.

Ø Help= Helper Column.

Ø Unique = where the formular is located and the Unique streets hopefully would be listed.

Source column contains 20 Streets – 3 different names.

Help contains numbers from 1 to 20.

This is the formular you provided, modified to the actual column names.

=IFERROR(INDEX(DISTINCT(COLLECT({(Source1:Source20)}, {Source}, @cell <> "")), [Help @row), "")

What do I do wrong?

(I did experiment with different parentheses etc. )

Teamciccone30576

Thanks for replying to my question!

But the article you refer to does not, as far as I can see, solve the problem.

For example, using the Join statement produces a count of the unique street and that is not what I am looking for.

And I cannot use reports either, since the sheet is used for other purposes as well.

I seem unreal that we can  duplicate the function from Excel – “=Unique(Main!B1:Bx)”

I solved the problem temporary by export the Main to Excell and create a new street list using the Excel formular and finally import the street list back to Smartsheet.

But any changes in “Main” will not be reflected in the “Street” list so any changes will render the street list useless.

So, it is really not a solution to my problem.

Anyone have any ideas?

Paul Newcome

@Teamciccone30576 I'm having trouble following your setup. Are you able to provide some screenshots?

Eric Law

@Teamciccone30576 Please use the @ and handle of the people you are conversing with as that will notify them when you respond to them.

Paul Newcome

Eric,

Unless someone unsubscribes from the notifications, anyone that has commented on the thread will still get a notification that a new comment has been made even if they have not been "@ mentioned".

Teamciccone30576

@Poul Newcome

Because the actual sheet 4000 addresses and close to 300 streets on one sheet, and on another sheet we have the list of UNIQUE streets.

Therefore, I made a small test sheet with 20 street – w/4 Unique names, all on one sheet,- in order to test the formular before I used it on the actual file.

I include a screen shoot which shows the Excel version side by side with the Smartsheet version.

Again the Formular used in Column UNIQUE is as follows:

=IFERROR(INDEX(DISTINCT(COLLECT({Source1:Source20},{Source},@cell <> "")), [Help @row), "")

Kurt

Genevieve P.

Hey @Teamciccone30576

It looks like you're just missing a closing ] around the word "Help" as a column reference. Actually, since Help is just text, we don't need any [ ] at all.

Try it again:

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

Keep in mind that the {references} will need to be created by referencing a column in another sheet through a pop-up window... it actually looks like you might want the {reference} to be the same:

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

See: Formulas: reference data from other sheets

Cheers,

Genevieve

Teamciccone30576

Hi @Geneviewer P

Thanks for taking time out to comment on my problem.

But the removal of the bracket did not solve the problem.

FYI, I set up the formular test sheet on one page – meaning that the “Source” column is on the same sheet as the “Help” column and the formular column “UNIQUE” - just to make the test simple.

It makes no difference if 1 use 2 sheets with the reference method.

Kurt

Genevieve P.

Hi @Teamciccone30576

Thanks for letting us know that you're testing on the same sheet! In this case, your references need to be set up differently. Instead of using {cross sheet references}, you'll want to use [square brackets] or just the column name.

Try this as your test:

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

Here's more information:Create a cell or column reference in a formula

Cheers,

Genevieve

Teamciccone30576

Hi @Genevieve P.

Thanks again for your continued interest.

I tried you formular exactly as you typed it, and a variation with the row # added (Source1:Source20)

It still gives me #UNPRARSEABLE.

I tried a number of variations of this, but it does not execute!

So, I am still searching for a solution.

Genevieve P.

Hey @Teamciccone30576

Can you show us what you're putting in the cell? Not the error in the cell but the formula open, like this:

I would suggest building the formula IN Smartsheet versus copy/pasting the data. This way you can click on the cells when you need to reference information and it will pull in the correct column name 🙂