More than a VLookup, Less than VB?

[Bundy]

Hi Guys,

Long time Reader of the Forums, but first time poster! Keep up the awesome help!

My current problem is that Vlookup only looks at the top item in a data file that meets the condition and doesn't more on.

File 1 is a flat data file just for storing Team, Name, Values
File 2 will be a calculation file that's needs to be able to self-generate the list of name, then a vlookup can be used to bring the data across.

Any suggestions or is excel limited in this and VB script is needed?

File 1(The Data):
Ford Explorers Jan Jubb 6 1 2 0 2 0
Ford Explorers Joe Smith 6 4 2 0 2 0
Ford Explorers John Bloggs 6 1 2 0 2 0
Ford Mustangs Jim Mayer 0 0 0 0 10 1
Ford Mustangs Martin Jones 6 1 2 0 2 0
Ford Mustangs Michele Mamm 6 1 2 0 2 0
Ford Mustangs Ross Salo 6 1 2 0 2 1
Holden HSV Joy Jubes 6 2 2 0 2 1
Holden HSV Natalie Bunny 6 2 2 0 2 0
Holden HSV Kate Smith 6 4 2 0 2 0
Holden HSV Debbie Jones 6 0 2 0 2 0
Holden HSV Samantha Bloggs 6 1 2 1 2 0

File 2(The Calculation):
Team Name: Ford Explorers

First name from Data File from this team
Second Name from Data File from this team
Etc
Etc

 

[Smitty]

Welcome to the Board!

This may be a good job for a Pivot Table.

The helpfile has a good tutorial on setting them up.

Hope that helps,

Smitty

EDIT: walk away for dinner and Aladin comes up with a beaut, as usual!

 

[Bundy]

I had thought of a pivot table, but that would mean manual selection if you only wanted the one team?

EG Only want Ford Explorers in this sheet - you would have to unselect all other teams from the selection box at the top.

 

[Aladin Akyurek]

I had thought of a pivot table, but that would mean manual selection if you only wanted the one team?

EG Only want Ford Explorers in this sheet - you would have to unselect all other teams from the selection box at the top.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Team	Name	Values
2	Ford Explorers	Jan Jubb	6	1	2	0	2	0			Ford Explorers
3	Ford Explorers	Joe Smith	6	4	2	0	2	0			3
4	Ford Explorers	John Bloggs	6	1	2	0	2	0			Jan Jubb
5	Ford Mustangs	Jim Mayer	0	0	0	0	10	1			Joe Smith
6	Ford Mustangs	Martin Jones	6	1	2	0	2	0			John Bloggs
7	Ford Mustangs	Michele Mamm	6	1	2	0	2	0
8	Ford Mustangs	Ross Salo	6	1	2	0	2	1
9	Holden HSV	Joy Jubes	6	2	2	0	2	1
10	Holden HSV	Natalie Bunny	6	2	2	0	2	0
11	Holden HSV	Kate Smith	6	4	2	0	2	0
12	Holden HSV	Debbie Jones	6	0	2	0	2	0
13	Holden HSV	Samantha Bloggs	6	1	2	1	2	0
Sheet1

K3:

=COUNTIF(A2:A13,K2)

K4:

Control+shift+enter...

=IF(ROWS($K$4:K4)<=$K$3,INDEX($B$2:$B$13,SMALL(IF($A$2:$A$13=$K$2,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS($K$4:K4))),"")

then copy down.

 

[Bundy]

Amazing - Thanks all!

 

[Superstar31]

Gracious