+ Reply to Thread
Results 1 to 11 of 11

Convert rows with repeating data to separate rows

  1. 08-05-2009, 01:32 PM #1
    lancemonotone
    lancemonotone is offline
    Registered User
    Join Date
    08-05-2009
    Location
    North Adams, MA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Convert rows with repeating data to separate rows

    Please see the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.

    I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.

    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-05-2009, 02:35 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help on converting rows with repeating data to separate rows

    Try this on a copy of your data:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 08-05-2009, 05:18 PM #3
    lancemonotone
    lancemonotone is offline
    Registered User
    Join Date
    08-05-2009
    Location
    North Adams, MA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Help on converting rows with repeating data to separate rows

    Beautiful! Thank you very much!
    Reply With QuoteRegister To Reply
  4. 10-13-2009, 11:10 AM #4
    lancemonotone
    lancemonotone is offline
    Registered User
    Join Date
    08-05-2009
    Location
    North Adams, MA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Help on converting rows with repeating data to separate rows

    Actually, there is a problem when I try to convert too many rows. Please see the attached workbook. The last set of Quantity/Price pairs seems to get cut from the list for each ID. For example, ID 10 has a Q/P pair of 300/633 but this doesn't appear in the list.

    I think it happens during the For loop because I commented out the other lines your code. I just couldn't figure out what it was doing in order to fix it.

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 10-13-2009, 12:42 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help on converting rows with repeating data to separate rows

    This line of code needed tweaking:
    Please Login or Register  to view this content.
    Also, you should store regular macros in regular modules, not the sheet module. I inserted a normal module and moved the macro to the right spot. It should be good to go now.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-13-2009, 01:02 PM #6
    lancemonotone
    lancemonotone is offline
    Registered User
    Join Date
    08-05-2009
    Location
    North Adams, MA
    MS-Off Ver
    Excel 2007
    Posts
    10

    [SOLVED] Help on converting rows with repeating data to separate rows

    That did it. Thanks for the help and the tip. I'm a PHP guy...I know (next to) NOTHING about Excel. I appreciate your time.
    Last edited by lancemonotone; 10-14-2009 at 04:32 PM.
    Reply With QuoteRegister To Reply
  7. 10-13-2009, 01:12 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help on converting rows with repeating data to separate rows

    Glad to help.

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)
    Reply With QuoteRegister To Reply
  8. 10-14-2009, 04:55 PM #8
    lancemonotone
    lancemonotone is offline
    Registered User
    Join Date
    08-05-2009
    Location
    North Adams, MA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Help on converting rows with repeating data to separate rows

    Hi JB,

    Sorry, I tried to mark the thread solved but I could only edit my most recent post and I didn't see a Prefix choice.

    I actually have one more question if you wouldn't mind. How would I modify the code in order to insert 1 or more columns between ID and the Quantity/Price pairs so that each ID row is followed by those extra columns with one Q/P pair? (I'm trying to work out how to import a product spreadsheet into a database as a csv file but I'm not sure which fields I actually need to include...the documentation isn't very good.)

    For instance: 
    Please Login or Register  to view this content.
    Looks like:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 10-14-2009, 06:11 PM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert rows with repeating data to separate rows

    Try this:
    Please Login or Register  to view this content.

    =======
    Now that you've removed the SOLVED flag and more than two days have passed, the best you can do is EDIT the original post and add [SOLVED] to the start of the title manually.
    Reply With QuoteRegister To Reply
  10. 10-14-2009, 07:12 PM #10
    lancemonotone
    lancemonotone is offline
    Registered User
    Join Date
    08-05-2009
    Location
    North Adams, MA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Convert rows with repeating data to separate rows

    Thanks, I'll try that code. Sorry, but I don't have a button to edit my original post or the title. I can only edit my most recent posts.
    Reply With QuoteRegister To Reply
  11. 02-25-2010, 07:10 PM #11
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert rows with repeating data to separate rows

    Check the Thread Tools menu at the top of this thread.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1