Match and extract values on different columns

[glenhmal923]

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:

Excel question.xlsx

Shared with Dropbox

www.dropbox.com

and here is a screenshot of what I mean:

Excel output notes.png

Shared with Dropbox

www.dropbox.com

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!

 

[mart37]

With a helpcolumn:

Excel question.xlsx
	A	B	C	D	E	F
1	Name	City				OUTPUT
2	Edward	Rockland		Edward	Robert	Edward
3	Edward	Carthage		Edward	Raymond	Raymond
4	Edward	Bowie		Edward	Edward	Robert
5	Edward	Pekin
6	Edward	Inglewood
7	Raymond	Rockland		Raymond
8	Raymond	Carthage		Raymond
9	Raymond	Bowie		Raymond
10	Raymond	Durant
11	Raymond	Dalton
12	Robert	Rockland		Robert
13	Robert	Carthage		Robert
14	Robert	Quincy
15	Robert	Bowie		Robert
16	Robert	Layton
Sheet1

Cell Formulas
Range	Formula
D2:D16	D2	=IF(COUNTIF($B$2:$B$16,B2)>=3,A2,"")
E2:E16	E2	=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]

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]

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.

 

[glenhmal923]

Thanks a lot guys, I will test the solution!