Excel How To: Reverse Ingredient List

[SzczerbiakManiac]

The actual lists are more complicated, but for simplicity, let's say I have this list of recipes in Excel that looks like this:
French Toast

• Eggs
• Bread
• Butter

Pancakes

• Eggs
• Flour
• Sugar

Hollandaise Sauce

• Eggs
• Butter
• Lemon

What I need is a list of ingredients with what recipes they're used in. E.g., Eggs are used in French Toast, Pancakes, and Hollandaise. Butter is used in French Toast and Hollandaise. Flour is only used in Pancakes.

Is there a relatively easy way to create that?

[RStar]

Wow, that's a good one. I would think you would have to start by imputing the full recipes into look up tables, but how to get the query to come up with a list of the recipes as a "where used" in excel is a perplexing question for me. Good luck!

[scaeagles]

I could write you a SQL query but that's not going to help you much.

[RStar]

Well, a lot of help we turned out to be. We could just toss in some HTML code and call it a day!

[SzczerbiakManiac]

Originally Posted by scaeagles

I could write you a SQL query but that's not going to help you much.

How much would you charge?

[Moonliner]

How are they listed in the table:

A1 French Toast
A2 Eggs
A3 Cinnamon
A4 Butter
A5 Pancakes
A6 Eggs
A7 Flour
.
.
.

How can you tell an ingredient cell from an item cell?

[SzczerbiakManiac]

Originally Posted by Moonliner

How are they listed in the table:

Like this:
A1 French Toast
A2 Eggs
A3 Cinnamon
A4 Butter

A6 Pancakes
A7 Eggs
A8 Flour

It's essentially a plain list, but each item is listed in its own cell. Each recipe/ingredient list is separated by an empty row. I can fiddle with the layout of that would help. E.g., either of these would be easy to accomplish with a quick bunch of cut & pastes:
A1 French Toast
A2 Eggs
A3 Cinnamon
A4 Butter

B1 Pancakes
B2 Eggs
B3 Flour
_____________________________

A1 French Toast
B2 Eggs
B3 Cinnamon
B4 Butter

A6 Pancakes
B7 Eggs
B8 Flour

[SzczerbiakManiac]

A friend suggested this solution which suited me just fine:
Column A | Column B
Dish | Ingredient
French Toast | Eggs
French Toast | Bread
French Toast | Butter
Pancakes | Eggs
Pancakes | Flour
Pancakes | Sugar
Hollandaise Sauce | Eggs
Hollandaise Sauce | Butter
Hollandaise Sauce | Lemon

Then filter on the ingredient.

I pretty much taught myself how to use Excel, to there are huge gaps in my knowledge--including this probably obvious solution.

Thank you all for putting up with my ignorance.

[Alex]

You'll need to put the dish in a separate column from ingredients and then repeat for each row (as SB suggests).

Once you've done that, what you're asking for is why god invented pivot tables.

[RStar]

I knew Alex would have the answer!