#1
|
|||
|
|||
Match values on different columns based on criteria
Hi I am trying to extract from the first column "Name" the matching values from a 2nd Column "City", only if I have at least 3 matching values on column "City". Basically trying to find all the values in column "Name" that have at least 3 matching values (same values) on column "City". I have put together an Excel sheet here: Dropbox - Excel question.xlsx - Simplify your life and here is a screenshot of what I mean: Dropbox - Excel output notes.png - Simplify your life I have tried a few formulas like Index, Match etc but no success, not getting the same output. Does anyone have any solution or idea on how I can make this work, to get that output? (check the screenshot, looking to get the names values) Thank you so much! |
#2
|
||||
|
||||
Not especially easy.
In the attached: 1. In Sheet1 a solution which may not work in Excel 2016 involving FILTER and UNIQUE. 2. In Sheet2 a Power Query in cell H1. Right-click and choose Refresh to update. |
#3
|
|||
|
|||
Thanks a lot for the help!!! thank you so much!
I have tested the solution in Sheet 2 and changed some values, but seems that the query is not working properly, i am still getting same output values in H1; any suggestion what am I doing wrong? thank you! |
#4
|
||||
|
||||
Cross posted at Match and extract values on different columns - Excel Formulas - OzGrid Free Excel/VBA Help Forum
Please read Excelguru Help Site - A message to forum cross posters to understand why it is important to add links to cross posts |
#5
|
||||
|
||||
Quote:
2. You said: Quote:
Are you looking to list names where: each name appears in at least three different cities or: the names in cities where there are at least 3 different names in one city (this is what I've done) or: something else? |
#6
|
|||
|
|||
Hi
actually you are correct, it is showing the right output for me; I tested the Sheet2 and that's the one working well for me; Sheet1 did not work that well. So I am looking to list names where each name appears in at least 3 different cities. That being said, if I may kindly ask if you can help with a tweak to the problem? I am trying to group the list of names in column F for instance, and on column G to display de-duplicated values of the City that are matching those Name values. Basically i am trying to get the same output like you did, but also get the corresponding City and also there will be separate groups of Name. The problem i am having is that i need to compare all the combinations of Name that have different cities (same rule applies at least 3 cities per each name from that group). I am finding hard to get a solution for this : ( I have attached the updated file and a screenshot if you don't mind me asking if you can help with this as well @p45cal ? Thank you so much! |
#7
|
||||
|
||||
I can get 2 of the lists you want, see attached, placed next to your desired output.
I cannot understand for the life of me how you obtain the lists I've highlighted in pink. |
#8
|
|||
|
|||
Not exactly what you asked, but I think close enough!
You can hide helper columns (ones colored differently) in datatable. Currently when same city is attached to same name several times, all occurrences are returned, and a city counts for every occurrence. Probably it is possible to ignore multiple occurrences, but this will demand much more time than I'm ready to spend. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create a unique list of values that match a criteria, sorted in order of another criteria | BradRichardson | Excel | 2 | 01-03-2017 12:25 AM |
Count unique values that match 2 or more criteria | caeiro01 | Excel | 1 | 10-25-2015 02:34 AM |
Hide rows in multiple columns based on zero values | Deane | Excel Programming | 19 | 06-23-2015 11:24 PM |
transpose values based on mulitple criteria | mlttkw | Excel | 1 | 12-06-2013 02:00 AM |
Change values in cells based on criteria | SaneMan | Excel Programming | 2 | 02-02-2012 07:58 AM |