Log In
Register
Home
March 15, 2021
Offline
Good afternoon,
I have a table that gives me the sales of 1 product and promotion for that product, by seller and name of the customers who bought either option or both.
My question is how do I know which customers bought the products without repeat customers,
What formula can I use without repeating the data?
Trusted Members
December 20, 2019
Offline
Please provide example workbook so we can see the data layout
March 15, 2021
Offline
File attached
KR
September 9, 2020
Offline
Hi Pedro,
The file is not attached. When you click on Attachments, you can select your file via Add Files and then finalise the procedure by clicking on Start Upload.
BR,
Lionel
March 15, 2021
Offline
Now with the file attached,
Thanks for the help
VIP
Trusted Members
June 25, 2016
Offline
Hi Pedro
I don't know which columns you are referring to but I guess it is columns A, C (maybe F) and L.
I would suggest using a Pivot Table as it is quick and easy to use.
Refer attached.
If it is not what you wanted, then let us have a sample of your expected results.
Sunny
(Excel 2019)
March 15, 2021
Offline
Many thanks for the reply,
My problem is that the pivot table counts the total customers with 1 reference or both and the same customer will be repeated if you buy the 2 refs. And what I need to know is the maximum customer reach for the product (be it normal or promo)
For example,
Customer A - bought the 2 references (normal + promo)
Customer B - bought 1 reference (normal)
Customer A will be counted 2 times in the pivot table
Sorry if I don't make myself understood!
September 9, 2020
Offline
Hi, Pedro,
I'm not sure I've grasped all the finer points of the reasoning. I suggest this solution based on a pivot table. In sheet_2, you have three results:
- A3: the number of distinct orders by Delegado and by Farmacia. You will therefore have one result even if there were several orders.
- E3: the number of orders per Delegado.
- H3: the number of orders by Farmacia.
BR,
Lionel
March 15, 2021
Offline
Thanks again for your attention
To know the number of customers with product X (since product x has another that is an "xx" promo, but for me, it is indifferent, as it counts as 1 product), it would have to be with formula because with the pivot table you don't do that interpretation and you will always count individually, correct?
Column P has a manual calculation, counting the customer with "1" and repeating with "0", I can identify 482 customers with the product x and/or its promo or both. I don't know if I explain myself correctly and if what I intend makes sense to you.
As I have to reproduce sales reports on a regular basis, this type of analysis is important to know the numerical distribution
VIP
Trusted Members
June 25, 2016
Offline
Hi Pedro
It looks like you wanted to count the unique address (column H) since you considered them to be different customers (even if they are the same pharmacy).
You can try this formula
=SUMPRODUCT(1/COUNTIF(H4:H787,H4:H787))
It will give you 475.
If you want to have values like in column P, then you will have to sort your data by column C and then H.
You can then use a formula like =IF(H4=H3,0,1) in column Q and then copy downwards. The total will also be 475.
Hope this is what you wanted.
Sunny
1 Guest(s)
