Solved

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

Posted on 2013-10-24
4
255 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now