Solved

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

Posted on 2013-10-24
4
274 Views
Last Modified: 2013-11-04
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
Comment
Question by:gantone1
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:barry houdini
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

by:gantone1
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

I attached sample spreadsheet

Thanks!
Example.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 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

by:gantone1
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question