Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2013-10-24
Medium Priority
294 Views
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
0
Question by:gantone1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 50

Expert Comment

ID: 39599089
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
0

Author Comment

ID: 39605800
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

Thanks!
Example.xlsx
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39606656
My method doesn't allow you to have blanks - the zero is from the blank cells - to avoid that change to this formula in A4 copied down

=IFERROR(INDEX(PersonGroupList,MATCH(1,INDEX((COUNTIF(A3:A\$3,PersonGroupList)=0)*(PersonGroupList<>""),0),0)),"")

That will ignore blank cells in the list

regards, barry
0

Author Comment

ID: 39609038
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.