Dynamic list within list from matrix

The problem I have is that I want to create a list of customers and within that list the products that said customer purchases, then add a total line at the bottom of each list. For example:
Customer 1
Product A
Product B
Product C
Customer 1 Total
Customer 2
Product B
Product D
Product E
Customer 2 Total
Customer 3
Product B
Product C
Customer 3 Total

I have the list of which customer take which products in a matrix, with customers across the top and products available on the left, an "x" in the matrix determines which customers take which products. For info both customers and products have unique identifier codes.

I have used index and small before to pull out a list based on a value, but that was in a single column. Any help would be greatly appreciated. If you need any further help please do not hesitate to ask.

It would help if you attached a sample Excel workbook, so we can see exactly how your data is laid out and can play about with different approaches before posting it back to you.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon (Attachments button) does not work on this forum, so don't try to use it.

Hope this helps.

Pete

I've re-created a basic copy of the file as I couldn't use the actual file due to confidentiality. The principles still apply though, the matrix is laid out like that, just with more customers and products. The other tab (forecast) I have laid out as it would appear, with monthly columns for me to manually enter a forecast, with the customers and products listed on the left. At the moment this is manually updated, with the layout repeated across other tabs for various purposes. The idea being that the matrix could be rolled out across the other tabs and it be unified based on the matrix. I hope that is enough info and the sheet is useful enough?

Thanks again

And where does date (in Forecast) originate as it not shown in product matrix?

The date in the forecast is added at that point, it is not required until then. It is so that a forecast can be added for the products that are listed.

I suspect you will need VBA to do this for a large number of customers and products.

@Ali: I asked the same question - what is required is to (only) generate column A from the "Matrix".

VBA

Please Login or Register  to view this content.

Click "RUN" on "Product Matrix"

Thanks John, that is a very elegant solution, does exactly what I want. I've applied it to my larger file (with tweaks for column and row numbers) and works perfectly. Much obliged.