Gary Antonellis
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
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
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
I attached sample spreadsheet
Thanks!
Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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-Engi neers' but it contains the same value as row 3.
Thanks!
Example2.xlsx
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-Engi
Thanks!
Example2.xlsx
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(
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