+ Reply to Thread
Results 1 to 14 of 14

Macro to transpose multiple rows into one row

  1. #1
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    7

    Macro to transpose multiple rows into one row

    Hello all,

    I'm wondering if this is possible. Here is a sample of a large data set:
    Capture.PNG

    I want to transpose these multiple rows into one row per service, id, name, age, street address with multiple phone numbers like this:
    Capture1.PNG

    Thanks guys!
    Attached Files Attached Files
    Last edited by Luden; 09-13-2019 at 09:16 PM.

  2. #2
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Macro to transpose multiple rows into one row

    Is your first output data line correct? You included a Sprint call (third data line of original data) in with the surrounding Verizon calls.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,506

    Re: Macro to transpose multiple rows into one row

    Where a name like say John has two or perhaps more Service references is it always the first one that you want to record?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    7

    Re: Macro to transpose multiple rows into one row

    Oh, that was my mistake. The service column should not be included in this excel file. Just id, name, age, street address and phone numbers. Sorry about that.

  5. #5
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Macro to transpose multiple rows into one row

    Is the ID sufficient to identify identical accounts or could the same ID have different names, ages and/or street addresses (which, if so, would require individual output data lines I guess)?

  6. #6
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    7

    Re: Macro to transpose multiple rows into one row

    Quote Originally Posted by Rick Rothstein View Post
    Is the ID sufficient to identify identical accounts or could the same ID have different names, ages and/or street addresses (which, if so, would require individual output data lines I guess)?
    The same ID could have different names, ages and/or street addresses.

  7. #7
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Macro to transpose multiple rows into one row

    Quote Originally Posted by Luden View Post
    The same ID could have different names, ages and/or street addresses.
    So then, would they require individual data output lines for each that differs in some way?

    Also, will the data be sorted so that any differences are all one under the other?

    It would help if your example showed the kind of differences we are now talking about and the output you expect for them rather than giving us a idealized example file hoping we will figure out how to handle what you have not shown or told us about.
    Last edited by Rick Rothstein; 09-13-2019 at 06:54 PM.

  8. #8
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    7

    Re: Macro to transpose multiple rows into one row

    Quote Originally Posted by Rick Rothstein View Post
    So then, would they require individual data output lines for each that differs in some way?

    Also, will the data be sorted so that any differences are all one under the other?

    It would help if your example showed the kind of differences we are now talking about and the output you expect for them rather than giving us a idealized example file hoping we will figure out how to handle what you have not shown or told us about.
    Sorry about the inconvenience. Answers for both questions are YES. Here are new images:
    Attachment 641371

    Attachment 641372

    Not sure if that's clear to you. This is my first time asking questions lol

  9. #9
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Macro to transpose multiple rows into one row

    Something seems to be wrong with your links... I get an "invalid attachment" message when I click either one. Did you use the same method to attach them as you did in your original message? I am guessing not as they look different than your first attachment's link (no surrounding "Attached Files" box).

  10. #10
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    7

    Re: Macro to transpose multiple rows into one row

    Quote Originally Posted by Rick Rothstein View Post
    Something seems to be wrong with your links... I get an "invalid attachment" message when I click either one. Did you use the same method to attach them as you did in your original message? I am guessing not as they look different than your first attachment's link (no surrounding "Attached Files" box).
    Oops. Mine works fine. I don't know why. Anyway, this is the result table. Phone numbers can be many. If the names are different, even though they have the same ID, they should still be in two rows.
    ID Name Age Street Address Phone Number
    110 Jack Age 50 1402 Kendall St, Covington, KY, 41011 (514) 580-1873 (635) 523-5494 (487) 890-0766 (673) 552-8840 (937) 779-3649
    110 Mary Age 52 1402 Kendall St, Covington, KY, 41011 (297) 653-9111 (773) 948-1349 (945) 965-7600 (226) 970-0616 (948) 560-6552
    112 John Age 45 16 Maplewood Ln, Natchez, MS, 39120 (575) 236-9508 (978) 507-9294 (886) 527-7310 (899) 465-3198 (765) 777-8377

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,506

    Re: Macro to transpose multiple rows into one row

    Attached is what I'd originally put together based in your original data.

    Before I modify it to account for the dropping of the Service Ref would you upload a new workbook as you did erlier which clearly shows the original data and the results.

    Currently the macro is

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    7

    Re: Macro to transpose multiple rows into one row

    Yes. I updated the excel in my original post. Thank you!

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,651

    Re: Macro to transpose multiple rows into one row

    Try
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    244

    Re: Macro to transpose multiple rows into one row

    Based on your updated file at the link in Message #1, here is the macro that I came up with (it assumes your data has less than about 65500 or so rows in it)...
    Please Login or Register  to view this content.
    Note: If there is any chance this code will be run when no multiple phone numbers exist for any account, then some error trapping code would have to be added to the above code.
    Last edited by Rick Rothstein; 09-13-2019 at 11:59 PM.

+ 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