Macro to rearrange structure of report.

Carl_H

New Member
Joined
Sep 16, 2014
Messages
10
Hello,

Is it possible to change the structure of this report:




To look like this:
[IMG]http://i58.tinypic.com/2924avk.jpg[/IMG]

if anyone can help me I would be very grateful.

My report has a lot more rows than above but the columns are the same.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Carl_H

New Member
Joined
Sep 16, 2014
Messages
10
Hello,

I was thinking maybe it will be easier if a make a table that you can copy paste into an excelsheet. If you need further information I'll do my best to provide it for you.

Also there is 2 blanks between * and the cost center name, and 3 blanks before the cost element if that is important facts.
Act. per 8</SPAN>
Plan. per 8</SPAN>
Var. per 8</SPAN>
Cost centers/Cost elements</SPAN>
Act per 01 - 8</SPAN>
Plan version 3</SPAN>
1769</SPAN>
41666,66</SPAN>
-39897,66</SPAN>
591202 Customer magazines, production</SPAN>
453677</SPAN>
500000</SPAN>
-140</SPAN>
0</SPAN>
-140</SPAN>
591816 Customer magazines</SPAN>
-2520</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
625001 Postal expenses</SPAN>
-1499990,5</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
655901 Other consultant fees</SPAN>
217271</SPAN>
0</SPAN>
148</SPAN>
0</SPAN>
148</SPAN>
659202 Hired Services Elanders (Publ Store)</SPAN>
752</SPAN>
0</SPAN>
1777</SPAN>
41666,66</SPAN>
-39889,66</SPAN>
* 19065 Customer magazines</SPAN>
-830810,5</SPAN>
500000</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
564901 Demo/test vehicle miscellaneous</SPAN>
107138</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
* 19066 VT Press Test</SPAN>
107138</SPAN>
0</SPAN>
0</SPAN>
4166,66</SPAN>
-4166,66</SPAN>
591101 Daily press media space</SPAN>
0</SPAN>
50000</SPAN>
0</SPAN>
41666,66</SPAN>
-41666,66</SPAN>
591201 Magazine media space</SPAN>
329504,25</SPAN>
500000</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
591802 Advertising</SPAN>
10000</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
595002 POS production</SPAN>
13158</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
597102 Photos production</SPAN>
1500</SPAN>
0</SPAN>
0</SPAN>
4583,34</SPAN>
-4583,34</SPAN>
597401 Internet</SPAN>
0</SPAN>
55000</SPAN>
0</SPAN>
8333,34</SPAN>
-8333,34</SPAN>
599701 Other advertising costs</SPAN>
136700</SPAN>
100000</SPAN>
0</SPAN>
8333,34</SPAN>
-8333,34</SPAN>
615001 Printed matter</SPAN>
11406</SPAN>
100000</SPAN>
0</SPAN>
67083,34</SPAN>
-67083,34</SPAN>
* 19067 VT Advertising</SPAN>
502268,25</SPAN>
805000</SPAN>
0</SPAN>
2083,34</SPAN>
-2083,34</SPAN>
564901 Demo/test vehicle miscellaneous</SPAN>
25120</SPAN>
25000</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
581003 Tickets other countries</SPAN>
0</SPAN>
0</SPAN>
14290</SPAN>
1250</SPAN>
13040</SPAN>
597102 Photos production</SPAN>
14290</SPAN>
15000</SPAN>
0</SPAN>
28750</SPAN>
-28750</SPAN>
598401 Sponsorship</SPAN>
326058,2</SPAN>
345000</SPAN>
0</SPAN>
0</SPAN>
0</SPAN>
609801 Other sales costs</SPAN>
173,41</SPAN>
0</SPAN>
25119,13</SPAN>
0</SPAN>
25119,13</SPAN>
783501 Depreciation of cars</SPAN>
25119,13</SPAN>
0</SPAN>
39409,13</SPAN>
32083,34</SPAN>
7325,79</SPAN>
* 19069 VT PR/Media</SPAN>
390760,74</SPAN>
385000</SPAN>

