• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset Formula help

Hi All,

I have to transpose data from rows to columns. I have used offset formula to transpose data in column C to rows starting in column E (please see highlighted in blue). Can you help me with two issues:

1. How to change the formula so I drag it to E2-G2 (highlighted yellow) to get the data in column D7-D9
2. I have this fixed format to transpose the data. I want E3-G3 to pick up data from C24-C26 (highlighted in green). There is 14 rows in between (fixed number of rows between two sets of data)

70281
 

Attachments

  • Payment trans (1).xlsx
    11.8 KB · Views: 3
Would you be happy with a single array formula?
= INDEX(UnitDetails, row, column)
The names 'row' and 'column' are defined to be
= 17*QUOTIENT(k-1,2)+7 + {0,1,2}
= 3+MOD(k-1,2)
 

Attachments

  • Payment trans (PB).xlsx
    12.5 KB · Views: 5
Would you be happy with a single array formula?
= INDEX(UnitDetails, row, column)
The names 'row' and 'column' are defined to be
= 17*QUOTIENT(k-1,2)+7 + {0,1,2}
= 3+MOD(k-1,2)
Many thanks. I have a bigger range than I posted in the example. I need to figure out to tailor it for the formula to work.
 
I use array formulas for simplicity but it should be possible to turn the formula into single row formulas copied down.
MS365: replace k by @k, drag down
Traditional Excel: Ctrl/Enter to overwrite the CSE formula; select the top row and recommit with CSE; drag row down.
 
Back
Top