Data Comparison Matrix

[rockandahardplace]

Hello,

I have searched high and low on this fabulous forum for the last day trying to find a solution to my problem . But alas I couldn't find the exact right fit for what I need to do...

I have two sets of data. They are in separate worksheets and arranged within their respective sheets by row. Column A in both sheets houses the headings/titles. In Sheet 1 you see the drink names and if you read across have an idea how to assemble the ****tails:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Cosmo	vodka	cranberry	triple sec	lime
2	Mojito	rum	mint	lime	sugar	soda
3	Long Island	vodka	tequila	rum	gin	triple sec	sweet & sour	coca cola
4	Gin & Tonic	gin	tonic	lime
5	Cape Cod	cranberry	vodka
6	Blood Mary	vodka	tomato	hot sauce	worcestershire	lemon	salt	celery
Sheet1

Excel 2010

The second sheet is just 'random' combinations of products:

Excel Workbook
	A	B	C	D	E
1	Combo 1	vodka	lemon	rum
2	Combo 2	lemon	lime	soda	sugar
3	Combo 3	tequila	lime
4	Combo 4	tomato juice	vodka	celery
5	Combo 5	mint	vodka
6	Combo 6	mint	rum
7	Combo 7	cranberry	vodka
8	Combo 8	midori	triple sec
9	Combo 9	whiskey	orange	bitters
Sheet2

Excel 2010

I need to find out if the entire contents of one row (e.g., Combo 7) of Sheet 2 fit into a recipe from Sheet 1. In my example Combo 7 (vodka & cranberry) are two elements found in both the Cosmo and Cape Cod. It's not required that all of the ingredients be listed. Most of the posts I have seen compare individual items in a list against other individual items. I need to treat these as a block. Ideally, the output would be in a separate worksheet and generate a matrix something like the following with 1s and 0s for true and false:

Excel Workbook
	A	B	C	D	E	F	G
1		Cosmo	Mojito	Long Island	Gin & Tonic	Cape Cod	Blood Mary
2	Combo 1	0	0	0	0	0	0
3	Combo 2	0	0	0	0	0	0
4	Combo 3	0	0	0	0	0	0
5	Combo 4	0	0	0	0	0	1
6	Combo 5	1	0	0	0	0	0
7	Combo 6	0	1	0	0	0	0
8	Combo 7	1	0	0	0	1	0
9	Combo 8	0	0	0	0	0	0
10	Combo 9	0	0	0	0	0	0
Sheet3

Excel 2010

Hopefully, this challenge will entice some of the programming experts to give this some attention. I appreciate in advance any guidance that you are able to provide. Thanks!!!

 

[Tom Urtis]

Two thoughts strike me about this post.

One is, you did an excellent job laying out your scenario and explaining what you want. It's your first post on this board and people with four-digit post counts can take a lesson from your post here about how to ask a question. Very nice job.

My other thought is, why are you needing this. Is it really drinks, or is there something else going on scenario-wise. In my previous life I was a bartender and no bartender I know analyzes their drinks like this, unless it's a bar/restaurant owner or manager figuring out their alcohol inventory. What gives?

 

[jc.021286]

Just thinking quickly,
without going into a UDF

=SUMPRODUCT(--(Sheets1!$A$1:$A$10)=A1),--(Sheets1!$B$1:$B$10)=B1),--(Sheets1!$C$1:$C$10)=C1),--(Sheets1!$D$1:$D$10)=D1),--(Sheets1!$E$1:$E$10)=Sheets2!E1))

This formula would return 1 if true, 0 if false... though mixing them up... I can get more complex.
=SUMPRODUCT(IF((Sheet1!$A$1:$A$10=A1)*1+(Sheet1!$B$1:$B$10=A1)*1+(Sheet1!$C$1:$C$10=A1)*1+(Sheet1!$D$1:$D$10=A1)*1+(Sheet1!$E$1:$E$10=A1)*1>0,1,0),IF((Sheet1!$A$1:$A$10=B1)*1+(Sheet1!$B$1:$B$10=B1)*1+(Sheet1!$C$1:$C$10=B1)*1+(Sheet1!$D$1:$D$10=B1)*1+(Sheet1!$E$1:$E$10=B1)*1>0,1,0),IF((Sheet1!$A$1:$A$10=C1)*1+(Sheet1!$B$1:$B$10=C1)*1+(Sheet1!$C$1:$C$10=C1)*1+(Sheet1!$D$1:$D$10=C1)*1+(Sheet1!$E$1:$E$10=C1)*1>0,1,0),IF((Sheet1!$A$1:$A$10=D1)*1+(Sheet1!$B$1:$B$10=D1)*1+(Sheet1!$C$1:$C$10=D1)*1+(Sheet1!$D$1:$D$10=D1)*1+(Sheet1!$E$1:$E$10=D1)*1>0,1,0))

