Generate List of Unique Values - INDEX? DISTINCT? VLOOKUP?

alex44

I'm trying to have Smartsheet create a list of unique data *without* using Reports. Help (-: In the Names table in the screenshot, you can see that there are names manually entered a number of times in random order. That is fine and good. What I'm trying to do is is have a second sheet have a "Names - Unique" column which *generates* a list of names based on a formula looking back to the "Names" column to find unique values. If the Names - Unique column could be alphabetically ordered, that would be nice but not required. 

I've looked at INDEX, MATCH, VLOOKUP, DISTINCT, etc. and can't seem to solve this. I've also looked how this can be done in Excel (which can be accomplished through an array formula or a LOOKUP formula) but these approaches do not apply in Smartsheet.

Thoughts?

Paul Newcome

Here is what I would suggest:

 

On your Master Sheet, add in a Helper column of the text/number type. In row 1, enter the following...

 

=COUNT(DISTINCT(Names$1:Names@row))

 

then in row 2 enter

 

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names1)), COUNT(DISTINCT(Names$1:Names@row)))

 

and dragfill that down the rest of the rows.

.

In your Unique Names sheet, you would then add a column where you simply enter 1, 2, 3, 4, 5, etc... basically entering the row number. We will call this column [Row ID] for this example. You will want to extend beyond what you anticipate, but we can build in an Alert that lets you know you need to add new rows if needed/preferred.

 

Then in the Unique Names column, you would enter something along the lines of...

 

=IFERROR(IF(ISNUMBER([Row ID]@row), INDEX({Master Sheet Names Column}, MATCH([Row ID]@row, {Master Sheet Helper Column}, 0))), "")

.

Sorting wouldn't be accomplished through the formula, but this will successfully pull a listing of each unique name from the other sheet.

.

To alert you to needing more rows, you would enter this into a cell on your Unique Sheet in a column that is not the [Unique Names] or [Row ID] column.

 

=IF(MAX(COLLECT([Row ID]:[Row ID], [Unique Names]:[Unique Names], ISTEXT(@cell))) <> MAX({Master Sheet Helper Column}), "Add " + (MAX({Master Sheet Helper Column}) - MAX(COLLECT([Row ID]:[Row ID], [Unique Names]:[Unique Names], ISTEXT(@cell)))) + " more rows", "No additional rows needed")

.

The above will look at how many rows are populated on your Unique Sheet. It will then compare it to how many rows SHOULD be populated based on the highest number in your Helper column of the Master Sheet.

 

It will then either display "Add ## more rows" if you need more rows with the ## being replaced with the number of rows needed, or, if there are no more rows needed, it will display "No additional rows needed".

.

You can then either leave as is if you intend to check frequently, use conditional formatting to highlight the cell either red or green depending on whether or not more rows are needed, use an Alert to notify you that you need more rows with the trigger being that particular cell changing to contain the word "more", or you can use a combination of the options.

 

This will keep you alerted to when new rows are needed.

.

Let me know how all of this works out.

alex44

Thanks so much!! I really appreciate it!

Paul Newcome

Happy to help! 

alex44

This was exactly what I was looking for but I wanted to ask a follow-up question if you can spare a moment...

My master sheet (the Names column) will get re-sorted by users periodically. It seems like the approach described would not work when that column gets re-sorted. Do you agree, or am I missing something?

Paul Newcome

You are correct.

 

Add a row at the top of the list. In the Names column, enter something along the lines of

 

This row used for formatting

 

and then lock that row. Make the rest of the rows (the ones containing actual names) child rows under this one.

 

You would then make a slight modification to the main formula that you dragfilled and use it for ALL rows containing a name including the first name.

 

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names1)), COUNT(DISTINCT(Names$1:Names@row))) - 1

 

Then the names will appear in your Unique-Names listing in whatever order they are on your master sheet.

 

That should solve your sorting issues on both sheets.

 

alex44

Thanks so much!!

