Matching customer name to list of SKUs they bought

Grisego

New Member
Joined
Jul 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

I need help with finding the appropriate formula:

I have:
  1. A list of customers
  2. A list of promotional SKUs
  3. A sales report with the SKU and the name of the customer that purchased it.
Customer Data.xlsx
GHIJKLMNOP
2CustomerSKUsSKUQuantityRevenueCustomer
3John Smithw1234w123415John Smith
4Alex Scottr1567r1567525John Smith
5Carlos Lopezt5789t5789210James Doe
6Tim Welchw1234w1234315Tim Welch
7James Doer1567r156715Carlos Lopez
8t5789t5789210Carlos Lopez
9t5790t57901050Alex Scott
10t5791t57911260Alex Scott
11w1234
12w1235
13w1236
14w1237
15w1238
16w1239
17
Consolidated
Cell Formulas
RangeFormula
O3:O10O3=N3*5


I need to mark, in the list of customers, the number of those SKUs that each customer purchased if they did.

Does anyone have an idea of what I might use?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,721
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
GHIJKLMNOP
1
2CustomerSKUsSKUQuantityRevenueCustomer
3John Smith2w1234w123415John Smith
4Alex Scott2r1567r1567525John Smith
5Carlos Lopez2t5789t5789210James Doe
6Tim Welch1w1234w1234315Tim Welch
7James Doe1r1567r156715Carlos Lopez
8t5789t5789210Carlos Lopez
9t5790t57901050Alex Scott
10t5791t57911260Alex Scott
11w1234
12w1235
13w1236
14w1237
15w1238
16w1239
17
Main
Cell Formulas
RangeFormula
H3:H7H3=ROWS(UNIQUE(FILTER($M$3:$M$10,$P$3:$P$10=G3)))
O3:O10O3=N3*5
 

Forum statistics

Threads
1,176,168
Messages
5,901,734
Members
434,914
Latest member
mabek

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
Top