<TBODY>
</TBODY>
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

With the additional headings removed try:

Code:
Sub Test()
    Dim ShNew As Worksheet
    Dim LastRow As Long
    Dim First As Long
    Dim r As Long
    Dim i As Long
    Set ShNew = Worksheets.Add
    ShNew.Range("A1:I1").Value = Array("Act. per 8", "Plan. per 8", "Var. per 8", "Cost center", "Cost center text", "Cost element", "Cost element text", "Act per 01 - 8", "Plan version 3")
    First = 2
    r = 2
    With Worksheets("Sheet1")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 2 To LastRow
            If Left(.Range("D" & i).Value, 1) <> "*" Then
                .Range("A" & i).Resize(, 3).Copy ShNew.Range("A" & r)
                ShNew.Range("F" & r).Value = Left(.Range("D" & i).Value, 6)
                ShNew.Range("G" & r).Value = Mid(.Range("D" & i).Value, 8)
                .Range("E" & i).Resize(, 2).Copy ShNew.Range("H" & r)
                r = r + 1
            Else
                ShNew.Range("D" & First & ":D" & r - 1).Value = Mid(Trim(.Range("D" & i).Value), 3, 5)
                ShNew.Range("E" & First & ":E" & r - 1).Value = Mid(Trim(.Range("D" & i).Value), 9)
                First = r
            End If
        Next i
    End With
End Sub
 

Carl_H

New Member
Joined
Sep 16, 2014
Messages
10
Hello,

First of all, thanks for your effort so far. It almost worked.

The result when I ran the macro can be found below: As you can see, last digit of cost center is missing, and if you paste the data below into an excel sheet you will also see that the cost element number only displays 3 digits in the correct column.

If you reply, I will not have time to check new result until tomorrow as I am about to leave work.

And by the way: I just started my new job and when I can start to use this macro I will be able to rebuild our report which will easily save me 30-40 hours a month, not having to do any copy paste which my former colleague seemed to like :) So once again. Thanks a lot!

EDIT: I did not include cost center 19066 below.
Act. per 8</SPAN>
Plan. per 8</SPAN>
Var. per 8</SPAN>
Cost center</SPAN>
Cost center text</SPAN>
Cost element</SPAN>
Cost element text</SPAN>
Act per 01 - 8</SPAN>
Plan version 3</SPAN>
4 166,66</SPAN>
4 166,66-</SPAN>
1906</SPAN>
VT Advertising</SPAN>
591</SPAN>
01 Daily press media space</SPAN>
50 000,00</SPAN>
41 666,66</SPAN>
41 666,66-</SPAN>
1906</SPAN>
VT Advertising</SPAN>
591</SPAN>
01 Magazine media space</SPAN>
329 504,25</SPAN>
500 000,00</SPAN>
1906</SPAN>
VT Advertising</SPAN>
591</SPAN>
02 Advertising</SPAN>
10 000,00</SPAN>
1906</SPAN>
VT Advertising</SPAN>
595</SPAN>
02 POS production</SPAN>
13 158,00</SPAN>
1906</SPAN>
VT Advertising</SPAN>
597</SPAN>
02 Photos production</SPAN>
1 500,00</SPAN>
4 583,34</SPAN>
4 583,34-</SPAN>
1906</SPAN>
VT Advertising</SPAN>
597</SPAN>
01 Internet</SPAN>
55 000,00</SPAN>
8 333,34</SPAN>
8 333,34-</SPAN>
1906</SPAN>
VT Advertising</SPAN>
599</SPAN>
01 Other advertising costs</SPAN>
136 700,00</SPAN>
100 000,00</SPAN>
8 333,34</SPAN>
8 333,34-</SPAN>
1906</SPAN>
VT Advertising</SPAN>
615</SPAN>
01 Printed matter</SPAN>
11 406,00</SPAN>
100 000,00</SPAN>
2 083,34</SPAN>
2 083,34-</SPAN>
1906</SPAN>
VT PR/Media</SPAN>
564</SPAN>
01 Demo/test vehicle miscellaneous</SPAN>
25 120,00</SPAN>
25 000,00</SPAN>
1906</SPAN>
VT PR/Media</SPAN>
581</SPAN>
03 Tickets other countries</SPAN>
14 290,00</SPAN>
1 250,00</SPAN>
13 040,00</SPAN>
1906</SPAN>
VT PR/Media</SPAN>
597</SPAN>
02 Photos production</SPAN>
14 290,00</SPAN>
15 000,00</SPAN>
28 750,00</SPAN>
28 750,00-</SPAN>
1906</SPAN>
VT PR/Media</SPAN>
598</SPAN>
01 Sponsorship</SPAN>
326 058,20</SPAN>
345 000,00</SPAN>
1906</SPAN>
VT PR/Media</SPAN>
609</SPAN>
01 Other sales costs</SPAN>
173,41</SPAN>
25 119,13</SPAN>
25 119,13</SPAN>
1906</SPAN>
VT PR/Media</SPAN>
783</SPAN>
01 Depreciation of cars</SPAN>
25 119,13</SPAN>

<TBODY>
</TBODY>
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

This is what I got with your sample data:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Act. per 8</td><td style=";">Plan. per 8</td><td style=";">Var. per 8</td><td style=";">Cost center</td><td style=";">Cost center text</td><td style=";">Cost element</td><td style=";">Cost element text</td><td style=";">Act per 01 - 8</td><td style=";">Plan version 3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1769</td><td style="text-align: right;;">41666.66</td><td style="text-align: right;;">-39897.66</td><td style="text-align: right;;">19065</td><td style=";">Customer magazines</td><td style="text-align: right;;">591202</td><td style=";">Customer magazines. production</td><td style="text-align: right;;">453677</td><td style="text-align: right;;">500000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">-140</td><td style="text-align: right;;">0</td><td style="text-align: right;;">-140</td><td style="text-align: right;;">19065</td><td style=";">Customer magazines</td><td style="text-align: right;;">591816</td><td style=";">Customer magazines</td><td style="text-align: right;;">-2520</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19065</td><td style=";">Customer magazines</td><td style="text-align: right;;">625001</td><td style=";">Postal expenses</td><td style="text-align: right;;">-1499990.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19065</td><td style=";">Customer magazines</td><td style="text-align: right;;">655901</td><td style=";">Other consultant fees</td><td style="text-align: right;;">217271</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">148</td><td style="text-align: right;;">0</td><td style="text-align: right;;">148</td><td style="text-align: right;;">19065</td><td style=";">Customer magazines</td><td style="text-align: right;;">659202</td><td style=";">Hired Services Elanders (Publ Store)</td><td style="text-align: right;;">752</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19066</td><td style=";">VT Press Test</td><td style="text-align: right;;">564901</td><td style=";">Demo/test vehicle miscellaneous</td><td style="text-align: right;;">107138</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4166.66</td><td style="text-align: right;;">-4166.66</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">591101</td><td style=";">Daily press media space</td><td style="text-align: right;;">0</td><td style="text-align: right;;">50000</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">41666.66</td><td style="text-align: right;;">-41666.66</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">591201</td><td style=";">Magazine media space</td><td style="text-align: right;;">329504.25</td><td style="text-align: right;;">500000</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">591802</td><td style=";">Advertising</td><td style="text-align: right;;">10000</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">595002</td><td style=";">POS production</td><td style="text-align: right;;">13158</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">597102</td><td style=";">Photos production</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4583.34</td><td style="text-align: right;;">-4583.34</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">597401</td><td style=";">Internet</td><td style="text-align: right;;">0</td><td style="text-align: right;;">55000</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">0</td><td style="text-align: right;;">8333.34</td><td style="text-align: right;;">-8333.34</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">599701</td><td style=";">Other advertising costs</td><td style="text-align: right;;">136700</td><td style="text-align: right;;">100000</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;">8333.34</td><td style="text-align: right;;">-8333.34</td><td style="text-align: right;;">19067</td><td style=";">VT Advertising</td><td style="text-align: right;;">615001</td><td style=";">Printed matter</td><td style="text-align: right;;">11406</td><td style="text-align: right;;">100000</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2083.34</td><td style="text-align: right;;">-2083.34</td><td style="text-align: right;;">19069</td><td style=";">VT PR/Media</td><td style="text-align: right;;">564901</td><td style=";">Demo/test vehicle miscellaneous</td><td style="text-align: right;;">25120</td><td style="text-align: right;;">25000</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19069</td><td style=";">VT PR/Media</td><td style="text-align: right;;">581003</td><td style=";">Tickets other countries</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">14290</td><td style="text-align: right;;">1250</td><td style="text-align: right;;">13040</td><td style="text-align: right;;">19069</td><td style=";">VT PR/Media</td><td style="text-align: right;;">597102</td><td style=";">Photos production</td><td style="text-align: right;;">14290</td><td style="text-align: right;;">15000</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">0</td><td style="text-align: right;;">28750</td><td style="text-align: right;;">-28750</td><td style="text-align: right;;">19069</td><td style=";">VT PR/Media</td><td style="text-align: right;;">598401</td><td style=";">Sponsorship</td><td style="text-align: right;;">326058.2</td><td style="text-align: right;;">345000</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19069</td><td style=";">VT PR/Media</td><td style="text-align: right;;">609801</td><td style=";">Other sales costs</td><td style="text-align: right;;">173.41</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">25119.13</td><td style="text-align: right;;">0</td><td style="text-align: right;;">25119.13</td><td style="text-align: right;;">19069</td><td style=";">VT PR/Media</td><td style="text-align: right;;">783501</td><td style=";">Depreciation of cars</td><td style="text-align: right;;">25119.13</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br />
 

