Rearraging data in excel - macro?

[excelhelp1235]

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

<colgroup><col><col span="19"></colgroup><tbody>
</tbody>

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

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

etc
etc

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

Thanks for any help

 

[StephenCrump]

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.

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