VBA to transform data in pivot-friendly format ?

[mystic2k]

Hello all !

I am trying to automate the following process (check out the example below) :
- My data is provided in columns like the top table in the example (4 columns: Q1 - Q4).
- I want to translate this format to the one below to use as a pivot table source.

The macro would need to adapt itself to function with varying numbers of columns (there might be any number between 2 and 6 I'd say), and with columns of any size (usually between 5'000 and 10'000 rows). However I'm happy to fix the position of the columns (their top anyway) on the worksheet - that's absolutely ok since the macro will only be used on a specific worksheet.

If it makes any difference, we're using 2007.

I've tried doing it myself, but I'm an absolute beginner in VBA and I'm not comfortable at all with loops, and with data ranges of varying sizes. So could one of you point me in the right direction ? I've tried looking in the forum, but really didn't know how to search for this...

Thanks in advance !

<b>Example</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Country</td><td >Q1</td><td >Q2</td><td >Q3</td><td >Q4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >France</td><td style="text-align:right; ">100</td><td style="text-align:right; ">110</td><td style="text-align:right; ">130</td><td style="text-align:right; ">120</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >UK</td><td style="text-align:right; ">180</td><td style="text-align:right; ">160</td><td style="text-align:right; ">220</td><td style="text-align:right; ">190</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Germany</td><td style="text-align:right; ">110</td><td style="text-align:right; ">130</td><td style="text-align:right; ">170</td><td style="text-align:right; ">140</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Italy</td><td style="text-align:right; ">90</td><td style="text-align:right; ">110</td><td style="text-align:right; ">120</td><td style="text-align:right; ">110</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Country</td><td >Period</td><td >Amount</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >France</td><td style="text-align:center; ">Q1</td><td style="text-align:center; ">100</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >UK</td><td style="text-align:center; ">Q1</td><td style="text-align:center; ">180</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Germany</td><td style="text-align:center; ">Q1</td><td style="text-align:center; ">110</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Italy</td><td style="text-align:center; ">Q1</td><td style="text-align:center; ">90</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >France</td><td style="text-align:center; ">Q2</td><td style="text-align:center; ">110</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >UK</td><td style="text-align:center; ">Q2</td><td style="text-align:center; ">160</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Germany</td><td style="text-align:center; ">Q2</td><td style="text-align:center; ">130</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Italy</td><td style="text-align:center; ">Q2</td><td style="text-align:center; ">110</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >France</td><td style="text-align:center; ">Q3</td><td style="text-align:center; ">130</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >UK</td><td style="text-align:center; ">Q3</td><td style="text-align:center; ">220</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >Germany</td><td style="text-align:center; ">Q3</td><td style="text-align:center; ">170</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Italy</td><td style="text-align:center; ">Q3</td><td style="text-align:center; ">120</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >France</td><td style="text-align:center; ">Q4</td><td style="text-align:center; ">120</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >UK</td><td style="text-align:center; ">Q4</td><td style="text-align:center; ">190</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Germany</td><td style="text-align:center; ">Q4</td><td style="text-align:center; ">140</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >Italy</td><td style="text-align:center; ">Q4</td><td style="text-align:center; ">110</td><td >*</td><td >*</td></tr></table>

 

[Chantty]

First generate the pivot based on your data.

Then put the Q1 to Q4 in values fileds.

Then you can drag the value fileds in to row filed.

go to opition -> display -> adopt classic mode .

You can get the report like.

Values	Country	Total
Sum of Q1	France	100
	Germany	110
	Italy	90
	UK	180
Sum of Q2	France	110
	Germany	130
	Italy	110
	UK	160
Sum of Q3	France	130
	Germany	170
	Italy	120
	UK	220
Sum of Q4	France	120
	Germany	140
	Italy	110
	UK	190
Total Sum of Q1		480
Total Sum of Q2		510
Total Sum of Q3		640
Total Sum of Q4		560

 

[mystic2k]

Thanks for your suggestion Chantty but I really need the data in "single column" format to do what I want with the pivot table afterwards.

Also, I'm posting a new example which is more like my actual data : there are several leading columns which should remain "untouched" (probably the sheet explains this better than me). This is important because I tried some bit of code I found on the forum, which worked perfectly with my original example but not with my real data since it has several columns which must not be transposed.

Thanks in advance for any help !!!

<b>New example</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:96px;" /><col style="width:112px;" /><col style="width:102px;" /><col style="width:81px;" /><col style="width:81px;" /><col style="width:81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#ff0000; font-weight:bold; text-decoration:underline; ">Source data format</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-weight:bold; ">Country</td><td style="font-weight:bold; ">Account</td><td style="font-weight:bold; ">Legal entity</td><td style="font-weight:bold; text-align:right; ">Jan 09</td><td style="font-weight:bold; text-align:right; ">Feb 09</td><td style="font-weight:bold; text-align:right; ">Mar 09</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 1</td><td style="text-align:right; "> *611,773.30 </td><td style="text-align:right; "> *454,149.40 </td><td style="text-align:right; "> *439,679.70 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 2</td><td style="text-align:right; "> * * *5,208.00 </td><td style="text-align:right; "> * * *5,999.70 </td><td style="text-align:right; "> * * *6,577.10 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 3</td><td style="text-align:right; "> * * *1,479.50 </td><td style="text-align:right; "> * * *1,127.60 </td><td style="text-align:right; "> * * *1,263.30 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 4</td><td style="text-align:right; "> *193,877.50 </td><td style="text-align:right; "> *156,504.40 </td><td style="text-align:right; "> *508,874.40 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 5</td><td style="text-align:right; "> * *12,448.30 </td><td style="text-align:right; "> * *11,435.70 </td><td style="text-align:right; "> * *12,481.80 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#ff0000; font-weight:bold; text-decoration:underline; ">Desired output format</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-weight:bold; ">Country</td><td style="font-weight:bold; ">Account</td><td style="font-weight:bold; ">Legal entity</td><td style="font-weight:bold; ">Period</td><td style="font-weight:bold; ">Balance</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 1</td><td style="text-align:right; ">Jan 09</td><td style="text-align:right; "> *611,773.30 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 2</td><td style="text-align:right; ">Jan 09</td><td style="text-align:right; "> * * *5,208.00 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 3</td><td style="text-align:right; ">Jan 09</td><td style="text-align:right; "> * * *1,479.50 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 4</td><td style="text-align:right; ">Jan 09</td><td style="text-align:right; "> *193,877.50 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 5</td><td style="text-align:right; ">Jan 09</td><td style="text-align:right; "> * *12,448.30 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 1</td><td style="text-align:right; ">Feb 09</td><td style="text-align:right; "> *454,149.40 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 2</td><td style="text-align:right; ">Feb 09</td><td style="text-align:right; "> * * *5,999.70 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 3</td><td style="text-align:right; ">Feb 09</td><td style="text-align:right; "> * * *1,127.60 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 4</td><td style="text-align:right; ">Feb 09</td><td style="text-align:right; "> *156,504.40 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 5</td><td style="text-align:right; ">Feb 09</td><td style="text-align:right; "> * *11,435.70 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 1</td><td style="text-align:right; ">Mar 09</td><td style="text-align:right; "> *439,679.70 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 2</td><td style="text-align:right; ">Mar 09</td><td style="text-align:right; "> * * *6,577.10 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 3</td><td style="text-align:right; ">Mar 09</td><td style="text-align:right; "> * * *1,263.30 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 4</td><td style="text-align:right; ">Mar 09</td><td style="text-align:right; "> *508,874.40 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >United States</td><td >XXXXXXXX - YY</td><td >company 5</td><td style="text-align:right; ">Mar 09</td><td style="text-align:right; "> * *12,481.80 </td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr></table>