Horizontal data into vertical (repeatedly)

[zainmerchant]

I have this data where you can see that against a S.No, there are amounts for 12 months. I want this data to be sorted in a way where against each S. No, there are 12 months (each) shown in vertical format with its respective figures. The final data should look like the data in the second tab but for each S. No.

Unfortunately, I'm unable to find something like this even on Google and I don't know VBA Coding so I can't use that option either. There's a total of 192 serial numbers so doing this manually is also not an option.

If anyone could tell me how to do it, that will be helpful.

[Sam Capricci]

assuming what you have in the "final sample" tab are the cells filled in like cells A2 and B2, you can put this in C2 and drag down...
=SUMPRODUCT((Combine!$A$2:$A$193='Final Sample'!A2)*(Combine!$B$1:$M$1='Final Sample'!B2),Combine!$B$2:$M$193)

[Bo_Ry]

Please try at
A2
=INT((ROWS(A$2:A2)-1)/12)+1

B2
=INDEX(Combine!$B$1:$M$1,MOD(ROWS(B$2:B2)-1,12)+1)

C2
=INDEX(Combine!$B$2:$M$193,A2,MOD(ROWS(B$2:B2)-1,12)+1)

or Power Query
select combine sheet > Ribbon > Data > New Query > From table
Advanced editor > paste below code

Please Login or Register  to view this content.

Open Power Query/Get and Transform. Click on New Query.

[zainmerchant]

Thank you so muchhhhh! The function part above worked! Thanks a lot man!