Hello!
I have two columns of data and I'm trying to find all the uniques in (A) the first column, then match for uniques in (B) the adjacent column and return as shown below in C and D. I've tried several Index/Match/Countif options but can't seem to get the layout right or get all uniques. Appreciate your help!
Table Sample.png
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Including a sample sheet and jpg for best compatibility.Test Sheet.xlsxTable Sample.jpg
In c2, copied down:
=IFERROR(INDEX($A$2:$A$15,MATCH(D2,$B$2:$B$15,0)),"")
In D2, copied down:
=IFERROR(INDEX($B$2:$B$15,MATCH(0,INDEX(COUNTIF($D$1:$D1,$B$2:$B$15),0),0)),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi, Glenn. Thanks for the help! I found one issue that I might have that I didn't consider before. Once a unique is found in B that matches A, it won't return that same value if also a match for a second value in A. Please see attached. When I put penne as a fruit, it doesn't return the Pasta/penne combination. So, essentially, column B could have values that match one or many unique values in A. Any idea how to correct this?Test Sheetv2.xlsx
Hey K-Dog,
Your question is exactly what the advanced*filter does when you check the "Unique" box.
Click on the data tab and then on Advanced Filter. Select your columns A:B and leave the criteria blank. Click where you want the*answer to go and check the unique box. Boom! Done!
http://www.contextures.com/xladvfilter01.html
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Or, should you prefer a dynamic, formula-based solution: here are two options for you....
Glenn,
These are great options to have! Thanks! Though Marvin's suggestion of the Advanced Filter is a great hack, I am definitely hoping to get something formula based to reduce a step, but can't figure out how to return uniques from B that may apply to multiple values in A as I mentioned above. Once it finds a unique in B, it won't return that unique if it applies to another value in A. (i.e. When I put penne as a fruit, it doesn't return the Pasta/penne combination.) but I'd like the returned values in the same format as my first example. Does that make sense?
Thanks, MarvinP. That's a great hack to have!
Did you actually look at Option 1 in Post 7??? What are E7 to F8 showing?
I did see that and want that exact output but liked that your initial formula did not add another column. Is it not possible without the concatenation? Thanks!Originally Posted by Glenn Kennedy
Possibly... But why not just hide the helper column? Or put it on another sheet & hide the whole sheet?
Another way ...
=IFERROR(INDEX(A$2:A$15,MATCH(0,COUNTIFS($C$1:$C1,$A$2:$A$15,$D$1:$D1,$B$2:$B$15),0)),"")
Copy across and down.
Very nice Phuocam!!
Hey K-Dog,
Calling*the Advanced Filter Method a "hack" is not quite correct. Excel has lots of tools built into it. Filters, Sorts, Formulas, Named Ranges are all great tools. NOT HACKS!!
I can do your problem with 7 mouse clicks and no formulas are needed. I'd call that a great tool, if you know how to use it. Learn a bit more about Advanced Filters and you will see how easy your problem is. If you need to automate it with different sizes of datasets you can create a Macro in VBA to do those 7 clicks. OH - VBA and Macros are*other great tools built into Excel. They aren't hacks either.
Phuocam,
This is perfect! How can I make it search all of column A and B instead of a range?
Noted, thanks, MarvinP.
Create dynamic named ranges: Group and Item
For Group
Name: Group
Refers to: =OFFSET(Sheet1!$A$2,0,0,COUNTIF(Sheet1!$A:$A,"?*")-1)
For Item
Name: Item
Refers to: =OFFSET(Sheet1!$B$2,0,0,COUNTIF(Sheet1!$B:$B,"?*")-1)
Formulae:
in C2
=IFERROR(INDEX(Group,MATCH(0,COUNTIFS($C$1:$C1,Group,$D$1:$D1,Item),0)),"")
in D2
and
=IFERROR(INDEX(Item,MATCH(0,COUNTIFS($C$1:$C1,Group,$D$1:$D1,Item),0)),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks