Here's a formulas play which can yield the required results in a table in
Sheet2,
with the unique codes extracted as col headers, eg: MOV, BIN, GAR
and with all the names neatly bunched below the codes, viz.:
MOV
John Doe
Bill Smith
etc
BIN
Jane Doe
etc
GAR
Sam Beatty
Jeff Jones
etc
A sample construct is available at:
http://www.savefile.com/files/465001
Display multiple matches.xls
Source data in Sheet1's cols A (codes) and B (names),
data from row2 down
In Sheet2,
In A2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!$A$2:A2,Shee t1!A2)1,"",ROW()))
Copy A2 down to cover the max expected extent of data in Sheet1's col A.
Leave A1 blank.
In B1:
=IF(COLUMN(A1)COUNT($A:$A),"",INDEX(Sheet1!$A:$A, SMALL($A:$A,COLUMN(A1))))
Copy B1 across to say, H1. This extracts the unique codes in Sheet1's col A
in B1 across. all results neatly bunched to the left.
In B2:
=IF(Sheet1!$A1="","",IF(Sheet1!$A1=B$1,ROW(),""))
Copy B2 across to H2, fill down to the extent as done in col A, plus one row.
Results area
In J1: =IF(B1="","",B1)
J1 copied across to P1
Then in J2:
=IF(ROW(A1)COUNT(B:B),"",INDEX(Sheet1!$B:$B,SMALL (B:B,ROW(A1))-1))
J2 copied to P2, filled down to the extent done in cols B to H. This will
extract the names corresponding to the codes from Sheet1's col B, with all
names neatly bunched at the top below the codes.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mcilwrk" wrote:
I am trying to display a list of names in one worksheet that match a specific
"code" in another worksheet. I have tried VLOOKUP, but it stops when its
finds the first instance of the code.
Example:
Sheet 1 Sheet 2
Col A Col B Col A Col B
Code Name MOV John Doe
MOV John Doe Bill Smith
BIN Jane Doe BIN Jane Doe
GAR Sam Beatty GAR Sam Beatty
MOV Bill Smith Jeff Jones
GAR Jeff Jones
I thought about pivot tables, but I also need data from additional column
accross the page and not every row and column has data in it. (Pivit tables
don't like blanks)
I apologize for possibly not explaining this clearly, I would be happy to
send a sample spreadsheet to anyone who might think they can help.
Regards
Ken McI