combined lists lookup

philiplaw

New Member
Joined
Nov 4, 2016
Messages
4
I am converting a product planning sheet from a very manual sheet to a much more automated system.

I have a customer/product matrix setup a little like below, where a '1' indicates if the customer takes the product or not:
Customer 1Customer 2Customer 3Customer 4
Product A111
Product B1111
Product C11
Product D111

<tbody>
</tbody>

I then have on another sheet the customers listed with the products taken across weeks:
week 1week 2week 3week 4
Customer 1
Product A
Product B
Product D
Customer 1 Total
Customer 2
Product A
Product B
Customer 2 Total

<tbody>
</tbody>

At the moment this second table is amended manually, so if a new product is added to a customer new rows have to be added.

*the challenge* What I would like is a way for this sheet to have a formula to add the customers, lookup what products they take based on the matrix, add a line for the total when it has reached the end of the taken products, then move on to the next customer and so on and so forth.

I hope that made sense? If anyone can shed some light on this I would really appreciate it, any other info you need just ask.

Thanks
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top