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.

Improve this question
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
    Commented Feb 19, 2019 at 2:58
  • stackoverflow.com/questions/6936120/…
    – Lee
    Commented Feb 19, 2019 at 9:03
  • @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
    Commented Feb 19, 2019 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
    Commented Feb 19, 2019 at 15:41
  • VBA is very different from C#, but I think most people would consider VBA to be considerably easier.
    – Charles Burge
    Commented Feb 19, 2019 at 16:32

3 Answers 3

Reset to default
2

There are several ways of doing this. A previous answer by p.phidot used OFFSET. The OFFSET function works, but is classified as a volatile function. This is not the end of the world, it just means it recalculates every time something in 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))
Improve this answer
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.

Improve this answer
3
  • shouldn't it be =OFFSET($A$1,(COLUMN()- 2 )*10+ROW()-1,0,1,1)?
    – Forward Ed
    Commented Feb 19, 2019 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_
    Commented Feb 19, 2019 at 7:32
  • I missed that tidbit. I actually saw it but did not understand at the time. Good job!
    – Forward Ed
    Commented Feb 19, 2019 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.

Improve this answer

You must log in to answer this question.

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