Compare series and display formatted series

Closed
Sham - Jun 20, 2009 at 02:31 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 21, 2009 at 12:24 AM
Hello,
I have a long series of data from 1950 to 2100 and their 12 months values as follows. I need to compare the vertical column and get the value horizontally for each year and 12 months.The required format would be like

1950 1951 1952..........................................2100
1
2
3
4
5
6
7
8
9
10
11
12

I used the command INDEX($B$3:$D$14,MATCH(C3,$C$3:$C$14,1),3) It can match only the months but not the year so I have to do it separately for every year taking only the monthly series for that year. I want a formula that matches the year first from whole series 1950-2100 and its month from 1 to 12 and display the value for that particular year in the above format. Any help would be appreciated. The data example is as follows. The same format continues up to 2100.

1950 1 0.114265742
1950 2 1.868213036
1950 3 0.371982709
1950 4 0.247900694
1950 5 0.063933763
1950 6 0.126441072
1950 7 0.31533796
1950 8 0.109362281
1950 9 1.190689917
1950 10 0.011078935
1950 11 0.62253943
1950 12 0.013913919
1951 1 0.829242
1951 2 0.726890942
1951 3 1.402155345
1951 4 0.090852742
1951 5 0.519283938
1951 6 0.438593793
1951 7 0.168011213
1951 8 1.178202133
1951 9 0.046328742
1951 10 0.04320839
1951 11 0.81522594
1951 12 0.670971346
1952 1 1.503163465
1952 2 0.554984511
1952 3 0.024513632
1952 4 0.261339648
1952 5 0.066605057
1952 6 0.153780878
1952 7 0.514289809
1952 8 0.336424178
1952 9 1.097034539
1952 10 0.281516791
1952 11 0.139816638
1952 12 0.102879747....................
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 21, 2009 at 12:24 AM
USE PIVOT TABLE.
You can read about pivot table

open this webhpage

https://www.contextures.com/tiptech.html
go down to the item pivot table
the result will be something like this

MONTH 1950 1951
1 0.114265742 0.829242
2 1.868213036 0.726890942
3 0.371982709 1.402155345
4 0.247900694 0.090852742
5 0.063933763 0.519283938
6 0.126441072 0.438593793
7 0.31533796 0.168011213
8 0.109362281 1.178202133
9 1.190689917 0.046328742
10 0.011078935 0.04320839
11 0.62253943 0.81522594
12 0.013913919 0.670971346
0