Extracting a list of unique values from a list on another sheet

Eddie Abasta

How can I extract a list of unique values from a list on another sheet? I tried doing this as if in Excel but it isn't working quite right.

Paul Newcome

The easiest way would be to use a helper column on the master sheet that will designate which rows have unique values and will establish a row number for each of those. You can then use an INDEX statement and use the corresponding row numbers for each unique value within a SMALL function to establish which row to pull from in your INDEX statement.

 

Something like the below screenshots....

Niyati Kumar

@Paul Newcome , I have a similar use case and was able to use the first formula for a helper column on my main sheet. However, I need the unique values identified by this formula to be indexed on a new sheet. I am using these sheets as templates, so I need the formula to be re-usable and reproducable when the templates and copied and used.

Paul Newcome

@Niyati Kumar I'm not sure I follow. Could you proved some screenshots of "mocked up" sheets that shows exactly what you are wanting to accomplish?

Niyati Kumar

Sure, @Paul Newcome.

Here's my project tracking sheet. My goal is to summarize the total Actual Hours that a team member is assigned to each month (for their Team Leads)--this is for each project.

My Helper column below is meant to help me identify all unique instances of team member names and the corresponding row number. I am using this formula =IF(COUNTIFS([Assigned To]$1:[Assigned To]@row, [Assigned To]@row) = 1, COUNTIFS([Assigned To]$1:[Assigned To]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))).

I have the formula in place to get these totals on another sheet, but ideally I would need the Team Member names from my project plan sheet (unique names) to auto-populate on the sheet where I have the totals being calculated. Currently I have entered those names manually in the Team Member column below. This is where I am having trouble. Now that I have identified the unique names on the project plan, I would like to index those names here--I haven't been able to make the Index formula work for this.

Thanks!

Paul Newcome

@Niyati Kumar Take a look at THIS SHEET. It provides a solution for parsing a list of unique names from another sheet without needing the helper columns on the source sheet.

Another option would be to leverage your helper column on the source sheet along with a helper column on your target sheet.

The helper column on the target sheet would simply be

1

2

3

4

5

6

7

etc until you have as many rows as you anticipate needing.

You could then use a formula such as

=INDEX({Other Sheet Name Column}, LARGE({Other Sheet Number Column}, [Helper Column]@row))

This will use the 1, 2, 3, 4, 5 in the helper column on your target sheet to pull the appropriate first, second, third, fourth, fifth number from the helper column in your source sheet and return that for the row number in your INDEX function.

Veronika Young

How can this be done if the unique values returned are "last name, first name". The comma separating the names seems to void the formula.

Paul Newcome

@Veronika Young You would need to use a different delimiter between the fields. So a different delimiter for the JOIN function in the String column then adjusting your other formulas to trigger on the new delimiter.

Veronika Young

The second linked solutions is great, but doesn't work because I have more than 4000 characters that would need to be joined in the distinct cell. Trying to follow the first solution and was able to get through the first half but stuck on index formula. Is there any way to break this solution out more? What is the Name List, what is the Row ID, and how did Crystle show up as number 6 instead of 9?

Paul Newcome

@Veronika Young WE would have to modify the source sheet a bit by adding in a column that will represent the row number and factoring that into the formula using a COLLECT function. Are you able to provide some screenshots of the source data with column headers shown?

Veronika Young

@Paul Newcome This is what it looks like so far, appreciate your help on this

Veronika Young

@Paul Newcome desperately hoping you'll be able to help as is the rest of my team :) any luck with the solution here?

Veronika Young

Paul Newcome

@Veronika Young My apologies. I must have missed the notification that you commented.

Would you be able to share your sheets with me? If so, you are welcome to message me on LinkedIn to get my private email address. I feel like this is going to be easier to figure out if we are both working with the same data, and it will give me exactly what you are working with so I can test appropriately.

www.linkedin.com/in/paul-newcome-8925aa188

Zach Valigura

https://community.smartsheet.com/discussion/comment/255853#Comment_255853

This was amazing. I hope one day I will fully understand the formulas you used, but for now, it works

Zach Valigura

https://community.smartsheet.com/discussion/comment/255853#Comment_255853

This was amazing. I hope one day I will fully understand the formulas you used, but for now, it works great.