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:

SKU Number	AttributeName	ProductAttributeValue
C01	Width	2.6"
C01	Length	4.4"
C01	Type	Delicate Duty
C01	Color	White
C06	Length	13.8"
C06	Height	5"
C06	Type	Switchable Clenaing
C06	Color	Yellow
C29	Width	0.875"
C29	Height	3.25"
C29	Length	4.875"
C29	Type	Scouring
C30	Size	2-7/8" x 2-7/8"
C30	Color	Yellow

<colgroup><col><col><col></colgroup><tbody>
</tbody>

....


The way I would wish for it to return is:

SKU Number	Color	Width	Length	Type	Height	Size.....
C01	White	2.6"	4.4"	Delicate Duty
C06	Yellow		13.8"	Switchable Clenaing	5"
C29		0.875"	4.875"	Scouring	3.25"
C30	Yellow					2-7/8" x 2-7/8"

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.