+ Reply to Thread
Results 1 to 4 of 4

Move Duplicate Vertical to Horizontal

  1. #1
    Registered User
    Join Date
    07-24-2007
    Posts
    6

    Move Duplicate Vertical to Horizontal

    Hello, I would like help moving a portion of vertical data to be horizontal.

    I have a report that is in Excel that is 12 columns wide and a few thousand rows long.
    The data is sales data that if the sale record is completed with no issues then I get one row but if the order has any discounts applied against it then it repeats the record for every discount.

    I need the data to show as a single row and move the duplicates to be within the single row.

    When you look at the data you will notice Sales Number 23630 is the perfect scenario with no adjustments against revenue but orders 13060 and 13150 both have discounts.

    I am thinking that I can use the Sales Number and when it repeats grab the data and put it in the columns needed. Columns are fixed with 12 columns on the report every week. I am sure for those good at VBA this is easy but I need guidance.

    Please see condensed sample data attached. I did condense the data down to only 4 columns but if you can give me a solution that works for the 4 columns I think I can get it to work for 12 columns.

    Thanks and if this is too vague please let me know and I'll revise.
    Attached Files Attached Files
    Last edited by sampson20; 12-27-2010 at 05:26 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,065

    Re: Move Duplicate Vertical to Horizontal

    The macro below starts at the bottom of the sheet working its way up. If the bottom sale number is the same as the previous then the description and amount are appended to the previous row until the end of the row is reached, then the old lower row is deleted.

    Try it on a back up copy of your workbook, in case your actual column layout is not the same as the sample.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-24-2007
    Posts
    6

    Re: Move Duplicate Vertical to Horizontal

    Code provided above by protonLeah worked great. I had to define one of the variables and I get an error when I get to the end of the file (or top since it goes from bottom to top). But Variable was easily defined and I just ignored error.

    Comments in code made it very easy to follow.

    thanks so much...

  4. #4
    Registered User
    Join Date
    01-09-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Move Duplicate Vertical to Horizontal

    I am opening the sample & when running the macro the VB edititor is opening & cursor is pointing towards Ln24, Col 17

    Pop up "Compile Error: Variable Not defined.

    Please help. Unable to use the macro

    Thanks

+ 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