Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-21-2021, 03:56 AM
glenhmalnv2d1 glenhmalnv2d1 is offline Match values on different columns based on criteria Windows 10 Match values on different columns based on criteria Office 2016
Novice
Match values on different columns based on criteria
 
Join Date: Feb 2021
Posts: 3
glenhmalnv2d1 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 02-21-2021, 06:03 AM
p45cal's Avatar
p45cal p45cal is offline Match values on different columns based on criteria Windows 10 Match values on different columns based on criteria Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeforums46519Excel question.xlsx (19.7 KB, 13 views)
Reply With Quote
  #3  
Old 02-21-2021, 11:34 PM
glenhmalnv2d1 glenhmalnv2d1 is offline Match values on different columns based on criteria Windows 10 Match values on different columns based on criteria Office 2016
Novice
Match values on different columns based on criteria
 
Join Date: Feb 2021
Posts: 3
glenhmalnv2d1 is on a distinguished road
Default

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!
Attached Images
File Type: png h1 cell.png (131.1 KB, 25 views)
Reply With Quote
  #4  
Old 02-22-2021, 12:18 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Match values on different columns based on criteria Windows 7 64bit Match values on different columns based on criteria Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 02-22-2021, 06:25 AM
p45cal's Avatar
p45cal p45cal is offline Match values on different columns based on criteria Windows 10 Match values on different columns based on criteria Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by glenhmalnv2d1 View Post
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
1. You have only 3 distinct names in column A so what would you expect - unless you want names repeating?
2. You said:
Quote:
Originally Posted by glenhmalnv2d1 View Post
have at least 3 matching values (same values) on column "City"
The cities you've added are different.



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?
Reply With Quote
  #6  
Old 02-28-2021, 05:06 PM
glenhmalnv2d1 glenhmalnv2d1 is offline Match values on different columns based on criteria Windows 10 Match values on different columns based on criteria Office 2016
Novice
Match values on different columns based on criteria
 
Join Date: Feb 2021
Posts: 3
glenhmalnv2d1 is on a distinguished road
Default

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!
Attached Images
File Type: png Excel question - updated - detail.png (149.0 KB, 16 views)
Attached Files
File Type: xlsx Excel question - updated.xlsx (9.7 KB, 6 views)
Reply With Quote
  #7  
Old 03-01-2021, 04:58 AM
p45cal's Avatar
p45cal p45cal is offline Match values on different columns based on criteria Windows 10 Match values on different columns based on criteria Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeforums46519Excel question - updated.xlsx (25.4 KB, 5 views)
Reply With Quote
  #8  
Old 03-03-2021, 12:46 AM
ArviLaanemets ArviLaanemets is offline Match values on different columns based on criteria Windows 8 Match values on different columns based on criteria Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsx Excel question - updated.xlsx (16.3 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match values on different columns based on criteria 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
Match values on different columns based on criteria 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft