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

**Science Boy** · 09-14-2007, 12:13 PM

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

**Ron Coderre** · 09-14-2007, 12:45 PM

Have you considered using a Pivot Table to do the heavy lifting?

Try this (using your sample file)

Add a heading to the Color column (C9: Color)

Then...from the Excel Main Menu
<data><pivot table>.....................Click [Next]
Set the data range ($A$9:$D$24).....Click [Next]
Click the [Layout] button
ROW: Drag the Code and ProductName fields here
(Double-click on each field and set Subtotals to None)

COLUMN: Drag the Color field here
(Double-click on the field and set Subtotals to None)

DATA: Drag the Amount field here
If it doesn't display as Sum of Amount...
Double-click on it and set the formula to SUM

Click [OK]
Select a destination cell to build the Pivot Table (eg Z15)
Click [Finish]

The finished Pivot Table will look like this:
Sum of Amount		Color								
Code	ProductName	black	blue	brown	green	orange	purple	red 	white	yellow
000001	AB		30	40			20	70	15	20	10
000002	CD		100			500	30		70	1000	
000003	EF		10		10				30

Note: I set the Grand Totals for the table to None
I also edited the last 000002 product name to "CD"

Is that something you can work with?