One play ..
In sheet: A
-----------
Names are listed in col A, in A1 down
In sheet: B
-----------
Assume the table is in cols A to I, data from row2 down
Names are assumed to be in col C, from C2 down
Use an empty column to the right of the table, say col K
Put in K2: =IF(ISNUMBER(MATCH(C2,A!A:A,0)),ROW(),"")
Copy K2 down to say, K100, to cover the max expected data
(Leave K1 empty)
In sheet: C
----------
Copy paste the same col headers over from sheet B
Put in A2:
=IF(ISERROR(SMALL(B!$K:$K,ROWS($A$1:A1))),"",INDEX (B!A:A,MATCH(SMALL(B!$K:$K
,ROWS($A$1:A1)),B!$K:$K,0)))
Copy A2 across to I2, fill down to I100
(cover the same range size as done in col K in sheet B)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bobf" wrote in message
...
I am trying to use a list of names (first, last) in a colum in worksheetA
to
find corresponding entries in an imported worksheet B, Once I find the
name
in worksheet B I want to extract the entire row of data into another new
worksheet.
For example
Worksheet A:
Column A
Bill Jones
Fred Smith
Worksheet B:
Place Overall Name Swim
Bike
Run Finish
1 12 Bill Jones 24 anytown ST 13:45 54:45
23:00 1:34:45
4 78 Fred Smith 56 anothertown ST 15:00 56:12
24:34 1:48:34
The data in worksheet B in the Name column is all in the same cell (not
different columns)
Now if there is a match between worksheetA name and the name exists in
worksheet B I want to copy the entire row from worksheet B into a new row
in
Worksheet C. Not all names in worksheet A will have an entry in worksheet
B
Any ideas?