Match and extract values on different columns

glenhmal923

New Member
Joined
Feb 21, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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:

and here is a screenshot of what I mean:

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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
With a helpcolumn:
Excel question.xlsx
ABCDEF
1NameCityOUTPUT
2EdwardRocklandEdwardRobertEdward
3EdwardCarthageEdwardRaymondRaymond
4EdwardBowieEdwardEdwardRobert
5EdwardPekin  
6EdwardInglewood  
7RaymondRocklandRaymond 
8RaymondCarthageRaymond 
9RaymondBowieRaymond 
10RaymondDurant  
11RaymondDalton  
12RobertRocklandRobert 
13RobertCarthageRobert 
14RobertQuincy  
15RobertBowieRobert 
16RobertLayton  
Sheet1
Cell Formulas
RangeFormula
D2:D16D2=IF(COUNTIF($B$2:$B$16,B2)>=3,A2,"")
E2:E16E2=IF(ROW()-1<=SUMPRODUCT(--(FREQUENCY(MATCH($D$2:$D$16,$D$2:$D$16),MATCH($D$2:$D$16,$D$2:$D$16))>0)),INDEX($A$2:$A$16,AGGREGATE(14,6,((FREQUENCY(MATCH($D$2:$D$16,$D$2:$D$16),MATCH($D$2:$D$16,$D$2:$D$16))>0)*(ROW($D$2:$D$16)-1)),ROW()-1)),"")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
I haven't looked at this question in detail but there appears to be some similarity to a question that I was looking at last week. My suggestion uses the office 365 dynamic arrays so would not work with excel 2016, but perhaps variations of @Eric W's suggestions could be used here.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,378
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Match values on different columns
and Match and extract values on different columns - OzGrid Free Excel/VBA Help Forum
and Match values on different columns based on criteria

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Last edited:

Forum statistics

Threads
1,141,284
Messages
5,705,493
Members
421,398
Latest member
Rahat Anwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top