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.

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.