How can I port data into new table, using any means necessary

Hi

I have a very unusual table that i want to upgrade first to a more normal style. I've attached a dummy but it also explain it below

explanation of table: product 000001 is named AB and contains multiple colors in different amount. and so on

code product color amount
000001 AB orange 20
000001 AB blue 40
000001 AB black 30
000001 AB red 15
000001 AB purple 70
000001 AB white 20
000001 AB yellow 10
000002 CD red 70
000002 CD white 1000
000002 CD black 100
000002 CD green 500
000002 EF orange 30
000003 EF red 30
000003 EF brown 10
000003 EF black 10

I want to make it like this
black blue brown, etc
code product
000001 AB 20 40
000002 CD 100
000003 EF 10



The actual table has >10000 rows and would result in a new table of 2500 by 600 columns. (not bothered excels limits on column for now) so typing the whole is not what i want to do.


I was thinking to use the labels/headers in the new table as parameters to look up a value. So by using 000001 and black in a macro/formula it would give me 20 and place that in the correct cell.

Is this something that can be done? Vlookup seems to be shorthanded for this.

hopefully someone been there before,

Science Boy


PS in the end I intend to bring the new table into msAccess so if you know a good way to put the original in access properly i'm also interested

That might help a lot, i'll give it a go on monday


thnx