This will Check A:D 1:10 of Sheet1 for values of A:D on sheet 2 just place in column G of Sheet 2 and run down and you'll be good. It repeats, so you can add e f and others Extend to 100 and you'd get a very easy 1 if it exists, 0 if it does not.

Phew,
Let me know if you need any more help,
jc

 

[jc.021286]

Sorry,
Just realized what you wanted on the third page.
=SUMPRODUCT(IF((Sheet1!$A$1:$A$10=Sheet2!A1)*1+(Sheet1!$B$1:$B$10=Sheet2!A1)*1+(Sheet1!$C$1:$C$10=Sheet2!A1)*1+(Sheet1!$D$1:$D$10=Sheet2!A1)*1+(Shee t1!$E$1:$E$10=Sheet2!A1)*1>0,1,0))

Running this formula across and down on your third sheet, should generate that table you wanted.

 

[rockandahardplace]

Two thoughts strike me about this post.

One is, you did an excellent job laying out your scenario and explaining what you want. It's your first post on this board and people with four-digit post counts can take a lesson from your post here about how to ask a question. Very nice job.

My other thought is, why are you needing this. Is it really drinks, or is there something else going on scenario-wise. In my previous life I was a bartender and no bartender I know analyzes their drinks like this, unless it's a bar/restaurant owner or manager figuring out their alcohol inventory. What gives?

Tom, I am flattered by your praise. As I alluded to in my original post, I have read quite a bit on this site looking for help. I learned what NOT to do. It was my hope that enough clear information up front in the correct form would expedite a solution.

But your previous life as a bartender did correctly plant a seed of doubt in your mind. No, I am not pursuing a project that analyzes how to concoct ****tails. I figured that if I was going to ask for help (and mask my data) that it might as well be interesting.

 

[rockandahardplace]

Sorry,
Just realized what you wanted on the third page.
=SUMPRODUCT(IF((Sheet1!$A$1:$A$10=Sheet2!A1)*1+(Sheet1!$B$1:$B$10=Sheet2!A1)*1+(Sheet1!$C$1:$C$10=Sheet2!A1)*1+(Sheet1!$D$1:$D$10=Sheet2!A1)*1+(Shee t1!$E$1:$E$10=Sheet2!A1)*1>0,1,0))

Running this formula across and down on your third sheet, should generate that table you wanted.

J.C. Thanks for the quick reply. I have taken time to input the formula from your second post. There's a couple of issues with it:

1) this method only takes me through column D but my example shows data up to Column H. In my real data set (much larger) it actually extends about 26 columns.

2) but I was more interested to see if this was the first step to extracting what I needed so I copied and pasted the formula. There was one typo that I corrected (Shee t to Sheet) and then generated this:

Excel Workbook
	A	B	C	D	E	F	G
1		Cosmo	Mojito	Long Island	Gin & Tonic	Cape Cod	Blood Mary
2	Combo 1	0	0	0	1	0	0
3	Combo 2	0	0	0	0	0	0
4	Combo 3	0	0	1	1	1	1
5	Combo 4	0	0	1	0	1	1
6	Combo 5	0	0	1	0	0	0
7	Combo 6	0	0	0	1	1	1
8	Combo 7	0	0	0	1	1	1
9	Combo 8	0	0	0	1	1	1
10	Combo 9	0	0	0	0	1	1
Sheet3

Excel 2010

If you compare this with my original post you'll notice discrepancies. For example, Combo 9 (whiskey, orange, bitters) isn't found in any of the drink recipes on Sheet 1. But the value that comes back is 'True'. Let me know if my original post was vague--first timer, so please be patient.

Thanks.

 

[Sandeep Warrier]

Hi

