Restructuring Listed Entries Into a Matrix, Matching Rows and Columns?

[EduardStoo]

Hi All -

I have arguably the biggest project I have ever attempted via excel here! I have a list of about 2,000 stock items, all broken down to a row for each ingredient contained within said product (so lines for each stock item/product can vary from between 3 to about 15 rows per product, depending on amount of ingredients).

What I really need to do, is try and make excel 'deposit' the % of each ingredient against the correct row in a new 'matrix' (that is to say, match stock code, column H in the e.g. file), with the correct column along on the same row for the named ingredient).

I think the e.g. file demonstrates what is required.

There are about 2,000 products vs about 200 ingredients so although it'd choke the computer, I don't mind this for the sake of a few hours, when the other option is to input manually!!

(The packaging can be discarded for this exercise, it's just ingredients).

I have already got excel to do a lot of the work using index/match vs 2 x criteria. I am wondering, in light of the huge amount of data, what the most efficient formula would be to use again. The data would be index/matched on a further page, so I'd like to keep it simple. I'd prefer not to go the pivot route, unless there is no other option, as I'm not a massive user of them at the moment!

Hope this makes sense and I would really appreciate the help as this could open up some other like projects for me which would be really useful!

Thanks,

Stuart

[PaulM100]

If you put the quantity found in column B(eg: 10 KG), in a different column not in the same, then you could use:
=SUMPRODUCT(($B$8:$B$13=$I3)*($D$8:$D$13=L$1)*($C$8:$C$13=L$2),$F$8:$F$13)
or replace tthe stock description with product code,this part ($B$8:$B$13=$I3) with this$A$8:$A$13=$H3)

[EduardStoo]

Please ignore this, I've sort of double posted!

[EduardStoo]

Thanks Paul -

I think this might work OK thanks. I can base the formula to work on the code and not the descriptions (i.e. as you say, change formula to column A, not B and column H, not I). The descriptions are not consistent, BUT the codes are, so this should work fine!

I'll come back to you!

Thanks,

Stuart