Need to convert large table from horizontal to vertical data

C

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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

S
  • Solved
Modifying LET formula to include an additional data check
Replies
7
Views
321
Cubist
Cubist
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
533
sspatriots
S
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
03856me
  • Question Question
Approach moving data from UserForm to Table
Replies
5
Views
626
NoSparks
N
K
  • Question Question
complicated macro transpose horizontal data through multiple sheets
Replies
2
Views
550
KalilMe
K

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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