transposing by worksheet function

[Rey]

Gross Rev ReturnsAct Gross Rev ReturnsAct Gross Rev ReturnsAct
Period 01 Period 01 Period 02 Period 02 Period 03 Period 03
2,495,676 2,661,807 8,804,432 3,699,560 13,473,648 6,413,720


I wish to set up the data above to appear as below by using a worksheet function
instead of copying and transposing period by period.

Period 01 Period 02 Period 03
Gross Rev 2,495,676 8,804,432 13,473,648
ReturnsAct 2,661,807 3,699,560 6,413,720

Any help will be appreciated.

 

[Alok Joshi]

Assuming that your data is lying in range A1:F3
You can enter the column hedings in B5 to D5 and
row headings in A6 and A7
Then the formula to be entered in cell B6 will be as follows
=SUM(($A$1:$F$1=$A6)*($A$2:$F$2=B$5)*$A$3:$F$3)
entered as an array formula (with Ctrl-Shift-Enter)

Then just copy and paste to other cells.

Alok

 

[Ashish Mathur]

Assuming your column headings (in the original form) are in range B11:B13 (revised form) and row headings (in the original form) are in range C10:E10 (revised form). In C11, enter the following formula

INDEX($B$5:$E$8,MATCH(C$10,$B$5:$B$8,0),MATCH($B11,$B$5:$E$5,0))

This is not an array formula. Now copy this in the desired range.

I have assumed that the original data is in range B5:E8

Regards,

Ashish Mathur