Master ingredients table

Normally I can figure my way thru Excel but i am having a complete block on how to manipulate some data my son has - I am trying to put a business plan together for him.

I have a top row that contains some names of products - currently 15 of them
Each of these is in a separate column.

Beneath each of these product titles is a list of ingredients used for that product, some of these have a percentage against them, most don't.
The ingredients are in no particular order currently but can be listed a to z or otherwise.
Currently there are 45 plus ingredients - that is a growing list.

I would like to create a spreadsheet that has all the ingredients in a column on the LHS with all the products on a top horizontal row and for a % or an X (or ingredient reference) to be shown in its appropriate position using the information in the original data and various excel functions.

For some reason i just can't get my head round this problem any help would be very appreciated.

Special-K thank you for your reply.

I am hoping that I have attached a sample file (https://www.excelforum.com/attachmen...1&d=1519046153) to this reply.


Are you planning to do away with the original 15 column layout and replace it with the "ingredients v products" layout? - I would prefer not to delete it all looking more to add another table to the spreadsheet,

I would prefer to use formulas to achieve what i want if it can be done, mainly because of my total lack of familiarity with VB



I have attached a sample sheet, this list of products is going to expand considerably overtime, it would be beneficial if new ingredients could be identified by a colour to highlight their introduction - but again not a necessity




Table 1 is example of original info.
On some of the ingredients there is - a space in front of the ingredient (which is an unwanted space to begin with)
- a % of total quantity used in the mix, which ideally the final table will have once all info has been collected
- on some it specifies ingredient items as coarse, powder or ground before or after the ingredient for the moment I am going to ID these as different products

I transposed and cleaned this table to look like table 3

Ideal outcome would be as per table 4 but showing all products with their ingredient % or an x to Identify the ingredient as being used
Where the ingredient is identified by a name including a percentage it would be ideal to have the % used against the name rather than an x against the ingredient name including percentage. BUT this can be dealt with later.


The two ingredients 'ground RM09' and 'coarse RM17' will be renamed so they read 'RM09 ground' & 'RM17 coarse' respectively.

The product names Product 1 to Product 1+n will be actual text names.
The materials RM01 to RM01+n will be actual names

I think I have covered most of it but probably not

Special-K thanks for looking - how about if all the extras are left out and one concentrated on just creating a master table from the information in table 3 and including it all in table 4 to be filled using just an x if material is used for a product and a space if not, for %'s to be added at a later point?
If this needs to be done with VBA then VBA it is (I will do a crash course - been wanting to learn VBA for years but not had requirement or time and have always managed what I've needed with formulas to now)
Again thank you

Special-K I would definitely like to have the full table array, it allows us to look at an ingredient to see how many products are utilising it.

Its not essential that the original info is kept as we can keep that in another sheet. But numerous new product formulas will be added regularly and additional materials too



I was thinking that if there was a formula to alphabetically list all the components used across all the products in the 3rd table removing duplicates that this would then provide the 1st column, and that a look up function could be used to identify its use in the appropriate cell of each column to create the final table, and that as an extra it would be possible to use a percentage from table 3 (though I did not include any percentages in that table yet- just dont have them currently) to replace the marker identifying its use

Special-K I appreciate you looking at it for me - thank you