Help : Copying excel Rows to columns.

[Creekleo]

Hello community,
I have a problem and would like to find a solution if possible.
I tried to explain all in the excel file attached. thank you

Canevas.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Main table
2				January		February		March		April		May		June		July		August		September		October		November		December
3	Code	AGENCY	PRODUCTS	N	V	N	V	N	V	N	V	N	V	N	V	N	V	N	V	N	V	N	V	N	V	N	V
4	5244	AGENCY 1	1	203	480,784	223	484,761	269	599,479	221	532,471	224	529,479	226	570,051	228	628,523	220	649,023	245	649,109	253	600,831	262	612,235	284	663,254
5	5244	AGENCY 1	2	79		87		105		86		87		88		89		86		95		98		102		111
6	5244	AGENCY 1	3	39	103,025	43	103,877	52	128,460	43	114,101	44	113,460	44	122,154	44	134,683	43	139,076	48	139,095	49	128,749	51	131,193	55	142,126
7	5244	AGENCY 1	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	5244	AGENCY 1	5	24	68,683	27	69,252	33	85,640	27	76,067	27	75,640	27	81,436	27	89,789	27	92,718	30	92,730	30	85,833	32	87,462	34	94,751
9	5244	AGENCY 1	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	5244	AGENCY 1	7	22	54,947	24	55,401	29	68,512	24	60,854	24	60,512	24	65,149	25	71,831	24	74,174	26	74,184	27	68,666	28	69,970	31	75,801
11	5244	AGENCY 1	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	5244	AGENCY 1	9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	5244	AGENCY 1	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	5244	AGENCY 1	11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	5244	AGENCY 1	12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	5244	AGENCY 1	13	21		23		28		23		24		24		24		23		26		27		28		30
17	5244	AGENCY 1	14	7		8		10		8		8		8		8		8		9		9		10		10
18	5244	AGENCY 1	15	13		14		17		14		14		14		14		14		15		16		17		18
19	5244	AGENCY 1	16	0		0		0		0		0		0		0		0		0		0		0		0
20	5244	AGENCY 1	17	0		0		0		0		0		0		0		0		0		0		0		0
21
22	I want to copy the months highlighted in orange color above from columns to rows then copy products data then agency code then agency name and then N and V data to the table below and I need to do this for every month from january to december. The goal is to convert the table above into the format in the table here. I have so maany data and i can't be copying elements by elements if there is any fast solution to this. thanks so much.
23
24
25
26	Results wanted
27	Month	Products	Code agency	Agency	N	V
28	January	1	5244	AGENCY 1	203	480,784
29	January	2	5244	AGENCY 1	79
30	January	3	5244	AGENCY 1	39	103,025
31	January	4	5244	AGENCY 1	0	0
32	January	5	5244	AGENCY 1	24	68,683
33	January	6	5244	AGENCY 1	0	0
34	January	7	5244	AGENCY 1	22	54,947
35	January	8	5244	AGENCY 1	0	0
36	January	9	5244	AGENCY 1	0	0
37	January	10	5244	AGENCY 1	0	0
38	January	11	5244	AGENCY 1	0	0
39	January	12	5244	AGENCY 1	0	0
40	January	13	5244	AGENCY 1	21
41	January	14	5244	AGENCY 1	7
42	January	15	5244	AGENCY 1	13
43	January	16	5244	AGENCY 1	0
44	January	17	5244	AGENCY 1	0
45	February	1	5244	AGENCY 1	223	484,761
46	February	2	5244	AGENCY 1	87
47	February	3	5244	AGENCY 1	43	103,877
48	February	4	5244	AGENCY 1	0	0
49	February	5	5244	AGENCY 1	27	69,252
50	February	6	5244	AGENCY 1	0	0
51	February	7	5244	AGENCY 1	24	55,401
52	February	8	5244	AGENCY 1	0	0
53	February	9	5244	AGENCY 1	0	0
54	February	10	5244	AGENCY 1	0	0
55	February	11	5244	AGENCY 1	0	0
56	February	12	5244	AGENCY 1	0	0
57	February	13	5244	AGENCY 1	23
58	February	14	5244	AGENCY 1	8
59	February	15	5244	AGENCY 1	14
60	February	16	5244	AGENCY 1	0
61	February	17	5244	AGENCY 1	0
62	AND SO ON Until december DATA is done I will need to copy elements by elements.
63
64
65
66
67
68
69
70
71
72
Canevas OBJ nouvelle ouverture

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E45:F45	Cell Value	<0	text	NO
E47:F56	Cell Value	<0	text	NO
D46:D61	Expression	=MOD(ROW(),2)	text	NO
D46:D61	Expression	=MOD(ROW(),2)=0	text	NO
D46:D61	Expression	=MOD(ROW(),2)=0	text	NO
D45	Expression	=MOD(ROW(),2)	text	NO
D45	Expression	=MOD(ROW(),2)=0	text	NO
D45	Expression	=MOD(ROW(),2)=0	text	NO
E29:F44	Cell Value	<0	text	NO
E28:F28	Cell Value	<0	text	NO
D29:D44	Expression	=MOD(ROW(),2)	text	NO
D29:D44	Expression	=MOD(ROW(),2)=0	text	NO
D29:D44	Expression	=MOD(ROW(),2)=0	text	NO
D28	Expression	=MOD(ROW(),2)	text	NO
D28	Expression	=MOD(ROW(),2)=0	text	NO
D28	Expression	=MOD(ROW(),2)=0	text	NO
B5:B20	Expression	=MOD(ROW(),2)	text	NO
B5:B20	Expression	=MOD(ROW(),2)=0	text	NO
B5:B20	Expression	=MOD(ROW(),2)=0	text	NO
B4	Expression	=MOD(ROW(),2)	text	NO
B4	Expression	=MOD(ROW(),2)=0	text	NO
B4	Expression	=MOD(ROW(),2)=0	text	NO
D4:AA4	Cell Value	<0	text	NO
D6:AA15	Cell Value	<0	text	NO

 

[Creekleo]

Up please if someone can help !

 

[Alex Blakenburg]

The rearrangements of the data can be achieved via either VBA or Power Query. Which do you want to use ?

The output will not have your conditional formatting on it though. The banding can be done simply by using a table which the PQ output will do automatically.

PS: you have merged cells in Row 2 most seasoned excel user consider this a really bad idea and would use horizontal > center across selection to achieve an identical look. PQ will break unmerge those cells.

 

[Alex Blakenburg]

If you want to use Power Query, Leila Gharani's video here will walk you through the steps.
(13.5 mins)
I think the only change I made to the what is in the video was that since you have merged cells in the heading, after converting the data to a table I selected applied Alignment > Horizontal > Center across selection to each month in the headings row (which is now 1 row lower and on row 3)

 

[Creekleo]

Thank you so much Mr.Alex for the help ! I will try to apply all your remarks and get back to you if it worked. Thanks

 

[Creekleo]

I will try to use Power Query method.