Paul Newcome

Happy to help! 

Clark Coles

@Paul Newcome Thank you for your comments, I know it's been a year since your last, but this helped me with a project I am working on. I am running into an issue though.

I am using API's (Wufoo form -> Smartsheets) that automatically adds a row to my master data sheet every time a form is filled out. As this sheet is consistently getting new rows added with new sellers, the helper column gets out of date and I need to manually update it in order to update my seller dashboard.

Is there a way to make the Helper column a 'column formula' or automatically update when a new row is added? Any help would be appreciated, thanks.

Paul Newcome

@Clark Coles I am not sure I follow. Could you set up some mock screenshots that show what you are working with and what you are trying to accomplish?

Clark Coles

@Paul Newcome Yes.

GOAL::: I'm trying to replicate the scenario at the top of this thread. I have a list of sales reps names that come in to a smartsheet, and these names are duplicated multiple times. On a separate sheet, I am creating a list of unique names from that sheet using the method you shared.

"On your Master Sheet, add in a Helper column of the text/number type. In row 1, enter the following...

 

=COUNT(DISTINCT(Names$1:Names@row))

 

then in row 2 enter

 

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names1)), COUNT(DISTINCT(Names$1:Names@row)))

 

and dragfill that down the rest of the rows.

.

In your Unique Names sheet, you would then add a column where you simply enter 1, 2, 3, 4, 5, etc... basically entering the row number. We will call this column [Row ID] for this example. You will want to extend beyond what you anticipate, but we can build in an Alert that lets you know you need to add new rows if needed/preferred.

 

Then in the Unique Names column, you would enter something along the lines of...

 

=IFERROR(IF(ISNUMBER([Row ID]@row), INDEX({Master Sheet Names Column}, MATCH([Row ID]@row, {Master Sheet Helper Column}, 0))), "") "

PROBLEM::: The issue that I'm running into is that the master sheet is continually getting new rows added to it at the top via an API (Wufoo Form -> Smartsheet). In order for the new row's unique names to show on my Dashboard sheet, I have to go into my master sheet and manually move the first row 1 formula to the new row 1, and drag the second formula for row 2 and on.

DESIRED SOLUTION:::: I'm looking for a way to make the helper column formulas automatically apply to new rows that are coming into the sheet without me needing to manually go in and apply the formulas on a daily basis.

See screenshot:

Please let me know if there is additional info or clarity I can provide, and thank you for your help.

Paul Newcome

@Clark Coles Have you tried building out the list and corresponding helper columns on a separate sheet? This will allow you to reference entire columns to pull from for the listing and will not be affected by new rows being added to the main sheet.

Clark Coles

@Paul Newcome Where I'm getting stuck there is that the formula in row 1 cell 1 of the Helper column is different that the formula for the rest of the rows in the column. How do I account for these two formulas referencing an entire column at the same time?

On your Master Sheet, add in a Helper column of the text/number type. In row 1, enter the following...

 

=COUNT(DISTINCT(Names$1:Names@row))

 

then in row 2 enter

 

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names1)), COUNT(DISTINCT(Names$1:Names@row)))

 

and dragfill that down the rest of the rows.

Leibel S

Clark,

You do not need a helper column at all in your source sheet.

Create a new sheet that has:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
3. Random column used just to get rows added to your sheet, so keep on typing into more rows and save to add additional rows.
4. "Names" : Column Formula: "=IFERROR(INDEX(DISTINCT(COLLECT({NAMES}, {NAMES}, <>"")), [ROW#]@row), "")

This process makes sure that everything (besides creating the additional rows) runs off of column formulas automatically.

Paul Newcome

DO you have a Created (date) system column on the source sheet?

Clark Coles

@Leibel Shuchat Thank you, that is working and updating when a new row comes in, I just have to remember to manually add rows.

@Paul Newcome yes I do, I'm open to other suggestions as well :) If I can get it all automated, that would be ideal.