 SOLVED

# Excel I need to separate the data six by six.

Hi guys i have a data homework to do.I am trying to do that at excel.

So the situation is i have got 2000 data in one row.

I need to separate this data six by six.

For example the data i got the data i want to get Thank you.

8 Replies

# Re: Excel I need to separate the data six by six.

Here is the answer for you.
=IFERROR(INDEX(INDIRECT("A2:A"&COUNTA(\$A:\$A)),ROW(A1)*6-5+COLUMN(A1)-1),"")
best response confirmed by SomeoneTrying (New Contributor)
Solution

# Re: Excel I need to separate the data six by six.

In addition to the formula @Starrysky 1988 sent you, here is a link with other approaches, such as with VBA.

How to move every other row to column in Excel

Standard Disclaimer: These are links to non-Microsoft websites.

NikolinoDE

I know I don't know anything (Socrates)

@SomeoneTrying

# Re: Excel I need to separate the data six by six.

If you are office365 users, you may use dynamic array formula as below.
=Makearray(Ceiling(Quotient(Counta(\$A2:\$A3000),6),1),6,Lambda(r,c,Index(\$A2:\$A3000,r*6-5+c-1)))

# Re: Excel I need to separate the data six by six.

An alternative could be to manually enter formulas =A2, =A3, =A4, =A5, =A6, =A7 in cells B2, C3, D4, E5, F6 and G7.

Then select range B2:G7 and fill down to row 28 in the attached example.

Then copy range B2:G28 and paste only values.

Now select only blanks with ctrl+G and delete blanks with right mouseclick and delete cells.

# Re: Excel I need to separate the data six by six.

Hope WRAPROWS function (microsoft.com) will be available soon for everyone, not only insiders.

# Re: Excel I need to separate the data six by six.

Thank you for your answer I've been dealing with this all day finally i figured it out.

# Re: Excel I need to separate the data six by six.

thank you so much

# Re: Excel I need to separate the data six by six.

Data in A

=INDEX(\$A\$1:\$A\$18,SEQUENCE(COUNTA(A:A)/6,6,1,1),1)