+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    12-16-2006
    Posts
    26

    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. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    Dubai
    MS-Off Ver
    2013
    Posts
    5,742

    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
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    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. #4
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    559

    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. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2013:2016
    Posts
    3,684

    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
    Cheers!
    Deep Dave

    Please Click STAR to Add Reputation if my/someone's answer helped!

    www.NeedForExcel.com

  6. #6
    Registered User
    Join Date
    12-16-2006
    Posts
    26

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

    Thank you all for your help.

  7. #7
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,142

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

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1