Create a list of all values from one sheet that match a cell on another - VBA

[Realjoshtodd]

Ok I need to build a list automatically on Sheet2 (In Column A from Row 2 down).

If I enter the data "Team 1" into A1 on Sheet2, I need it to pull anyones names from Sheet1 Column B. (Column A has the Team # they are on).

I've tried the following and have had no luck.

=IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($B$1:$B1))),"")

Wouldn't produce anything.

I've seen a VBA code in the past that worked similar but couldn't find it to try to manipulate it to work in this case.

 

[Fluff]

Where do you want to put the names?

 

[Realjoshtodd]

Where do you want to put the names?

Sheet2 Column A Row 2

 

[Peter_SSs]

What Excel version are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version

 

[Fluff]

How about

+Fluff.xlsm
	A	B
1	Team	Name
2	Team1	Tom Pearce
3	Team2	Bill Brewer
4	Team3	Jan Stewer
5	Team1	Peter Gurney
6	Team2	Peter Davy
7	Team3	Dan'l Whiddon
8	Team1	Harry Hawke
9	Team2	Tom Cobley
Sheet1

+Fluff.xlsm
	A
1	Team1
2	Tom Pearce
3	Peter Gurney
4	Harry Hawke
5
6
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(INDEX(Sheet1!$B$2:$B$9,AGGREGATE(15,6,(ROW(Sheet1!$B$2:$B$9)-ROW(Sheet1!$B$2)+1)/(Sheet1!$A$2:$A$9=$A$1),ROWS(A$2:A2))),"")

 

[Peter_SSs]

What Excel version are you using?

If it is Excel 365 with the FILTER function, then just put this formula in A2 and the other results will automatically 'spill' down into any required cells.

Sheet1 the same as Fluff's above.

Realjoshtodd 2020-04-01 1.xlsm
	A	B	C	D
1	Name			Team1
2	Tom Pearce
3	Peter Gurney
4	Harry Hawke
5
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=FILTER(Sheet1!B2:B20,Sheet1!A2:A20=D1)
Dynamic array formulas.