Carl_H

New Member
Joined
Sep 16, 2014
Messages
10
Hello,

with the data I pasted at first it works beautifully. However, it seems extra "blanks" have been removed when I pasted the data. My data set looks like * "blank" "blank" 19065 Customer magazines (* 19065 Customer magazines) and for the cost elements there is e.g. "blank" "blank" "blank" 591202 Customer magazines, production ( 591202 Customer magazines, production).
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
How about?

Code:
Sub Test()
    Dim ShNew As Worksheet
    Dim LastRow As Long
    Dim First As Long
    Dim r As Long
    Dim i As Long
    Set ShNew = Worksheets.Add
    ShNew.Range("A1:I1").Value = Array("Act. per 8", "Plan. per 8", "Var. per 8", "Cost center", "Cost center text", "Cost element", "Cost element text", "Act per 01 - 8", "Plan version 3")
    First = 2
    r = 2
    With Worksheets("Sheet1")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 2 To LastRow
            If Left(.Range("D" & i).Value, 1) <> "*" Then
                .Range("A" & i).Resize(, 3).Copy ShNew.Range("A" & r)
                ShNew.Range("F" & r).Value = Left(WorksheetFunction.Trim(.Range("D" & i).Value), 6)
                ShNew.Range("G" & r).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 8)
                .Range("E" & i).Resize(, 2).Copy ShNew.Range("H" & r)
                r = r + 1
            Else
                ShNew.Range("D" & First & ":D" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 3, 5)
                ShNew.Range("E" & First & ":E" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 9)
                First = r
            End If
        Next i
    End With
End Sub
 

Carl_H

New Member
Joined
Sep 16, 2014
Messages
10
Now it works perfectly. Thank you so much for your help and time spent on this!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,069
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top