Consolidate multiple rows of information into one row [Closed]

Report
-
Hello,

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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!