Unique dealers count who has billed combinations of each products

[Bijeesh P]

The sample data given below

Dealer	Product type	Date	Employee
441075K023	TILEFIXO	27-04-23	Jayaraman
441075K023	WEATHERPRO WP+ 200	27-04-23	Jayaraman
441075K023	TILEFIXO	30-05-23	Jayaraman
441075K023	TILEFIXO CEMENTIOUS GROUT	30-05-23	Jayaraman
441076M023	TILEFIXO	30-05-23	VIJAYKUMAR A
441076M023	WEATHERPRO WP+ 200	30-05-23	VIJAYKUMAR A
441076M023	TILEFIXO	30-05-23	VIJAYKUMAR A
441179R016	WEATHERPRO WP+ 200	20-05-23	Prakash
441179R016	WEATHERPRO WP+ 200	20-05-23	Prakash
441179R016	WEATHERPRO WP+ 200	20-05-23	Prakash
441179R016	WEATHERPRO WP+ 200	20-05-23	Prakash
441113J006	FIXOBLOCK	20-05-23	Palani
441113J006	TILEFIXO	20-05-23	Palani
441004R076	SEAL & DRY SBR	30-04-23	Ganesh
441004R076	SEAL & DRY SBR	30-04-23	Ganesh
441004R076	TILEFIXO	30-04-23	Ganesh
441004R076	WEATHERPRO WP+ 200	30-04-23	Ganesh
441004R076	WEATHERPRO WP+ 200	31-05-23	Ganesh
441075A016	TILEFIXO	27-04-23	M Karthi
441075A016	SEAL & DRY HIFLEX	27-04-23	M Karthi
441075A016	TILEFIXO	29-04-23	M Karthi

Unique dealer count in product combinations with month and employee wise. There are around a total of15 types of products. and 40 employees. The required result sample as below

Month	Employee	Product	Unique Dealer Count
Apr	VIJAYKUMAR A	Only one product	5​
Apr	VIJAYKUMAR A	Two product	4​
May	Prakash	Three Product	4​
May	Prakash	One product	5​
Apr	Palani	6 product	7​
Apr	Palani	8 product	2​
May	Ganesh	6 product	4​
May	Ganesh	Only one product	5​
Apr	VIJAYKUMAR A	Two product	3​
Apr	VIJAYKUMAR A	Three Product	6​
May	Prakash	One product	7​
May	Prakash	6 product	2​
Apr	Palani	8 product	4​
Apr	Palani	Only one product	4​
May	Ganesh	Two product	1​
May	Ganesh	Three Product	4​

Thanks in advance.

 

[etaf]

i'm not following the summary table based on the data table , i'm assuming the data table is only a small set of what in the summary

I have done a pivot table - which is close to your output
with a product count

will a pivot table work for you ?

we can layout as you have done by date then dealer

are you still using excel 2016 version ????

Dealer-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Dealer	Product type	Date	Employee
2	441075K023	TILEFIXO	4/27/23	Jayaraman					Date	Dealer	Employee	Product type	Count of Product type
3	441075K023	WEATHERPRO WP+ 200	4/27/23	Jayaraman					Apr	441004R076	Ganesh	SEAL & DRY SBR	2
4	441075K023	TILEFIXO	5/30/23	Jayaraman								TILEFIXO	1
5	441075K023	TILEFIXO CEMENTIOUS GROUT	5/30/23	Jayaraman								WEATHERPRO WP+ 200	1
6	441076M023	TILEFIXO	5/30/23	VIJAYKUMAR A						441075A016	M Karthi	SEAL & DRY HIFLEX	1
7	441076M023	WEATHERPRO WP+ 200	5/30/23	VIJAYKUMAR A								TILEFIXO	2
8	441076M023	TILEFIXO	5/30/23	VIJAYKUMAR A						441075K023	Jayaraman	TILEFIXO	1
9	441179R016	WEATHERPRO WP+ 200	5/20/23	Prakash								WEATHERPRO WP+ 200	1
10	441179R016	WEATHERPRO WP+ 200	5/20/23	Prakash					May	441004R076	Ganesh	WEATHERPRO WP+ 200	1
11	441179R016	WEATHERPRO WP+ 200	5/20/23	Prakash						441075K023	Jayaraman	TILEFIXO	1
12	441179R016	WEATHERPRO WP+ 200	5/20/23	Prakash								TILEFIXO CEMENTIOUS GROUT	1
13	441113J006	FIXOBLOCK	5/20/23	Palani						441076M023	VIJAYKUMAR A	TILEFIXO	2
14	441113J006	TILEFIXO	5/20/23	Palani								WEATHERPRO WP+ 200	1
15	441004R076	SEAL & DRY SBR	4/30/23	Ganesh						441113J006	Palani	FIXOBLOCK	1
16	441004R076	SEAL & DRY SBR	4/30/23	Ganesh								TILEFIXO	1
17	441004R076	TILEFIXO	4/30/23	Ganesh						441179R016	Prakash	WEATHERPRO WP+ 200	4
18	441004R076	WEATHERPRO WP+ 200	4/30/23	Ganesh
19	441004R076	WEATHERPRO WP+ 200	5/31/23	Ganesh
20	441075A016	TILEFIXO	4/27/23	M Karthi
21	441075A016	SEAL & DRY HIFLEX	4/27/23	M Karthi
22	441075A016	TILEFIXO	4/29/23	M Karthi
Sheet1

i have also added to dropbox - BUT only for a few days - just so you can see the pivot table and setup

Dropbox - File Deleted - Simplify your life

www.dropbox.com