1

I am trying to find a way of tranposing large datasets (the structure is shown below) in a certain way and wondering how to implement it in Excel using a formula as to avoid time-consuming and error prone copy-paste transpose!

I want to do the transformation of the data on sheet A on a separate sheet (B). I essentially have triplicate measurements multiple features of different groups. The number of groups can vary but the measurements will always be in triplicate. Thanks for any help you can give

enter image description here

1 Answer 1

1

You can play around a bit with the INDEX, COLUMN and ROW formulas and some arithmetic. INDEX will look up a cell's value for you, given a row and column number. For a simple transpose, you just need the COLUMN of the current cell as the row and the ROW of the current cell as the column. However, your operation is slightly more difficult than a simple transposition and therefore we need some arithmetic. With some trial and error I arrived at the formula below.

Provided that Sheet A contains the data in the top left corner (i.e. B2 contains the value 5), this formula on Sheet B will work:

=INDEX('Sheet A'!$1:$1048576, MOD(COLUMN()+1,3)+3*ROW()-4, 1+(COLUMN()+1)/3)

enter image description here

Column numbers start at 1, so the MOD(COLUMN()+1,3) gives 0 for the GroupnA-columns, 1 for B and 2 for C. The 3*ROW() part correspond to the three rows for each Groupn in Sheet A. The 1+(COLUMN()+1)/3 maps all Cat 1 columns in Sheet B to column 2 in Sheet A, Cat 2 to column 3, etc.

1
  • That worked perfect. Thanks for that, it is really useful and adaptable and saves a lot of copy and pasting. Apr 1, 2017 at 21:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .