Link to home
Start Free TrialLog in
Avatar of Gary Antonellis
Gary AntonellisFlag for United States of America

asked on

Find Unique values in list of cells to populate cells in different worksheet

I have a list of cells (but not the whole column) that contains duplicate values.  I would like to populate cells in a different worksheet with only the unique values from this list.

The list of unique values should update dynamically, if user changes/adds/deletes values in the original list of cells such that the unique values are impacted, the list of unique values will change.

If possible I would like to do this without using VB
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

See attached example

In Sheet1 I listed some repeating values in A2:A20 and named it list using a dynamic named range (which means the definition expands automatically if you add values - but there shouldn't be gaps)

Now in Sheet 2 for the unique list I put this formula in A2

=INDEX(List,1)

and then this formula in A3 copied as far down as needed and further

=IFERROR(INDEX(List,MATCH(0,INDEX(COUNTIF(A$2:A2,List),0),0)),"")

as long as you copy this formula down far enough the list will automatically update as you change the sheet1 list

regards, barry
unique-list.xlsx
Avatar of Gary Antonellis

ASKER

I am doing something wrong, but I am close!  I attached a portion of spreadsheet.  I am getting unique values of worksheet "P-Pay Rules" cells A20-A61 to populate worksheet "config_Hours_Class" cells A3-A16 except the first cell with formula (A4) contains '0' instead of the second value in list

I attached sample spreadsheet

Thanks!
Example.xlsx
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I feel like I am abusing you!

Your suggestion worked fine but another requirement was added.  I need to contatenate the string 'HRS_CLASS_GRP_' to the start of each unique value, but when I do it messes up the comparison to the previous values in list, returning the first value in list.

On the second tab I was expecting row 4 to contain 'HRS_CLASS_GRP_Exempt-Engineers' but it contains the same value as row 3.

Thanks!
Example2.xlsx