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

 

[Sandeep Warrier]

The formula in G1 and G10 seem to be proper.

One thing I forgot to mention, you need to ensure that there are no extra spaces in any of the cells. I seem to recall that when I had initially copied over your data (from Post 1), the "Cranberry" from Sheet2 had an extra space after it.

 

[rockandahardplace]

Sandeep,

I am on my knees praising you. Thank you very much. Your solution worked flawlessly. Darn those extra spaces (that aren't entirely visible). Once I removed them the matrix looked beautiful.

Quick follow up question and then go enjoy your weekend. If I want to use this formula for a much larger data set are there any adjustments that need to be made to the parent formula? Sheet 2 of my real data has a minimum of 100 rows but a maximum of 9 columns. Sheet 1 is similar with a minimum of 100 rows and up to 26 columns.

Thanks!!!

 

[Sandeep Warrier]

You're welcome! I'm glad it worked fine.

For the actual data, you can extend the ranges, but as before ensure that the absolute and relative referencing are proper i.e. the $ signs.

The formula should behave in the same way i.e. across columns only the column name should change in the last parenthesis, and down rows only the row number should change in the second parenthesis.