One possible way using a UDF

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> MatchData(ComboRng <SPAN style="color:#00007F">As</SPAN> Range, DrinkRng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strDrinks <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ComboCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lComboCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lComboMatch <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    lComboCount = 0<br>    lComboMatch = 0<br>    strDrinks = Concat(DrinkRng)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ComboCell <SPAN style="color:#00007F">In</SPAN> ComboRng<br>        <SPAN style="color:#00007F">If</SPAN> LenB(ComboCell.Value) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            lComboCount = lComboCount + 1<br>            <SPAN style="color:#00007F">If</SPAN> InStrB(LCase$(strDrinks), LCase$(ComboCell.Value)) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                lComboMatch = lComboMatch + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ComboCell<br>    MatchData = lComboCount = lComboMatch<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> Concat(rngInput <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> cellInput <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Temp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cellInput In rngInput.SpecialCells(xlCellTypeConstants)<br>        <SPAN style="color:#00007F">If</SPAN> LenB(cellInput.Value) > 0 <SPAN style="color:#00007F">Then</SPAN> Temp = Temp & cellInput.Value & "|"<br>    <SPAN style="color:#00007F">Next</SPAN> cellInput<br>    Concat = Left$(Temp, Len(Temp) - 1)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

<b>Excel 2007</b><table width="90%" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Cosmo</td><td style=";">vodka</td><td style=";">cranberry</td><td style=";">triple sec</td><td style=";">lime</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Mojito</td><td style=";">rum</td><td style=";">mint</td><td style=";">lime </td><td style=";">sugar</td><td style=";">soda</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Long Island</td><td style=";">vodka</td><td style=";">tequila</td><td style=";">rum</td><td style=";">gin</td><td style=";">triple sec</td><td style=";">sweet & sour</td><td style=";">coca cola</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Gin & Tonic</td><td style=";">gin</td><td style=";">tonic</td><td style=";">lime </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Cape Cod</td><td style=";">cranberry</td><td style=";">vodka</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Blood Mary</td><td style=";">vodka</td><td style=";">tomato</td><td style=";">hot sauce</td><td style=";">worcestershire</td><td style=";">lemon</td><td style=";">salt</td><td style=";">celery</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table width="90%" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Combo 1</td><td style=";">vodka</td><td style=";">lemon </td><td style=";">rum</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Combo 2</td><td style=";">lemon</td><td style=";">lime </td><td style=";">soda</td><td style=";">sugar</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Combo 3</td><td style=";">tequila</td><td style=";">lime </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Combo 4</td><td style=";">tomato juice</td><td style=";">vodka</td><td style=";">celery</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Combo 5</td><td style=";">mint</td><td style=";">vodka</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Combo 6</td><td style=";">mint</td><td style=";">rum</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Combo 7</td><td style=";">cranberry</td><td style=";">vodka</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Combo 8</td><td style=";">midori</td><td style=";">triple sec</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Combo 9</td><td style=";">whiskey</td><td style=";">orange</td><td style=";">bitters</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

<b>Excel 2007</b><table width="90%" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Cosmo</td><td style=";">Mojito</td><td style=";">Long Island</td><td style=";">Gin & Tonic</td><td style=";">Cape Cod</td><td style=";">Blood Mary</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Combo 1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Combo 2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Combo 3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Combo 4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Combo 5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Combo 6</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Combo 7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Combo 8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Combo 9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=--MatchData(<font color="Blue">INDEX(<font color="Red">Sheet2!$B$1:$E$9,MATCH(<font color="Green">$A2,Sheet2!$A$1:$A$9,0</font>),0</font>),INDEX(<font color="Red">Sheet1!$B$1:$H$6,MATCH(<font color="Green">B$1,Sheet1!$A$1:$A$6,0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[rockandahardplace]

Sandeep,

Thank you for your time attempting to solve this puzzle. I like your approach and I believe the results you posted. However, when I create the UDF and then type in the formula and copy across and down my results differ:

Excel Workbook
	A	B	C	D	E	F	G
1		Cosmo	Mojito	Long Island	Gin & Tonic	Cape Cod	Blood Mary
2	Combo 1	0	0	0	0	0	0
3	Combo 2	0	0	0	0	0	0
4	Combo 3	0	0	0	0	0	0
5	Combo 4	0	0	0	0	0	0
6	Combo 5	0	0	0	0	0	0
7	Combo 6	0	1	0	0	0	0
8	Combo 7	0	0	0	0	0	0
9	Combo 8	0	0	0	0	0	0
10	Combo 9	0	0	0	0	0	0
Sheet3

Excel 2010

What do you think differed between your approach vs. my use of the UDFs?

Thanks.

 

[Sandeep Warrier]

Hi

Can you confirm if you have all the referencing proper? For example, the cell references containing the "$" should be exactly as shown.

 

[rockandahardplace]

The formulas remain static as I move horizontally with the exception of the Column letter in the last parenthesis. Here is the formula in cell G2:Excel 2010</b>

Cell Formulas
Range	Formula
G2	=--MatchData(INDEX(Sheet2!$B$1:$E$9,MATCH($A2,Sheet2!$A$1:$A$9,0),0),INDEX(Sheet1!$B$1:$H$6,MATCH(G$1,Sheet1!$A$1:$A$6,0),0))

If I move down vertically within a column then the only change is the row number in the second parenthesis. Here is the formula in cell G10:>Excel 2010</b>

Cell Formulas
Range	Formula
G10	=--MatchData(INDEX(Sheet2!$B$1:$E$9,MATCH($A10,Sheet2!$A$1:$A$9,0),0),INDEX(Sheet1!$B$1:$H$6,MATCH(G$1,Sheet1!$A$1:$A$6,0),0))

Does that help diagnose the errors of my ways?