Consolidate multiple rows of information into one row

Accountant33 - Feb 9, 2017 at 04:50 PM

I am hoping someone will be able to help with a VBA Macro or formulas or something. I'm generally at my wits end trying to figure this out.

What I have is a commission report 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, below the customer name, 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. After one or more "blanks" in Column B, the next customer name will be in Column D and then the commission details start over. Thus, copying to the new sheet will need to increase the row, by one.
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 would 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.

Any help would be much appreciated. I do know some VBA code, so I can edit as needed, but a working file to start with would be amazing. Thank you to whomever can solve my dilemma.