+ 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
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    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,465

    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
    586

    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 2016:2019
    Posts
    3,870

    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

  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
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    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)

Similar Threads

  1. [SOLVED] Transpose every 4 rows in one column to 4 column in one row
    By ttkt in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-07-2013, 01:41 PM
  2. Replies: 9
    Last Post: 07-22-2012, 12:03 PM
  3. [SOLVED] Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.
    By Jahoker in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-15-2012, 10:37 AM
  4. transpose Rows to column
    By gcol in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-01-2011, 05:12 PM
  5. Transpose Column to Rows
    By hardeep.kanwar in forum Excel General
    Replies: 7
    Last Post: 03-29-2010, 05:57 AM

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