Rearranging data base

A

az365

New Member
Joined
Aug 12, 2018
Messages
5
Hi Guys,

Anyone can come up with an VBA which can help to rearranging database?

Please refer to the following.

Original:

[TABLE="width: 467"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Sum of VR1600v (FTTN)[/TD]
[/TR]
[TR]
[TD]Jan-17[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Feb-17[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mar-17[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Apr-17[/TD]
[TD="align: right"]756[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]May-17[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jun-17[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jul-17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Aug-17[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1108[/TD]
[/TR]
[TR]
[TD]Sep-17[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]780[/TD]
[/TR]
[TR]
[TD]Oct-17[/TD]
[TD="align: right"]864[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Nov-17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1700[/TD]
[/TR]
[TR]
[TD]Dec-17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7120[/TD]
[/TR]
[TR]
[TD]Jan-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]796[/TD]
[/TR]
[TR]
[TD]Feb-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]707[/TD]
[/TR]
[TR]
[TD]Mar-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]114[/TD]
[/TR]
[TR]
[TD]Apr-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11251[/TD]
[/TR]
[TR]
[TD]May-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1279[/TD]
[/TR]
[TR]
[TD]Jun-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1337[/TD]
[/TR]
[TR]
[TD]Jul-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1416[/TD]
[/TR]
[TR]
[TD]Aug-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]650[/TD]
[/TR]
[TR]
[TD]Sep-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Oct-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Nov-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Dec-18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Desire:

[TABLE="width: 432"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Jan-17[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Feb-17[/TD]
[TD="align: right"]1032[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Mar-17[/TD]
[TD="align: right"]1454[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Apr-17[/TD]
[TD="align: right"]756[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]May-17[/TD]
[TD="align: right"]1050[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Jun-17[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Jul-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Aug-17[/TD]
[TD="align: right"]840[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Sep-17[/TD]
[TD="align: right"]576[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Oct-17[/TD]
[TD="align: right"]864[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Nov-17[/TD]
[TD="align: right"]720[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Dec-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Jan-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Feb-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Mar-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Apr-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]May-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Jun-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Jul-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Aug-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Sep-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Oct-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Nov-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of HG658 VDSL - iiNet[/TD]
[TD]Dec-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Jan-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Feb-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Mar-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Apr-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]May-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Jun-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Jul-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Aug-17[/TD]
[TD="align: right"]11208[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Sep-17[/TD]
[TD="align: right"]7680[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Oct-17[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Nov-17[/TD]
[TD="align: right"]10700[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Dec-17[/TD]
[TD="align: right"]7120[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Jan-18[/TD]
[TD="align: right"]796[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Feb-18[/TD]
[TD="align: right"]7707[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Mar-18[/TD]
[TD="align: right"]11104[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Apr-18[/TD]
[TD="align: right"]11251[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]May-18[/TD]
[TD="align: right"]12079[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Jun-18[/TD]
[TD="align: right"]13317[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Jul-18[/TD]
[TD="align: right"]14816[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Aug-18[/TD]
[TD="align: right"]6504[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Sep-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Oct-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Nov-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sum of VR1600v (FTTN)[/TD]
[TD]Dec-18[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Basically

1. Put header in front of the date column.
2. Copy and paste the corresponding amount to the third column.

This case I show is simplified case. I have 50+ column I need to rearrange to this format.

I am thinking there will be some kind of VBA to loop through the process.

Much appreciate the help.

Logit I know you are here to help.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
You can actually do that without code using a consolidation range pivot table. I’ll dig out the instructions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
SUM XLOOKUP
Replies
5
Views
253
StephenCrump
S
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
660
Joe4
Joe4
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
866
Cubist
Cubist
ricafonyat
  • Solved
Total Revenue according certain periods
Replies
4
Views
147
Phuoc
P
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
218
Fluff
Fluff

Forum statistics

Threads
1,223,923
Messages
6,175,391
Members
452,640
Latest member
steveridge

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