# Transpose every 5 cells in column A to indidual rows in column B

1. ## Transpose every 5 cells in column A to indidual rows in column B

I have over 3,000 cells in column A which I need to transpose every 5 cells into column B.

Example:
a1
a2
a3
a4
a5
which needs to transpose to B1,C1,D1,E1,F1
a6
a7
a8
a9
a10
which needs to transpose to B2,C2.D2,E2,F2
etc.

Would appreciate any help.

2. ## Re: Transpose every 5 cells in column A to indidual rows in column B

In B1

=INDEX(\$A:\$A,ROW(A1)*5-5+COLUMN(A1))

Copy down and across

3. ## Re: Transpose every 5 cells in column A to indidual rows in column B

This was tricky, but a whole lot of fun to figure out:

Put this in B1 and the copy and paste out to F and down as far as needed

=INDEX(\$A\$1:\$A\$3000,COLUMN(A1)+((ROW(A1)-1)*5))

4. ## Re: Transpose every 5 cells in column A to indidual rows in column B

Hi meridklt,

Check out the linkl below - it will do exactly what you want under columns and rows number 17.

http://www.asap-utilities.com/downlo...-utilities.php

Regards

peterrc

5. ## Re: Transpose every 5 cells in column A to indidual rows in column B

Here!

in B1 - =IF(INDEX(\$A\$1:\$A\$1000,ROW(A1)*5-5+COLUMN(A1))=0,"",INDEX(\$A\$1:\$A\$1000,ROW(A1)*5-5+COLUMN(A1)))

Deep

6. ## Re: Transpose every 5 cells in column A to indidual rows in column B

Thank you all for your help.

7. ## Re: Transpose every 5 cells in column A to indidual rows in column B

There are currently 1 users browsing this thread. (0 members and 1 guests)