1

I have a set of 110 numbers in Excel. I want to split this into 11 columns of 10 rows each.

So, for column:

324
292
300
120
276
320
984
136
300
280
69
289
266
292
266
494
315
118
295
289

I want the output to be:

324 69
292 289
300 266
120 292
276 266
320 494
984 315
136 118
300 295
280 289

What I've tried so far

  1. Assigned =A11 in B1, =A21 in C1, =A31 in D1 and then used the autofill. However, this gives the formula =D11 in E1 and hence doesn't work.

  2. Using Text to Columns wizard. This only provides for splitting of data for delimiter and there is now way of splitting based on number of rows.

The most similar question I've found is here where the goal is the opposite of mine (combining multiple columns into a single one).

This and this solve a similar problem but they copy the data row wise.

This question attempts the same problem in R.

I know this can be solved using a library like pandas, but I think there should be an easy way of doing it in Excel too.

5
  • How are your VBA skills? You could start at the top of the column and run a macro to read the cell contents into an array (use ActiveCell.Offset(1, 0).Select to read the next cell). Then write another macro to write the contents back, using nested loops to offset by 1,0 - and then by -10,1 every tenth loop. – Charles Burge Feb 19 '19 at 2:58
  • @Lee I have referenced another solution using R in my question. I know we can easily script to handle this situation. I am looking for something that works out of the box with Excel. – BenKenobi007 Feb 19 '19 at 15:39
  • @CharlesBurge I am still not very experienced with excel. Given that I've coded with c# before, how difficult is VBA ? – BenKenobi007 Feb 19 '19 at 15:41
  • VBA is very different from C#, but I think most people would consider VBA to be considerably easier. – Charles Burge Feb 19 '19 at 16:32
2

There are several ways of doing this. A previous answer by p.phidot used OFFSET. The OFFSET functions works, but is classified as a volatile functions. This is not the end of the world, it just means it recalculates every time something on the spreadsheet changes. Regular formulas only recalculate when something that affects them changes. As a non volatile alternative, I suggest the use of the INDEX function below. Copy it down 10 rows then copy to the right as far as you need to go.

=INDEX($A:$A,(COLUMN(A1)-1)*10+ROW(A1))
2

Assuming the 324 is located in A1, put this in B1 :

=OFFSET($A$1,(COLUMN()-1)*10+ROW()-1,0)

and drag it downward & right-wards.

idea : use the destination row and column to feed the offset from 1st value.

3
  • shouldn't it be =OFFSET($A$1,(COLUMN()- 2 )*10+ROW()-1,0,1,1)? – Forward Ed Feb 19 '19 at 4:14
  • Sure. In my case, I reuse the original list for the 1st column of the data and yours "re-produce" it as a separate column. [ An acceptable good answer too. ] /(^_^) – p._phidot_ Feb 19 '19 at 7:32
  • I missed that tidbit. I actually saw it but did not understand at the time. Good job! – Forward Ed Feb 19 '19 at 11:55
1

Your problem can be solved by using few Helper values:

enter image description here

How it works:

  • I'm assuming that your data are in Range A2:A41.
  • Write 0 in D1, 10 in E1 & 20 in F1.

N.B.

  • D1, E1 & F1 are Helper Cells.

  • 30 & 40 should value in G1 & H1, if you need further Rows to Copy.

  • Write this Formula in D2 & Fill Down then Right.

    =IF(ROW(D2)>11,"",INDEX($A:$A,COLUMN($A$1)+ROW($A10)+D$1))

  • Adjust cell references in the Formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.