Need to convert large table from horizontal to vertical data

casey716

New Member
Joined
Jun 27, 2018
Messages
1
Good Morning,
I have been trying to write a VBA code all morning to get this table so that there is only one UPC column and one UPC check digit column for each row of data.
I need to have the data in columns A-I for each row of a different UPC. There are some rows that have up to 10 different UPCs and some that have only 2 UPC columns. I would like to convert that single row with 10 UPC columns into 10 separate rows and the single row with 2 UPC columns into 2 separate rows.

I appreciate it in advance, the original document has 50k+ rows already but we need to be able to sort easier than with the horizontal stacking currently.

[TABLE="width: 2479"]
<tbody>[TR]
[TD]POS Combined Description[/TD]
[TD]Sell UOM[/TD]
[TD]Sell Pack Qty[/TD]
[TD]Sell Retail[/TD]
[TD]Sell Category[/TD]
[TD]Purchase Cost[/TD]
[TD]Purchase Pack Qty[/TD]
[TD]Market Price Zone Number[/TD]
[TD]UPC 1[/TD]
[TD]UPC 1 check digit[/TD]
[TD]UPC 2[/TD]
[TD] UPC 2 check digit[/TD]
[TD]UPC 3[/TD]
[TD]UPC 3 check digit[/TD]
[TD]UPC 4[/TD]
[TD]UPC 4 check digit[/TD]
[TD]UPC 5[/TD]
[TD]UPC 5 check digit[/TD]
[TD]UPC 6[/TD]
[TD]UPC 6 check digit[/TD]
[TD]UPC 7[/TD]
[TD]UPC 7 check digit[/TD]
[TD]UPC 8[/TD]
[TD]UPC 8 check digit[/TD]
[TD]UPC 9[/TD]
[TD]UPC 9 check digit[/TD]
[TD]UPC 10[/TD]
[TD]UPC 10 check digit[/TD]
[TD]UPC 11[/TD]
[TD]UPC 11 check digit[/TD]
[TD]UPC 12[/TD]
[TD]UPC 12 check digit[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BOSTON HERALD DAILY[/TD]
[TD]DAILY[/TD]
[TD]1[/TD]
[TD]2.00[/TD]
[TD]Publications[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98238E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.98E+11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]POWER SVC DIESEL SUPPL 1[/TD]
[TD]16Z[/TD]
[TD]1[/TD]
[TD]6.09[/TD]
[TD]Auto Supplies[/TD]
[TD="align: right"]3.89[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]27854010160[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CITGO TRANSGAR EACH[/TD]
[TD]EACH[/TD]
[TD]1[/TD]
[TD]2.99[/TD]
[TD]Auto Supplies[/TD]
[TD="align: right"]21.78[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33159000201[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33159010200[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REPEL WINDSHIE WASHER[/TD]
[TD]WASHER[/TD]
[TD]1[/TD]
[TD]1.79[/TD]
[TD]Auto Supplies[/TD]
[TD="align: right"]5.44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]59934902256[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAR FRESH STRW EACH[/TD]
[TD]EACH[/TD]
[TD]1[/TD]
[TD]1.00[/TD]
[TD]Auto Supplies[/TD]
[TD="align: right"]13.34[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]76171103123[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SNOW BRUSH EACH[/TD]
[TD]EACH[/TD]
[TD]1[/TD]
[TD]4.99[/TD]
[TD]Auto Supplies[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]79062005186[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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
Back
Top