Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C"
I am trying to set up an equation that will show me "who" has "what":
Tool: hammer, drill, screwdriver, saw Borrower: Jack, John, Jill Jack borrowed hammer, drill, and screwdriver John borrowed hammer, drill, and saw Jill borrowed hammer and screwdriver Desired Table Result: Tool Borrower Hammer Jack Hammer John Hammer Jill Drill Jack Drill John Screwdriver Jack Screwdriver Jill Saw John |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C"
Just hazarding some guesses here
as to what you have & what you're after <g Assuming the source phrases are listed in A2 down: Jack borrowed hammer, drill, and screwdriver John borrowed hammer, drill, and saw Jill borrowed hammer and screwdriver here's one way to set-up a "who" has "what" quick-glance table in C1:G4 Tools listed in D1:G1 in any order: hammer, drill, screwdriver, saw Borrowers listed in C2:C4 in any order: Jack, John, Jill Then placed in D2: =IF(SUMPRODUCT((ISNUMBER(SEARCH(D$1,$A$1:$A$10))*I SNUMBER(SEARCH($C2,$A$1:$A$10))))0,"X","") D2 copied across to G2, filled down to G4 to populate the table The quick-glance table will show you "who" has "what" (via the X's returned) Adapt the range $A$1:$A$10 to suit the expected extent in col A An implemented sample is available at: http://www.savefile.com/files/3589760 QuickGlance_WhoBorrowedWhatTool_Table.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Stephen - Dallas" wrote: Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C" I am trying to set up an equation that will show me "who" has "what": Tool: hammer, drill, screwdriver, saw Borrower: Jack, John, Jill Jack borrowed hammer, drill, and screwdriver John borrowed hammer, drill, and saw Jill borrowed hammer and screwdriver Desired Table Result: Tool Borrower Hammer Jack Hammer John Hammer Jill Drill Jack Drill John Screwdriver Jack Screwdriver Jill Saw John |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C"
Thank you for replying max. However, that results are not what I am looking
for. Rather than a quick glance table. I need a two-column table that will list all times hammer matches jack, john, or jill, all time drill matches jack, john, or jill, etc. In other words, the formula would return: "if the borrower has borrowed any tools, tell me what tool was borrowed by whom, noting that if the borrower borrowed multiple tools, list the corresponding tool with each borrower." Thanks again. "Max" wrote: Just hazarding some guesses here as to what you have & what you're after <g Assuming the source phrases are listed in A2 down: Jack borrowed hammer, drill, and screwdriver John borrowed hammer, drill, and saw Jill borrowed hammer and screwdriver here's one way to set-up a "who" has "what" quick-glance table in C1:G4 Tools listed in D1:G1 in any order: hammer, drill, screwdriver, saw Borrowers listed in C2:C4 in any order: Jack, John, Jill Then placed in D2: =IF(SUMPRODUCT((ISNUMBER(SEARCH(D$1,$A$1:$A$10))*I SNUMBER(SEARCH($C2,$A$1:$A$10))))0,"X","") D2 copied across to G2, filled down to G4 to populate the table The quick-glance table will show you "who" has "what" (via the X's returned) Adapt the range $A$1:$A$10 to suit the expected extent in col A An implemented sample is available at: http://www.savefile.com/files/3589760 QuickGlance_WhoBorrowedWhatTool_Table.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Stephen - Dallas" wrote: Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C" I am trying to set up an equation that will show me "who" has "what": Tool: hammer, drill, screwdriver, saw Borrower: Jack, John, Jill Jack borrowed hammer, drill, and screwdriver John borrowed hammer, drill, and saw Jill borrowed hammer and screwdriver Desired Table Result: Tool Borrower Hammer Jack Hammer John Hammer Jill Drill Jack Drill John Screwdriver Jack Screwdriver Jill Saw John |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C"
"Stephen - Dallas" wrote:
Thank you for replying max. However, that results are not what I am looking for. Based on earlier set-up .. how about a slight tweak to the formula in D2: =IF(SUMPRODUCT((ISNUMBER(SEARCH(D$1,$A$1:$A$10))*I SNUMBER(SEARCH($C2,$A$1:$A$10))))0,$C2,"") D2 copied across/filled down to G4 as before would return the borrower's names under the tools headers in D1:G1, viz. we'd get in D1:G4 : hammer__drill__screwdriver__saw Jack_____Jack______Jack John_____John_____________John Jill________________Jill (the above may suffice for the purpose ?) Rather than a quick glance table. I need a two-column table that will list all times hammer matches jack, john, or jill, all time drill matches jack, john, or jill, etc. In other words, the formula would return: "if the borrower has borrowed any tools, tell me what tool was borrowed by whom, noting that if the borrower borrowed multiple tools, list the corresponding tool with each borrower." Perhaps this might bring you close .. Assuming the source phrases are listed in A2 down: Jack borrowed hammer, drill, and screwdriver John borrowed hammer, drill, and saw Jill borrowed hammer and screwdriver In B2: =INDEX({"hammer";"drill";"screwdriver";"saw"},INT( (ROW(A1)-1)/3)+1) In C2: =IF(ISNUMBER(SEARCH(B2,INDIRECT("A"&MOD(ROW(A1)-1,3)+2))),INDEX({"Jack";"John";"Jill"},MOD(ROW(A1)-1,3)+1),"") Select B2:C2, fill down to C14 The above would return ..: hammer Jack hammer John hammer Jill drill Jack drill John drill screwdriver Jack screwdriver screwdriver Jill saw saw John saw -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |