Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Consolidate multiple rows into one [Closed]

Report
-
I have a commission file that shows a Customer name in cell D2, customer address in J2 and then the commission details in a changing number of rows below that. Column B will contain the same number (customer id) for all commission details, Column H contains differing product types and column K will have the price.
Columns B-H are blank in between customers. So, I assume a "Do Until Blank" type macro would work here.
What I need to do is take each customer and consolidate everything into one row on a new sheet. Since each customer could have anywhere between 2 and 10 products, I need to run a macro to consolidate the info.

New Sheet:
Row 1 will contain headings with Name, Address and then Product descriptions (Product X, Product Y, etc.)
Rows 2-unknown should contain:
Column A = Customer Name
Column B = Customer Address
Column C = Price of Product X (i.e. if Column H contains "Product X" then input Column K)
Column D = Price of Product Y
Column E = Price of Product Z
etc. etc.
Column K = Total of all prices.

I also need error handling, so if the product is not found (i.e. I have products A-Z and Product 4 shows up) it will place that price into an extra field, maybe column J (would need a SumIF so J sums all "not found" products) so it is included in the total in column K.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!