+ Reply to Thread
Results 1 to 6 of 6

Transpose data to new worksheet with for loop

  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Transpose data to new worksheet with for loop

    Hello,
    I am new to creating Macros and need some major help. I have an Excel 2010 sheet with a large amount of data where all of the data is in a single column (A). I am needing to format it so that each data set is in its own row (and in different columns, of course). I assume I need to use a for loop, because not all data sets will have the same number of columns, using "Name:" as the indicator to start a new row.

    Thank you in advance for any and all help with this!
    Attached Files Attached Files
    Last edited by SUKAMC; 06-07-2012 at 02:50 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Transpose data to new worksheet with for loop

    SUKAMC,

    Welcome to the forum!
    Assuming the data is like your sample and starts in cell A1 and the first cell contains "Name: xxxx", then this should work for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Transpose data to new worksheet with for loop

    Tigeravatar,
    That is awesome thank you! There is one problem with it, but that is my fault because I failed to point it out. Sorry.

    If you look at A11 and A12 from the example data I attached, A12, if possible, should be concatenated with the data in A11. The data that needs to be concatenated, like in cell A12, will never have a colon within the cell.

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Transpose data to new worksheet with for loop

    Updated code. It assumes any cell without a colon should be concatenated to the cell(s) above it:
    Please Login or Register  to view this content.
    Last edited by tigeravatar; 06-07-2012 at 03:53 PM. Reason: Updated code to turn off calculation, events, and screenupdating; changed the redim method for arrdata

  5. #5
    Registered User
    Join Date
    06-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Transpose data to new worksheet with for loop

    Perfect!
    Thank you so much.

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Transpose data to new worksheet with for loop

    You're very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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