Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
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/comm...b-fc5130ffd40c Hope that helps. "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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
Or, you could do it like this:
MOV.....John Doe.....Bill Smith BIN.......Jane Doe GAR.....Sam Beatty.....Jeff Jones Biff "andy62" wrote in message ... 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/comm...b-fc5130ffd40c Hope that helps. "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 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
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:$A N$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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
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 -- Ken McI "Max" wrote: 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:$A N$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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying multiple matches
.. 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)*(Resident s!$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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mcilwrk" wrote: 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 -- Ken McI |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
link multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
Formula for displaying in 1 cell Lowest value from multiple other cells | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Multiple matches on VLOOKUP | Excel Worksheet Functions |