Transposing Product Attributes from Columns to Rows

Axe383

New Member
Joined
Aug 28, 2013
Messages
2
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 NumberAttributeNameProductAttributeValue
C01Width2.6"
C01Length4.4"
C01TypeDelicate Duty
C01ColorWhite
C06Length13.8"
C06Height5"
C06TypeSwitchable Clenaing
C06ColorYellow
C29Width0.875"
C29Height3.25"
C29Length4.875"
C29TypeScouring
C30Size2-7/8" x 2-7/8"
C30ColorYellow

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


The way I would wish for it to return is:

SKU NumberColorWidthLengthTypeHeightSize.....
C01White2.6"4.4"Delicate Duty
C06Yellow13.8"Switchable Clenaing5"
C290.875"4.875"Scouring3.25"
C30Yellow2-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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
I'd set up the row and column first (use the remove duplicates feature of Excel 2010 or the advanced filter for earlier versions):

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">SKU Number</td><td style=";">AttributeName</td><td style=";">ProductAttributeValue</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SKU Number</td><td style=";">Color</td><td style=";">Width</td><td style=";">Length</td><td style=";">Type</td><td style=";">Height</td><td style=";">Size</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">C01</td><td style=";">Width</td><td style=";">2.6"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C01</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">C01</td><td style=";">Length</td><td style=";">4.4"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C06</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">C01</td><td style=";">Type</td><td style=";">Delicate Duty</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">C01</td><td style=";">Color</td><td style=";">White</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">C06</td><td style=";">Length</td><td style=";">13.8"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">C06</td><td style=";">Height</td><td style=";">5"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">C06</td><td style=";">Type</td><td style=";">Switchable Clenaing</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">C06</td><td style=";">Color</td><td style=";">Yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">C29</td><td style=";">Width</td><td style=";">0.875"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">C29</td><td style=";">Height</td><td style=";">3.25"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">C29</td><td style=";">Length</td><td style=";">4.875"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">C29</td><td style=";">Type</td><td style=";">Scouring</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">C30</td><td style=";">Size</td><td style=";">2-7/8" x 2-7/8"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">C30</td><td style=";">Color</td><td style=";">Yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4 (3)</p><br /><br />

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">SKU Number</td><td style=";">AttributeName</td><td style=";">ProductAttributeValue</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">SKU Number</td><td style=";">Color</td><td style=";">Width</td><td style=";">Length</td><td style=";">Type</td><td style=";">Height</td><td style=";">Size</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">C01</td><td style=";">Width</td><td style=";">2.6"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C01</td><td style=";">White</td><td style=";">2.6"</td><td style=";">4.4"</td><td style=";">Delicate Duty</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">C01</td><td style=";">Length</td><td style=";">4.4"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C06</td><td style=";">Yellow</td><td style="text-align: right;;">#N/A</td><td style=";">13.8"</td><td style=";">Switchable Clenaing</td><td style=";">5"</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">C01</td><td style=";">Type</td><td style=";">Delicate Duty</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C29</td><td style="text-align: right;;">#N/A</td><td style=";">0.875"</td><td style=";">4.875"</td><td style=";">Scouring</td><td style=";">3.25"</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">C01</td><td style=";">Color</td><td style=";">White</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C30</td><td style=";">Yellow</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style=";">2-7/8" x 2-7/8"</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">C06</td><td style=";">Length</td><td style=";">13.8"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">C06</td><td style=";">Height</td><td style=";">5"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">C06</td><td style=";">Type</td><td style=";">Switchable Clenaing</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">C06</td><td style=";">Color</td><td style=";">Yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">C29</td><td style=";">Width</td><td style=";">0.875"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">C29</td><td style=";">Height</td><td style=";">3.25"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">C29</td><td style=";">Length</td><td style=";">4.875"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">C29</td><td style=";">Type</td><td style=";">Scouring</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">C30</td><td style=";">Size</td><td style=";">2-7/8" x 2-7/8"</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">C30</td><td style=";">Color</td><td style=";">Yellow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4 (3)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">{=INDEX(<font color="Blue">$C$2:$C$15,MATCH(<font color="Red">$G2&H$1,$A$2:$A$15&$B$2:$B$15,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

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

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
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

New Member
Joined
Aug 28, 2013
Messages
2
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,182
Messages
5,623,229
Members
415,957
Latest member
Newguy1924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top