Excel

Question

Hi, I am trying to figure out how to take a single row of numbers in excel (591 cells, 1 number per cell) and split it into multiple rows containing three cells of numbers per row (197 rows of three columns per row). For example, if I have 9 cells in row 1 as: 1 2 3 4 5 6 7 8 9, I need to convert it to

1 2 3

4 5 6

7 8 9

Any help would be appreciated. Thanks!

Answer 1

Hi, I am trying to figure out how to take a single row of numbers in excel (591 cells, 1 number per cell) and split it into multiple rows containing three cells of numbers per row (197 rows of three columns per row). For example, if I have 9 cells in row 1 as: 1 2 3 4 5 6 7 8 9, I need to convert it to

1 2 3

4 5 6

7 8 9

With 1,2,3 ... 589,590,591 in Row 1 - "A1:VS1"

use the following VBA code...

'---

Sub ThreeAcross()
Dim Rng As Excel.Range
Dim Ndx As Long, RowNdx As Long

RowNdx = 2
Set Rng = Range("A1:VS1").Cells ' 1 to 591
Application.ScreenUpdating = False
For Ndx = 4 To 591 Step 3
Rng(1, Ndx).Resize(1, 3).Cut Destination:=Cells(RowNdx, 1)
RowNdx = RowNdx + 1
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

'---

Result looks like...

it continues down to row 197...

'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

Answer 2

Hi Tyty92,

I'm Sneha and I'd be happy to help you out with your question.

Yes, you can do it by entering formula =SEQUENCE(3,COUNT(A2:I2)/3) in cell A6.

If you get an #CALC! error

This error occurs when Excel's calculation engine encounters an unspecified calculation error with an array. To resolve it, try rewriting your formula. If you have a nested formula, you can try using the Evaluate Formula tool to identify where the #CALC! error is occurring in your formula.

For better detailed guidance and information for formula error, refer to following documentation :- https://support.microsoft.com/en-us/office/evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7

I hope this information helps. If you have any questions, please let me know and be glad to assist you further.

Best Regards,
Sneha

Answer 3

You are welcome. Neither do I. Try once again. Read up on the CALC error on Google.

Answer 4

Thanks for the reply! Unfortunately I am getting a #CALC! error when trying this. Not sure what would be causing that.

Answer 5

Hi,

If you use Microsoft 365, then enter this formula in cell A6

=SEQUENCE(3,COUNT(A2:I2)/3)

Hope this helps.