transposing by worksheet function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Back
Top