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
4
Medium Priority
?
294 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
[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
  • Learn & ask questions
  • 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 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

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

Independent Software Vendors: 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

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

636 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