Displaying multiple matches

[mcilwrk]

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 apologoze 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

 

[Don Guillett]

try data>filter>autofilter

 

[andy62]

There is a formula you can copy into colum B of your sheet2 that will look up
and display all the matches you want. The challenge is that you won't know
how many rows to leave before your next column A entry. You might be better
off, in your Sheet2, with all the lookup items in row1, then all the matches
below them in the same column, like this:

Row1 MOV BIN GAR
John Doe Jane Doe Sam Beatty
Bill Smith Jeff Jones


Given my expertise it would take me quite a while to customize the formula
for your example, so I'll just refer you to where I asked a similar question
a few months ago:

http://www.microsoft.com/office/com...ions&mid=86242a30-f692-4cc1-88fb-fc5130ffd40c

Hope that helps.

 

[T. Valko]

Or, you could do it like this:

MOV.....John Doe.....Bill Smith
BIN.......Jane Doe
GAR.....Sam Beatty.....Jeff Jones

Biff

 

[Max]

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,Sheet1!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]

The crux of your intent in your actual set-up:

.... What I was trying to do was look up the activity code
(cell AH3 for example) in all of column A of the residents tab and simply
list the names of the people that participated (hence the name appearing on
each line on the residents tab, column B) ...

One way to achieve the multiple name returns for any single activity code ..

Create a named range, "Code",
where Code =Table!$A$2:$A$51

In Residents,
The codes: HH, MOV. BIN, etc would be listed within A2:A67, with
corresponding names listed in B2:B67

Set up this reference table:

In AO12:
=INDEX(Code,COLUMN(A1))
Copy AO12 across to CL12. This will list out all the codes in the named
range

In AO13:
=IF($A13="","",IF($A13=AO$12,ROW(),""))
Copy AO13 to CL13, fill down to CL67.

Then in Activity Summary,

With the 1st activity code in AH3

Put in A7:
=IF(ISERROR(SMALL(OFFSET(Residents!$AN$12:$AN$67,,MATCH($AH$3,Residents!$AO$12:$CL$12,0)),ROW(A1))),"",INDEX(Residents!$B$12:$B$67,MATCH(SMALL(OFFSET(Residents!$AN$12:$AN$67,,MATCH($AH$3,Residents!$AO$12:$CL$12,0)),ROW(A1)),OFFSET(Residents!$AN$12:$AN$67,,MATCH($AH$3,Residents!$AO$12:$CL$12,0)),0)))
Copy A7 down to A12 (this assumes you expect only a max of 6 names per
activity code). A7:A12 will return all the different names from "Residents"
corresponding to the activity code in AH3, neatly bunched together.

With the 2nd activity code in AH17

Put in A21:
=IF(ISERROR(SMALL(OFFSET(Residents!$AN$12:$AN$67,,MATCH($AH$17,Residents!$AO$12:$CL$12,0)),ROW(A1))),"",INDEX(Residents!$B$12:$B$67,MATCH(SMALL(OFFSET(Residents!$AN$12:$AN$67,,MATCH($AH$17,Residents!$AO$12:$CL$12,0)),ROW(A1)),OFFSET(Residents!$AN$12:$AN$67,,MATCH($AH$17,Residents!$AO$12:$CL$12,0)),0)))
Copy A21 down to A26. A21 houses the same formula as A7, except it points to
the 2nd activity code in AH17 (instead of to AH3). Likewise, this returns
the different names corresponding to the activity code in AH17.

Repeat the above constructs as far down as required for all other activity
codes.

Have sent over the implemented construct in your sample. Please do not send
unsolicited samples to my email. Keep all discussions within the newsgroups.

 

[mcilwrk]

Thanks for your help Max. Sorry I replied to your email before reading the
request to post all follow-up questions in the newsgroup....

As a follow up, there are 31 columns representing every day of the month.
What code would I need to use to duplicate that daily participation
information for each resident from the residents tab over to the activities
tab where it summarizes which resident participated in which activity during
the month.?

It is so difficult to explain what I am trying to do without being able to
see the spreadsheet.

Thanks again for your input

 

[Max]

.. The only outstanding question I have now is how to duplicate the
information entered into columns F to AJ on the residents tab to the columns
C to AG in the activity summary tab. In other words, when the residents names
are summarized on the activities tab for each of the activities they
participated in, how do I get their participation information to fill in too?
...

In Activity Summary,

Array-enter in C7 (press CTRL+SHIFT+ENTER):
=IF(OR($AH$3="",$A7=""),"",INDEX(Residents!F$13:F$67,MATCH(1,(Residents!$A$13:$A$67=$AH$3)*(Residents!$B$13:$B$67=$A7),0)))
then copy C7 across to AG7 and fill down

Repeat the construct similarly for the other codes. Copy C7 n paste into the
next top left corner cell, ie C21, then change AH3 to AH17, array-enter it,
then copy across/fill down. Repeat for C35, and so on.

I've sent over an implemented construct for the above.