How to transpose data in excel from quarterly to monthly format-CodePudding

I have the following data in Excel sheet1.

I would like to transpose this data into columns in another sheet. I have used the following formula to do so

=TRANSPOSE(Sheet1!C3:J3)

Which gives the following output

As you can see, the first sheet is frequenced quarterly and the second sheet is frequenced monthly. When I transpose the data into the monthly sheet, is there a way to tweak this formula to have the same number appear in all three months of a given quarter? (so for 0.83 to appear in the first three months, 2.23 in the next three months and so on?)

CodePudding user response：

You can benefit from INDEX and MATCH (Match will work only if headers in transposed data is exactly the same than source data):

Formula in B11:

=INDEX($C$3:$F$4;MATCH(B$10;$B$3:$B$4;0);ROUNDUP(MONTH($A11)/3;0))

Drag to left and down