Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C"

[Stephen - Dallas]

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

 

[Max]

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))*ISNUMBER(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

:

Re: Match #1, 2, 3 with "A", #1, 2, 2 with "B", and #3, 3 with "C"

 

[Stephen - Dallas]

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]

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))*ISNUMBER(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