Rearraging data in excel - macro?

excelhelp1235 · Mar 11, 2016

Hi,

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

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

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

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

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

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

Thanks for any help

StephenCrump · Mar 11, 2016

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

Rearraging data in excel - macro?

excelhelp1235

New Member

StephenCrump

MrExcel MVP

Similar threads

Share this page

Rearraging data in excel - macro?

excelhelp1235

New Member

StephenCrump

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock