Can A Macro Solve This Problem

[whynot]

I have rows of data in a spreadsheet that is listed sequentially. For example, 1Q03, 1Q04,1Q05 , 2Q03, 2Q04,2Q05,3Q03,3Q04,3Q05, 4Q03,4Q04,4Q05, and 2003, 2004, 2005...
i would like to rearrange the data so that all the data for 2003 is in sequential order: 1Q03, 2Q03, 3Q03, 4Q03, 2003, 1Q04,2Q04,3Q04,4Q04,2004...
i would also like to color the border a blue and change the quarters (1Q03) to transparent and color the year a specific color.
i have attached an example.
Thank you very much for the help.

KELYA 1.0.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
14	1Q04	1Q05	1Q06	1Q07	1Q08	1Q09	1Q10	1Q11	1Q12	1Q13	1Q14	1Q15	1Q16	1Q17	1Q18	1Q19	1Q20	1Q21	1Q22	2Q04	2Q05	2Q06	2Q07	2Q08	2Q09	2Q10	2Q11	2Q12	2Q13	2Q14	2Q15	2Q16	2Q17	2Q18	2Q19	2Q20	2Q21	2Q22E	3Q04	3Q05	3Q06	3Q07
15
16	$1,158.811	$1,249.335	$1,360.089	$1,350.858	$1,393.975	$1,043.00	$1,130.40	$1,339.10	$1,354.80	$1,314.80	$1,330.80	$1,320.60	$1,349.10	$1,289.70	$1,369.90	$1,382.60	$1,261.10	$1,205.90	$1,296.40	$1,224.464	$1,311.904	$1,416.409	$1,415.674	$1,455.847	$1,028.90	$1,209.40	$1,405.80	$1,366.10	$1,366.90	$1,410.50	$1,385.00	$1,375.50	$1,333.60	$1,386.90	$1,367.50	$975.30	$1,258.10	$1,294.53	$1,244.85	$1,344.64	$1,418.80	$1,425.30
17	15.5%	7.8%	8.9%	-0.7%	3.2%	-25.2%	8.4%	18.5%	1.2%	-3.0%	1.2%	-0.8%	2.2%	-4.4%	6.2%	0.9%	-8.8%	-5.5%	7.0%	15.6%	7.1%	8.0%	-0.1%	2.8%	-29.3%	17.5%	16.2%	-2.8%	0.1%	3.2%	-1.8%	-0.7%	-3.0%	4.0%	-1.4%	-28.7%	29.0%	2.9%	13.5%	8.0%	5.5%	0.6%
18
19	($975.455)	($1,045.251)	($1,140.727)	($1,121.650)	($1,138.557)	($867.10)	($950.40)	($1,125.40)	($1,131.10)	($1,097.90)	($1,108.50)	($1,100.30)	($1,116.40)	($1,058.10)	($1,131.70)	($1,131.00)	($1,037.80)	($992.60)	($1,037.80)	($1,026.382)	($1,097.802)	($1,186.186)	($1,166.108)	($1,194.605)	($857.20)	($1,018.50)	($1,183.70)	($1,142.90)	($1,146.20)	($1,182.40)	($1,162.70)	($1,145.00)	($1,104.80)	($1,146.40)	($1,123.50)	($786.10)	($1,027.10)	($1,038.21)	($1,042.486)	($1,126.466)	($1,180.207)	($1,176.919)
20	84.2%	83.7%	83.9%	83.0%	81.7%	83.1%	84.1%	84.0%	83.5%	83.5%	83.3%	83.3%	82.8%	82.0%	82.6%	81.8%	82.3%	82.3%	80.1%	83.8%	83.7%	83.7%	82.4%	82.1%	83.3%	84.2%	84.2%	83.7%	83.9%	83.8%	83.9%	83.2%	82.8%	82.7%	82.2%	80.6%	81.6%	80.2%	83.7%	83.8%	83.2%	82.6%
21	16.4%	7.2%	9.1%	-1.7%	1.5%	-23.8%	9.6%	18.4%	0.5%	-2.9%	1.0%	-0.7%	1.5%	-5.2%	7.0%	-0.1%	-8.2%	-4.4%	4.6%	15.7%	7.0%	8.1%	-1.7%	2.4%	-28.2%	18.8%	16.2%	-3.4%	0.3%	3.2%	-1.7%	-1.5%	-3.5%	3.8%	-2.0%	-30.0%	30.7%	1.1%	12.7%	8.1%	4.8%	-0.3%
22
23	$183.36	$204.08	$219.36	$229.21	$255.42	$175.90	$180.00	$213.70	$223.70	$216.90	$222.30	$220.30	$232.70	$231.60	$238.20	$251.60	$223.30	$213.07	$258.60	$198.08	$214.10	$230.22	$249.57	$261.24	$171.70	$190.90	$222.10	$223.20	$220.70	$228.10	$222.30	$230.50	$228.80	$240.50	$244.00	$189.20	$231.00	$256.32	$202.368	$218.178	$238.597	$248.38
24	15.8%	16.3%	16.1%	17.0%	18.3%	16.9%	15.9%	16.0%	16.5%	16.5%	16.7%	16.7%	17.2%	18.0%	17.4%	18.2%	17.7%	17.7%	19.9%	16.2%	16.3%	16.3%	17.6%	17.9%	16.7%	15.8%	15.8%	16.3%	16.1%	16.2%	16.1%	16.8%	17.2%	17.3%	17.8%	19.4%	18.4%	19.8%	16.3%	16.2%	16.8%	17.4%
25	10.8%	11.3%	7.5%	4.5%	11.4%	-31.1%	2.3%	18.7%	4.7%	-3.0%	2.5%	-0.9%	5.6%	-0.5%	2.8%	5.6%	-11.2%	-4.6%	21.4%	14.9%	8.1%	7.5%	8.4%	4.7%	-34.3%	11.2%	16.3%	0.5%	-1.1%	3.4%	-2.5%	3.7%	-0.7%	5.1%	1.5%	-22.5%	22.1%	11.0%	17.2%	7.8%	9.4%	4.1%
26
27	($181.342)	($197.989)	($205.925)	($218.715)	($236.947)	($201.90)	($176.20)	($208.10)	($209.00)	($209.80)	($216.00)	($208.20)	($218.00)	($213.50)	($226.20)	($229.30)	($210.80)	($202.70)	($235.20)	($189.404)	($200.494)	($208.897)	($225.300)	($242.448)	($193.60)	($180.90)	($200.80)	($199.40)	($201.80)	($220.40)	($210.80)	($220.60)	($208.50)	($220.10)	($221.00)	($178.30)	($217.30)	($234.31)	($189.908)	($200.849)	($213.345)	($226.099)
28	15.6%	15.8%	15.1%	16.2%	17.0%	19.4%	15.6%	15.5%	15.4%	16.0%	16.2%	15.8%	16.2%	16.6%	16.5%	16.6%	16.7%	16.8%	18.1%	15.5%	15.3%	14.7%	15.9%	16.7%	18.8%	15.0%	14.3%	14.6%	14.8%	15.6%	15.2%	16.0%	15.6%	15.9%	16.2%	18.3%	17.3%	18.1%	15.3%	14.9%	15.0%	15.9%
29	9.8%	9.2%	4.0%	6.2%	8.3%	-14.8%	-12.7%	18.1%	0.4%	0.4%	3.0%	-3.6%	4.7%	-2.1%	5.9%	1.4%	-8.1%	-3.8%	16.0%	11.4%	5.9%	4.2%	7.9%	7.6%	-20.1%	-6.6%	11.0%	-0.7%	1.2%	9.2%	-4.4%	4.6%	-5.5%	5.6%	0.4%	-19.3%	21.9%	7.8%	11.8%	5.8%	6.2%	6.0%
Quarterly Model

Cell Formulas
Range	Formula
G16:M16,O16:S16	G16	='Quarterly Segment'!C45
Y16	Y16	='new segment breakdown'!H117
Z16:AE16,AI16:AJ16	Z16	='Quarterly Segment'!S45
AO16	AO16	='new segment breakdown'!L16+'new segment breakdown'!L29+'new segment breakdown'!L42-4.1
AR16	AR16	='new segment breakdown'!O117
AS16:AV16	AS16	='Quarterly Segment'!AI45
G17	G17	=G16/B16-1
H17:W17,AA17:AQ17,AT17:AU17	H17	=H16/G16-1
Z17	Z17	=Z16/C16-1
AR17	AR17	=+(AR16-AQ16)/AQ16
AS17	AS17	=AS16/D16-1
AV17	AV17	=AV16/AB16-1
AC19	AC19	=-1168.108+2
AO19	AO19	=AO23-AO16
AR19	AR19	=AR20*-AR16
AV19	AV19	=-1178.419+1.5
G20:AQ20,AS20:AV20	G20	=-G19/G16
G21,G29,G25	G21	=G19/B19-1
H21:Y21,AA21:AR21,AT21:AV21,H29:Y29,AA29:AR29,AT29:AV29,H25:Y25,AA25:AR25,AT25:AV25	H21	=H19/G19-1
Z21,Z29,Z25	Z21	=Z19/C19-1
AS21,AS29,AS25	AS21	=AS19/D19-1
G23:W23,Y23:AN23,AP23:AV23	G23	=G16+G19
X23	X23	='new segment breakdown'!G18+'new segment breakdown'!G31+'new segment breakdown'!F44
G24:AV24	G24	=G23/G16
L27	L27	=-206.1+4.2
T27	T27	=-215.2+1.7
V27	V27	=-234.8+5.5
AJ27	AJ27	=-222.2+1.8
AR27	AR27	=-AR16*AR28
G28:AQ28,AS28:AV28	G28	=-G27/G16

KELYA 1.0.xlsx
	B	C	D	E	F
14	1Q03	2Q03	3Q03	4Q03	2003
15
16	$1,003.397	$1,059.517	$1,097.27	$1,164.97	$4,325.16
17
18
19	($837.845)	($887.113)	($924.661)	($978.905)	($3,628.52)
20	83.5%	83.7%	84.3%	84.0%	83.9%
21
22
23	$165.55	$172.40	$172.607	$186.068	$696.63
24	16.5%	16.3%	15.7%	16.0%	16.1%
25
26
27	($165.162)	($169.955)	($169.898)	($182.879)	($687.89)
28	16.5%	16.0%	15.5%	15.7%	15.9%
29
30
31	$0.390	$2.449	$2.709	$3.189	$8.74
32	0.0%	0.2%	0.2%	0.3%	0.2%
33
34
35	$0.122	$0.004	($0.084)	($0.119)	($0.08)
36	0.01%	0.00%	0.008%	0.010%	0.002%
37
38
39	$0.512	$2.453	$2.625	$3.070	$8.66
40	0.1%	0.2%	0.2%	0.3%	0.2%
41
Quarterly Model

Cell Formulas
Range	Formula
B16	B16	='Quarterly Segment'!B45
C16	C16	='Quarterly Segment'!R45
D16	D16	='Quarterly Segment'!AH45
E16	E16	='Quarterly Segment'!AX45
F16,F39,F35,F31,F27,F23,F19	F16	=B16+C16+D16+E16
B20:F20	B20	=-B19/B16
B23:E23	B23	=B16+B19
B24:F24	B24	=B23/B16
B28:F28	B28	=-B27/B16
B31:E31,B39:E39	B31	=B23+B27
B32:F32	B32	=B31/B16
B36:C36	B36	=B35/B16
D36:F36	D36	=-D35/D16
B40:F40	B40	=B39/B16

 

[Jeffrey Mahoney]

I think it would faster to do it manually

 

[whynot]

I think it would faster to do it manually

Thank you. Unfortunately, I have 15 years of data. So manually it will take a long time.

 

[Jeffrey Mahoney]

I can move 60 columns in less than 30 minutes. How about you?

 

[Jeffrey Mahoney]

I could maybe write a macro for that in about an hour. If I had to do that more than once, yup, I would definitely write one.