Need to convert large table from horizontal to vertical data

[casey716]

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]

 

[sheetspread]

You can do this with 1. Power Query in BI Tools


or 2. The reverse pivot table:

https://www.youtube.com/watch?v=xmqTN0X-AgY

for option 2, concatenate the first 8 columns. Are all the check digits zero? If so, they can be deleted first, otherwise concatenate them to their UPC columns.