Results 1 to 2 of 2

Thread: Rearraging data in excel - macro?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Rearraging data in excel - macro?

    Hi,

    I need help rearranging a large amount of data in Excel.

    The current format is like this (Sample of the data)

    Company name Leverage 2012 Leverage 2011 Leverage 2010 Leverage 2009 Leverage 2008 Leverage 2007 Leverage 2006 Leverage 2005 Levergae 2004 Leverage 2003 Market Cap Market Cap. Market Cap. Market Cap. Market Cap. Market Cap. Market Cap. Market Cap. Market Cap.
    % % % % % % % % % % th USD th USD th USD th USD th USD th USD th USD th USD th USD
    2012 2011 2010 2009 2008 2007 2006 2005 2004 2003 2012 2011 2010 2009 2008 2007 2005 2006 2004
    3M COMPANY 63.65 74.17 61.76 79.93 117.67 72.24 65.45 57.53 61.22 74.00 63,796,459 57,280,038 61,692,341 58,526,909 39,872,633 60,139,467 60,579,270 60,915,387 64,151,493
    ACCENTURE PLC 106.38 101.84 121.03 111.49 118.74 84.57 91.44 143.15 145.03 297.16 39,103,722 34,534,621 23,324,082 20,270,843 24,865,691 25,957,029 14,028,414 17,081,698 10,297,406
    ADVANCE AUTO PARTS, INC. 69.72 73.22 45.00 25.42 48.82 54.35 52.24 55.85 76.17 82.39 5,307,890 5,044,406 5,560,105 3,830,672 3,232,760 6,710,016 4,669,886 3,768,471 3,268,574
    AIR PRODUCTS & CHEMICALS INC 121.18 107.41 106.32 129.09 106.56 88.09 82.97 89.26 93.06 113.32 17,508,122 16,276,965 17,602,262 16,298,138 14,508,785 24,386,778 12,643,878 15,218,973 12,469,606
    AIRGAS INC 229.22 151.44 152.23 124.47 153.80 148.58 161.76 124.95 141.41 145.59 7,527,956 6,794,121 5,591,755 5,263,259 2,749,477 3,750,577 3,021,345 3,357,671 1,618,977
    ALCOA INC 162.91 149.58 151.80 171.55 160.75 99.60 110.61 99.60 98.42 124.86 9,263,313 9,206,227 15,719,986 15,706,972 9,011,574 30,999,817 25,728,886 26,111,731 27,336,720
    ALTRIA GROUP, INC. 800.79 713.02 488.64 624.01 614.57 118.59 100.64 138.67 159.90 204.83 63,669,336 60,972,929 51,402,312 40,677,506 31,029,590 159,195,666 151,940,059 174,511,451 124,244,342
    AMEREN CORP 200.77 178.31 181.73 182.52 186.86 159.78 163.91 155.09 167.30 191.35 7,453,737 7,969,256 6,717,293 5,786,256 6,885,541 11,222,646 7,030,897 11,082,457 6,879,960
    AMERICAN ELECTRIC POWER COMPANY INC 226.28 220.82 230.70 239.08 265.49 254.54 256.80 248.29 261.37 310.56 20,710,432 19,970,199 17,299,441 16,631,513 13,514,051 18,643,868 11,951,696 16,843,499 11,065,550
    AMETEK INC 82.66 86.63 89.57 85.55 103.95 88.68 87.61 89.12 81.55 95.54 9,128,920 6,740,300 6,281,951 4,123,747 3,224,136 5,025,096 2,840,053 3,370,420 2,381,398
    AMGEN INCORPORATED 154.99 127.03 64.57 57.74 55.81 70.46 51.04 25.68 33.15 22.01 66,146,021 56,282,907 51,870,367 57,256,671 61,187,334 50,510,087 102,657,815 88,924,106 83,508,736
    AMPHENOL CORP 82.19 75.12 44.83 55.57 74.87 70.47 93.91 133.83 117.20 201.30 10,374,145 7,522,904 9,216,001 7,919,737 4,215,214 8,266,388 3,872,838 5,432,124 3,214,824
    ANADARKO PETROLEUM CORP 135.57 159.87 130.79 132.33 138.68 172.63 300.52 83.38 97.82 118.99 37,137,107 38,010,166 37,744,324 30,680,143 17,695,978 30,635,058 23,569,821 20,465,280 16,122,005
    ARCHER-DANIELS-MIDLAND COMPANY 50.16 55.85 59.62 66.91 68.03 53.87 54.83 59.53 66.00 70.69 18,038,863 19,232,088 16,603,547 17,186,327 21,732,392 22,234,473 14,189,114 27,396,008 11,136,266
    AT&T INC. 164.19 130.26 115.93 134.07 141.27 109.17 103.02 127.17 125.28 126.97 191,472,795 179,202,239 173,635,795 165,405,034 167,950,500 252,051,384 95,836,422 226,303,864 85,566,710
    AUTONATION INC 138.74 97.87 72.35 54.20 63.40 61.25 50.84 16.78 24.39 27.34 4,833,991 5,158,046 4,173,514 3,341,187 1,747,310 2,880,865 5,596,969 7,225,833 6,387,863
    AVERY DENNISON CORPORATION 124.62 114.21 121.71 169.39 165.30 145.20 82.32 101.27 107.69 120.57 3,477,790 3,045,734 4,632,730 4,114,053 3,478,727 5,658,389 6,425,580 7,897,406 6,971,992
    BAKER HUGHES INC 38.52 36.98 43.11 35.10 45.56 30.90 35.14 37.44 45.48 62.47 17,958,564 21,229,700 24,655,917 12,544,213 9,862,512 25,804,848 19,887,459 24,559,707 13,961,794
    BEST BUY CO, INC 113.13 92.07 45.82 48.09 60.35 34.30 17.53 18.43 21.53 32.06 5,449,621 9,543,863 12,708,893 15,258,168 11,915,024 18,042,006 26,481,831 22,274,849 17,151,621
    BOSTON SCIENTIFIC CORP 123.96 71.65 77.26 80.13 81.48 86.81 84.72 56.89 50.73 50.49 7,867,197 7,911,241 11,506,980 13,593,870 11,621,793 17,337,839 20,161,588 14,143,573 29,266,821
    BROWN FORMAN CORP 48.67 58.25 49.87 53.74 40.58 62.62 38.13 53.51 84.98 109.05 7,410,709 6,367,752 5,252,720 4,352,805 4,347,784 4,236,216 6,659,997 4,877,140 5,623,197
    CA, INC. 52.11 53.05 55.85 66.87 80.19 111.35 105.83 51.26 62.46 74.65 11,480,158 13,384,363 12,333,082 12,205,023 9,137,256 11,551,318 15,756,821 13,663,878 15,693,122
    CAMERON INTERNATIONAL CORPORATION 44.06 42.16 35.49 39.07 68.58 45.43 68.24 36.90 49.44 51.82 13,929,480 12,061,426 12,305,464 9,225,206 4,500,105 10,527,221 4,695,754 6,017,143 2,721,064
    CARDINAL HEALTH INC 69.15 67.61 64.63 51.41 70.63 58.74 41.92 42.48 61.16 48.33 14,535,186 15,923,638 12,169,240 11,009,977 18,406,991 34,357,517 26,188,824 29,258,893 31,860,493
    CARMAX INC 211.46 194.55 201.35 13.84 28.46 29.14 22.02 24.94 20.35 18.32 8,763,983 6,953,920 7,977,821 4,499,519 2,078,370 4,009,215 3,236,009 5,427,678 3,398,736
    CARNIVAL CORPORATION 40.23 41.96 43.67 48.94 53.04 47.57 46.23 46.51 53.96 57.09 22,982,790 19,767,841 25,788,544 19,995,328 13,110,143 28,154,804 33,397,085 30,026,119 32,489,987
    CATERPILLAR INC 280.21 354.25 324.29 435.06 682.80 339.25 426.14 288.05 311.24 349.90 58,697,911 58,666,520 59,832,138 35,602,949 26,870,194 46,145,274 39,479,441 40,845,781 33,558,379
    CENTURYLINK, INC. 162.07 152.11 118.09 125.72 147.10 126.68 118.70 101.62 115.70 115.52 24,421,206 22,974,945 14,000,895 10,773,386 2,736,783 4,576,472 4,626,152 6,091,007 4,948,420
    CERNER CORP 12.36 12.58 12.41 14.85 19.75 25.05 30.60 40.99 34.72 41.09 13,297,937 10,376,106 7,849,446 6,729,721 3,173,606 4,509,508 3,165,032 3,478,384 1,851,113
    CINTAS CORP 73.70 70.20 41.54 44.49 52.72 46.31 44.45 28.82 32.16 40.09 4,789,452 4,773,214 3,974,616 3,558,483 4,536,740 6,085,921 6,724,996 7,056,498 7,559,582
    CMS ENERGY CORP 397.03 401.02 413.61 392.64 398.42 421.37 442.65 448.80 515.27 576.03 6,465,667 5,598,621 4,549,108 3,595,645 2,286,959 3,912,082 1,587,292 3,714,665 1,143,157
    COACH INC 20.85 26.69 28.80 24.12 20.55 6.88 8.09 4.61 11.76 6.87 16,828,662 19,374,923 10,913,608 8,536,653 9,856,706 17,588,065 12,676,129 11,297,057 8,372,064
    COCA-COLA COMPANY (THE) 82.77 82.49 79.62 38.09 36.75 38.78 24.74 19.96 36.79 40.38 162,587,105 158,918,120 152,720,150 132,079,344 104,734,646 141,824,639 99,607,827 113,023,019 102,894,312
    COLGATE PALMOLIVE CO 352.63 293.85 199.25 152.28 270.27 210.01 356.84 353.36 413.37 502.84 49,393,239 44,716,695 38,793,277 40,844,252 34,593,150 39,741,031 31,953,964 33,691,240 29,804,281
    COMCAST CORPORATION 205.20 208.72 152.74 149.62 162.93 158.73 153.11 145.07 140.35 146.43 79,162,355 49,683,746 45,517,904 34,783,415 34,789,387 37,498,096 59,413,252 97,027,893 76,283,678
    CONAGRA FOODS, INC. 108.46 106.58 102.32 101.72 88.22 100.15 102.72 116.50 141.98 144.44 10,446,397 10,398,101 10,773,604 8,314,028 11,498,177 14,441,267 14,047,492 12,140,472 15,105,755
    CONSOLIDATED EDISON, INC. 219.91 215.54 200.01 202.38 210.51 171.50 194.52 186.44 184.92 189.65 16,266,161 18,168,875 14,401,874 12,515,596 10,652,402 13,263,547 10,794,890 11,200,310 10,193,750
    CONSTELLATION BRANDS, INC. 144.30 133.20 155.52 168.17 263.95 209.62 138.90 112.31 142.24 101.72 7,084,404 3,845,761 3,776,123 2,980,890 2,545,346 3,685,063 5,077,035 5,131,403 4,684,945
    CORNING INC 27.97 22.30 23.38 27.58 28.56 34.01 48.58 61.38 109.92 70.55 18,650,359 20,399,096 30,194,820 30,048,165 14,812,740 38,177,164 16,050,168 29,268,089 9,608,875
    CSX CORP 216.36 222.80 201.83 187.13 200.73 172.26 159.44 178.97 226.99 207.26 20,349,086 22,112,010 24,176,093 19,035,202 12,808,433 18,490,331 11,019,183 15,038,799 8,699,012
    CVS HEALTH CORPORATION 38.31 38.35 38.47 43.83 39.19 40.49 40.29 32.53 39.09 22.51 60,277,585 53,080,482 47,245,477 45,433,589 41,277,192 58,706,417 21,139,435 24,732,018 17,216,740
    DANAHER CORP 51.40 53.07 38.12 45.13 51.00 64.02 56.75 39.33 45.39 51.49 38,721,257 32,285,778 30,847,021 24,156,860 18,093,095 27,234,242 15,106,116 22,260,813 15,547,547



    And i need to rearrange it so its in the format:

    Company Year Leverage Market Cap ROA P/B Ratio Fixed Assets
    3M COMPANY 2003 74.00 63,796,459 etc etc etc
    3M COMPANY 2004 61.22 57,280,038
    3M COMPANY 2005 57.53 61,692,341
    3M COMPANY 2006 65.45 58,526,909
    3M COMPANY 2007 72.24 39,872,633
    3M COMPANY 2008 117.67 60,139,467
    3M COMPANY 2009 79.93 60,579,270
    3M COMPANY 2010 61.76 60,915,387
    3M COMPANY 2011 74.17 64,151,493
    3M COMPANY 2012 63.65 66,465,229
    ACCENTURE PLC 2003 106.38
    ACCENTURE PLC 2004 101.84
    ACCENTURE PLC 2005 121.03
    ACCENTURE PLC 2006 111.49
    ACCENTURE PLC 2007 118.74
    ACCENTURE PLC 2008 84.57
    ACCENTURE PLC 2009 91.44
    ACCENTURE PLC 2010 143.15
    ACCENTURE PLC 2011 145.03
    ACCENTURE PLC 2012 297.16
    etc
    etc

    Does anyone have an idea of a quick way to do this?

    Thanks for any help

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearraging data in excel - macro?

    Welcome to the Forum!

    Here's a VBA approach. It could also be done using relatively simple formulae if you'd prefer.

    I've assumed your data (excluding headers) is in A6:U47 (including a 2003 column for market cap, which wasn't included in the post) and that the column progression is always 2012 ---> 2003 (i.e. 2005 and 2006 headers for market cap were just accidentally transposed?)

    I suggest you test on the data you've posted (works for me!) and then try to tweak the code as indicated for your full dataset.

    Code:
    Sub Rearrange()
    
        Dim vIn As Variant, vOut As Variant
        Dim lNoYears As Long, lOutputFields As Long, i As Long, j As Long
        
        lNoYears = 10 '2003 to 2012 inclusive
        lOutputFields = 4   'expand as required
        vIn = Range("A6:U47").Value     'Data excluding headers, expand columns as required
        ReDim vOut(1 To UBound(vIn) * lNoYears, 1 To lOutputFields)
        
        For i = 1 To UBound(vIn)
            For j = 1 To lNoYears
                vOut(10 * (i - 1) + j, 1) = vIn(i, 1)
                vOut(10 * (i - 1) + j, 2) = 2002 + j
                vOut(10 * (i - 1) + j, 3) = vIn(i, 12 - j) 'Leverage
                vOut(10 * (i - 1) + j, 4) = vIn(i, 22 - j) 'Market Cap
                'vOut(10 * (i - 1) + j, 5) = vIn(i, 32 - j) 'Next field
                'etc
            Next j
        Next i
        
        Worksheets("Sheet2").Range("A1").Resize(UBound(vOut), UBound(vOut, 2)).Value = vOut
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •