Group number of purchased SKUs by counting customer

blader1989 · Mar 25, 2024

Hi all,

I'm building a report that will group the number of purchased SKUs by counting customer with total sales > 0, I have tried pivot and many ways but I could not get the result as my expectation in attached file so I hope you can help me.

Thank you for your reading time, your help will be most appreciated!!!

Here is my table example

Product Name	Customer Code	Billing Date	Qty	Year	Month	Total Sales
Product 45	121001076	08/02/2023	5	2023	2	864285
Product 46	121001076	04/01/2023	10	2023	1	2033330
Product 46	121001076	01/02/2023	20	2023	2	4066660
Product 51	121001258	20/02/2023	60	2023	2	3914280
Product 51	121001258	24/02/2023	30	2023	2	1957140
Product 51	121001792	04/01/2023	131	2023	1	8546178
Product 51	121001792	09/01/2023	69	2023	1	4501422
Product 51	121001943	04/01/2023	-50	2023	1	-3261900
Product 51	121001943	23/02/2023	50	2023	2	3261900
Product 44	121002462	14/01/2023	10	2023	1	1000000
Product 44	121002462	28/02/2023	20	2023	2	2000000
Product 46	121002462	14/01/2023	10	2023	1	2033330
Product 46	121002462	28/02/2023	20	2023	2	4066660
Product 46	121002507	14/01/2023	20	2023	1	4066660
Product 51	121002507	16/02/2023	20	2023	2	1304760
Product 46	121002535	07/02/2023	4	2023	2	813332
Product 19	121002730	13/02/2023	10	2023	2	985710
Product 44	121002730	10/01/2023	20	2023	1	2000000
Product 44	121002730	27/01/2023	30	2023	1	3000000
Product 44	121002730	13/02/2023	30	2023	2	3000000
Product 44	121002730	23/02/2023	30	2023	2	3000000
Product 44	121002730	28/02/2023	20	2023	2	2000000
Product 46	121002730	10/01/2023	20	2023	1	4066660
Product 46	121002730	27/01/2023	30	2023	1	6099990
Product 46	121002730	31/01/2023	10	2023	1	2033330
Product 46	121002730	31/01/2023	10	2023	1	2033330
Product 46	121002730	13/02/2023	20	2023	2	4066660
Product 46	121002730	23/02/2023	20	2023	2	4066660
Product 46	121002730	28/02/2023	10	2023	2	2033330
Product 46	121002752	14/01/2023	10	2023	1	2033330
Product 44	121003038	05/01/2023	20	2023	1	2000000
Product 44	121003038	21/02/2023	20	2023	2	2000000
Product 44	121003038	24/02/2023	20	2023	2	2000000
Product 46	121003038	05/01/2023	25	2023	1	5083325
Product 46	121003038	27/01/2023	20	2023	1	4066660
Product 46	121003038	28/01/2023	10	2023	1	2033330
Product 46	121003038	21/02/2023	10	2023	2	2033330
Product 46	121003038	24/02/2023	20	2023	2	4066660
Product 44	121003326	16/01/2023	10	2023	1	1000000
Product 44	121003326	27/01/2023	30	2023	1	3000000
Product 51	121003349	17/02/2023	50	2023	2	3261900
Product 51	121003359	16/01/2023	41	2023	1	2674758
Product 44	121003423	16/01/2023	10	2023	1	1000000
Product 44	121003423	27/01/2023	20	2023	1	2000000
Product 46	121003423	16/01/2023	30	2023	1	6099990
Product 46	121003423	27/01/2023	30	2023	1	6099990
Product 44	121003739	09/01/2023	5	2023	1	500000
Product 44	121003739	15/02/2023	5	2023	2	500000
Product 46	121003739	09/01/2023	5	2023	1	1016665

dermie_72 · Mar 25, 2024

Not sure if this is what you're after:
I put your sample data into a spreadsheet A1:G50
I created a unique table of Customer codes via the unique function into I2

Excel Formula:

=UNIQUE(B2:B50)

Then copied the month's from the table to the heading, IE J1:K1 became 1 & 2
I then used a formula to count the number of sku's purchased by customer that month.

Excel Formula:

=COUNTIFS($B:$B,$I2,$F:$F,J$1,$D:$D,">0")

And encompassed the range you're wanting to detect.
In Q2 I added the Number of SKU's going from 1 down and used a formula in R2 going across the number of months (You might need to change the start column depending on the number of months in the previous table.

Excel Formula:

=COUNTIF(J$:J$,$Q2)

This returns the table that you requested. Hope that helps.

Peter_SSs · Mar 26, 2024

Thanks for the XL2BB data - makes it much easier.

You put the formula in J3 not J2 as suggested. Put it in J2 instead and remove all the formulas from J3 to J9.

Also, since you have a dynamic array formula in I2, change the J2 formula to this, then you do not have to specify the column I range.

Excel Formula:

=BYROW(I2#,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(A2:A16,B2:B16=r,NA()))),0)))

Peter_SSs · Mar 27, 2024

blader1989 said:
the customer code 121002730 when counting purchased product should be 1 because the product 44 purchased by he/she was 0

So the count for customer code 121001943 should also be zero since that code has a negative total sales?

blader1989.xlsm

A

B

G

H

I

J

K

1

Product Name

Customer Code

Total Sales

Jan

2

Product 44

121002462

1000000

121002462

2

3

Product 44

121002730

2000000

121002730

1

should be 1

4

Product 44

121002730

-2000000

121003038

1

5

Product 44

121003038

2000000

121001076

1

6

Product 46

121001076

2033330

121002507

1

7

Product 46

121002462

2033330

121002752

1

8

Product 46

121002507

4066660

121001792

1

9

Product 46

121002730

4066660

121001943

0

??

10

Product 46

121002730

6099990

11

Product 46

121002730

2033330

12

Product 46

121002730

2033330

13

Product 46

121002752

2033330

14

Product 51

121001792

8546178

15

Product 51

121001792

4501422

16

Product 51

121001943

-3261900

17

Sheet2

Cell Formulas
Range	Formula
I2:I9	I2	=UNIQUE(B2:B16)
J2:J9	J2	=BYROW(I2#,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(A2:A16,(B2:B16=r)*(SUMIFS(G2:G16,A2:A16,A2:A16,B2:B16,r)>0),NA()))),0)))
Dynamic array formulas.

dermie_72 · Mar 27, 2024

Excel Formula:

=BYROW(I2#,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(A2:A16,B2:B16=r,D2:D16=">0",NA()))),0)))

You wanted to only have items with a positive value in D, so you would need to include that in your filter

Peter_SSs · Mar 27, 2024

@dermie_72
That isn't a valid formula.

dermie_72 said:
You wanted to only have items with a positive value in D,

Also, I think the OP does not want just rows with positive value but customers who have positive total for a product in D

shaowu459 · Mar 28, 2024

Glad to see that your question has been resolved, but I would like to offer an alternative solution in case the PIVOTBY function is available.

Code:

=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)

Book1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

1

Product Name

Customer Code

Billing Date

Qty

Year

Month

Total Sales

2

Product 45

121001076

2023-2-8

5

2023

2

864285

01 Jan

02 Feb

3

Product 46

121001076

2023-1-4

10

2023

1

2033330

121001076

1

2

4

Product 46

121001076

2023-2-1

20

2023

2

4066660

121001258

1

5

Product 51

121001258

2023-2-20

60

2023

2

3914280

121001792

1

6

Product 51

121001258

2023-2-24

30

2023

2

1957140

121002462

2

7

Product 51

121001792

2023-1-4

131

2023

1

8546178

121002507

1

8

Product 51

121001792

2023-1-9

69

2023

1

4501422

121002535

1

9

Product 51

121001943

2023-1-4

-50

2023

1

-3261900

121002730

2

3

10

Product 51

121001943

2023-2-23

50

2023

2

3261900

121002752

1

11

Product 44

121002462

2023-1-14

10

2023

1

1000000

121003038

2

12

Product 44

121002462

2023-2-28

20

2023

2

2000000

121003326

1

13

Product 46

121002462

2023-1-14

10

2023

1

2033330

121003349

1

14

Product 46

121002462

2023-2-28

20

2023

2

4066660

121003359

1

15

Product 46

121002507

2023-1-14

20

2023

1

4066660

121003423

2

16

Product 51

121002507

2023-2-16

20

2023

2

1304760

121003739

2

1

17

Product 46

121002535

2023-2-7

4

2023

2

813332

18

Product 19

121002730

2023-2-13

10

2023

2

985710

19

Product 44

121002730

2023-1-10

20

2023

1

2000000

20

Product 44

121002730

2023-1-27

30

2023

1

3000000

21

Product 44

121002730

2023-2-13

30

2023

2

3000000

22

Product 44

121002730

2023-2-23

30

2023

2

3000000

23

Product 44

121002730

2023-2-28

20

2023

2

2000000

24

Product 46

121002730

2023-1-10

20

2023

1

4066660

25

Product 46

121002730

2023-1-27

30

2023

1

6099990

26

Product 46

121002730

2023-1-31

10

2023

1

2033330

27

Product 46

121002730

2023-1-31

10

2023

1

2033330

28

Product 46

121002730

2023-2-13

20

2023

2

4066660

29

Product 46

121002730

2023-2-23

20

2023

2

4066660

30

Product 46

121002730

2023-2-28

10

2023

2

2033330

31

Product 46

121002752

2023-1-14

10

2023

1

2033330

32

Product 44

121003038

2023-1-5

20

2023

1

2000000

33

Product 44

121003038

2023-2-21

20

2023

2

2000000

34

Product 44

121003038

2023-2-24

20

2023

2

2000000

35

Product 46

121003038

2023-1-5

25

2023

1

5083325

36

Product 46

121003038

2023-1-27

20

2023

1

4066660

37

Product 46

121003038

2023-1-28

10

2023

1

2033330

38

Product 46

121003038

2023-2-21

10

2023

2

2033330

39

Product 46

121003038

2023-2-24

20

2023

2

4066660

40

Product 44

121003326

2023-1-16

10

2023

1

1000000

41

Product 44

121003326

2023-1-27

30

2023

1

3000000

42

Product 51

121003349

2023-2-17

50

2023

2

3261900

43

Product 51

121003359

2023-1-16

41

2023

1

2674758

44

Product 44

121003423

2023-1-16

10

2023

1

1000000

45

Product 44

121003423

2023-1-27

20

2023

1

2000000

46

Product 46

121003423

2023-1-16

30

2023

1

6099990

47

Product 46

121003423

2023-1-27

30

2023

1

6099990

48

Product 44

121003739

2023-1-9

5

2023

1

500000

49

Product 44

121003739

2023-2-15

5

2023

2

500000

50

Product 46

121003739

2023-1-9

5

2023

1

101666

51

52

Sheet1

Cell Formulas
Range	Formula
I2:K16	I2	=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
Dynamic array formulas.

Peter_SSs · Mar 28, 2024

blader1989 said:
Thank you @dermie_72 and @Peter_SSs everything worked well now. I hope your solution can help all who seek the problem like me in future. If I have any question I will send the message to you.

Have a nice day!!! Cheers!

You're welcome. Thanks for the follow-up.

blader1989 · Mar 26, 2024

dermie_72 said:
Not sure if this is what you're after:
I put your sample data into a spreadsheet A1:G50
I created a unique table of Customer codes via the unique function into I2

Excel Formula:

=UNIQUE(B2:B50)

Then copied the month's from the table to the heading, IE J1:K1 became 1 & 2
I then used a formula to count the number of sku's purchased by customer that month.

Excel Formula:

=COUNTIFS($B:$B,$I2,$F:$F,J$1,$D:$D,">0")

And encompassed the range you're wanting to detect.
In Q2 I added the Number of SKU's going from 1 down and used a formula in R2 going across the number of months (You might need to change the start column depending on the number of months in the previous table.

Excel Formula:

=COUNTIF(J$:J$,$Q2)

This returns the table that you requested. Hope that helps.

Hi Dermie,

Thank you so much, that's look promising, I have applied your approach and found how can I modify that if they buy same product, it will only count as 1? In the image below you can see this customer code 121002730 buy only 2 product even he/she bought in multiple times.

Peter_SSs · Mar 26, 2024

@blader1989
It would help you get better/faster replies if you could give your sample data in a form helpers can easily test with.

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Try a formula like this in J2 for the layout shown in post #3

Excel Formula:

=BYROW(I2:I4,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(A2:A50,B2:B50=r,NA()))),0)))

Group number of purchased SKUs by counting customer

blader1989

New Member

Attachments

Excel Facts

dermie_72

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

dermie_72

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

shaowu459

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

blader1989

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Group number of purchased SKUs by counting customer

New Member

Attachments

Excel Facts

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock