Transposing Product Attributes from Columns to Rows

[Axe383]

I have a large batch of product information that I am looking to reformat so that it can be imported to our website. Below is an example of the format that it is in now is:

[TABLE="width: 667"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]SKU Number[/TD]
[TD]AttributeName[/TD]
[TD]ProductAttributeValue[/TD]
[/TR]
[TR]
[TD]C01[/TD]
[TD]Width[/TD]
[TD]2.6"[/TD]
[/TR]
[TR]
[TD]C01[/TD]
[TD]Length[/TD]
[TD]4.4"[/TD]
[/TR]
[TR]
[TD]C01[/TD]
[TD]Type[/TD]
[TD]Delicate Duty[/TD]
[/TR]
[TR]
[TD]C01[/TD]
[TD]Color[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]C06[/TD]
[TD]Length[/TD]
[TD]13.8"[/TD]
[/TR]
[TR]
[TD]C06[/TD]
[TD]Height[/TD]
[TD]5"[/TD]
[/TR]
[TR]
[TD]C06[/TD]
[TD]Type[/TD]
[TD]Switchable Clenaing[/TD]
[/TR]
[TR]
[TD]C06[/TD]
[TD]Color[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]C29[/TD]
[TD]Width[/TD]
[TD]0.875"[/TD]
[/TR]
[TR]
[TD]C29[/TD]
[TD]Height[/TD]
[TD]3.25"[/TD]
[/TR]
[TR]
[TD]C29[/TD]
[TD]Length[/TD]
[TD]4.875"[/TD]
[/TR]
[TR]
[TD]C29[/TD]
[TD]Type[/TD]
[TD]Scouring[/TD]
[/TR]
[TR]
[TD]C30[/TD]
[TD]Size[/TD]
[TD]2-7/8" x 2-7/8"[/TD]
[/TR]
[TR]
[TD]C30[/TD]
[TD]Color[/TD]
[TD]Yellow[/TD]
[/TR]
</tbody>[/TABLE]
....


The way I would wish for it to return is:

[TABLE="width: 748"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SKU Number[/TD]
[TD]Color[/TD]
[TD]Width[/TD]
[TD]Length[/TD]
[TD]Type[/TD]
[TD]Height[/TD]
[TD]Size.....[/TD]
[/TR]
[TR]
[TD]C01[/TD]
[TD]White[/TD]
[TD]2.6"[/TD]
[TD]4.4"[/TD]
[TD]Delicate Duty[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C06[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD]13.8"[/TD]
[TD]Switchable Clenaing[/TD]
[TD]5"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C29[/TD]
[TD][/TD]
[TD]0.875"[/TD]
[TD]4.875"[/TD]
[TD]Scouring[/TD]
[TD]3.25"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C30[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2-7/8" x 2-7/8"[/TD]
[/TR]
</tbody>[/TABLE]


No big deal right? Well, the problem that I am having is that there are 60,000+ unique "sku numbers" and 3500+ unique "attribute names". Meaning, that when complete, the spreadsheet will have 3500+ columns and 60,000+ Rows. I cannot do a crosstab query in Access because there is over 255 rows. And, attempting to do a vlookup or index/match is almost impossible because it would involve searching and calculating over 210 million cells.

Can anyone help me out with a solution? Thank you.

 

[sheetspread]

I'd set up the row and column first (use the remove duplicates feature of Excel 2010 or the advanced filter for earlier versions):

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SKU Number	AttributeName	ProductAttributeValue				SKU Number	Color	Width	Length	Type	Height	Size
2	C01	Width	2.6"				C01
3	C01	Length	4.4"				C06
4	C01	Type	Delicate Duty				C29
5	C01	Color	White				C30
6	C06	Length	13.8"
7	C06	Height	5"
8	C06	Type	Switchable Clenaing
9	C06	Color	Yellow
10	C29	Width	0.875"
11	C29	Height	3.25"
12	C29	Length	4.875"
13	C29	Type	Scouring
14	C30	Size	2-7/8" x 2-7/8"
15	C30	Color	Yellow
Sheet4 (3)

Then paste this formula in H2 and copy across/down:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	SKU Number	AttributeName	ProductAttributeValue				SKU Number	Color	Width	Length	Type	Height	Size
2	C01	Width	2.6"				C01	White	2.6"	4.4"	Delicate Duty	#N/A	#N/A
3	C01	Length	4.4"				C06	Yellow	#N/A	13.8"	Switchable Clenaing	5"	#N/A
4	C01	Type	Delicate Duty				C29	#N/A	0.875"	4.875"	Scouring	3.25"	#N/A
5	C01	Color	White				C30	Yellow	#N/A	#N/A	#N/A	#N/A	2-7/8" x 2-7/8"
6	C06	Length	13.8"
7	C06	Height	5"
8	C06	Type	Switchable Clenaing
9	C06	Color	Yellow
10	C29	Width	0.875"
11	C29	Height	3.25"
12	C29	Length	4.875"
13	C29	Type	Scouring
14	C30	Size	2-7/8" x 2-7/8"
15	C30	Color	Yellow
Sheet4 (3)

Cell Formulas
Range	Formula
H2	{=INDEX($C$2:$C$15,MATCH($G2&H$1,$A$2:$A$15&$B$2:$B$15,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Then highlight the table, press f5(goto), special, formulas, uncheck all except errors, and delete

 

[sheetspread]

I just read what you wrote about the data being too large, try this method and tell me what happens. Excel 2010 has 1,048,576 rows by 16,384 columns---over 17 billion cells. The calculation might be slow, but I don't believe it will take forever, especially for a bulk export. Excel 2003's 65k something row limit is insufficient unless you can work around it.

 

[Axe383]

Hi,

Thanks for your response. I've tried this technique before. It works well with less data, but when I try to do all of the 60,000 skus and 3500 attributes, Excel "runs out of resources" and the file cannot finish. I don't know if there is some way that I could work around that

 

[sheetspread]

Yes all I can think of is splitting the data into smaller groups and using the lookup formula. Or append smaller Access crosstab queries to a table. I didn't know the Access version of SQL or Excel pivot tables printed summaries like this with text that can't be calculated, it would be interesting to see how.