Transpose yearly and monthly data

Welcome to the forum.

First, clear ALL data from column K oonwards.

Then, in K2:

=UNIQUE(F2:F26)

In L2:

=TRANSPOSE(UNIQUE(G3:G26))

In L3 copied down:

=TRANSPOSE(FILTER(H$3:H$26,(F$3:F$26=$K3),""))

Alternatively (also in the attached workbook), in Y2:

Year

In Y3:

=UNIQUE(--LEFT(A4:A1803,4))

In Z2:

=TRANSPOSE(UNIQUE(--MID(A4:A1803,6,2)))

In Z3 copied down:

=TRANSPOSE(FILTER(B$4:B$1803,(--LEFT(A$4:A$1803,4)=$Y3),""))

AliGW on MS365 Insider (Windows) 64 bit

	Y	Z	AA	AB	AC	AD	AE
2	Year	1	2	3	4	5	6
3	1950	-3.97088	-1.08387	2.395987	7.754243	10.45857	16.31512
4	1951	-3.26019	-0.66962	1.486795	5.467989	10.93503	15.0262
5	1952	-4.32564	-0.44316	4.329255	7.133764	12.10834	17.06618
6	1953	-3.1646	-0.75623	2.12897	8.089749	11.11607	18.33482
7	1954	-3.20357	0.378325	0.369382	4.433573	11.11754	16.75439
8	1955	-4.54524	-4.14993	-0.60776	6.343187	11.74301	15.51771
9	1956	-5.51674	-1.28329	3.637789	9.392274	13.00579	19.12136
10	1957	-4.61113	-5.61714	2.178316	6.621301	11.87935	16.52139
11	1958	-6.3783	-3.79945	0.319811	6.119252	9.363994	16.12909

Sheet: sample_temp_monthly

No response ...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

I am determined to get to grips with dyanmic arrays... The OP may/may not have gone away happy... but I decided to fiddle with this one this morning because... well, just because...

Finally!!

One formula in one cell:

=LET(a,A4:A1803,b,B4:B1803,y,LEFT(a,4),u,UNIQUE(y),rc,COUNTA(u),cc,ROWS(a)/rc,tcc,cc+1,d,INDEX(b,SEQUENCE(rc,cc)),f,CHOOSE(1+INT(SEQUENCE(,tcc)/tcc),d,u),INDEX(f,SEQUENCE(rc),tcc-MOD(tcc+1-MOD(SEQUENCE(,tcc),tcc),tcc)))

The OP didn't have the courtesy to say whether or not (s)he was happy. Having looked at post #2, (s)he just disappeared. Not even so much as a quick, "Thanks". But don't worry, Glenn - I'm interesetd in your fromula and others will be, too.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

What have you tried?

In F2:

=UNIQUE(A2:A19)

IN G1 copied across to R1:

=COLUMNS($G1:G1)

In G2 copied across and down:

=SUMIFS($C$2:$C$19,$A$2:$A$19,$F2,$B$2:$B$19,G$1)

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.