Count of unique occurrences with multiple criteria/exceptions

[sdk1013]

I need help finding the count of unique encounters for data similar to the example below. A unique encounter = unique ID at a unique location receiving a unique service on a unique date by a unique provider. I need to be able to filter and run unique encounter counts based on those variables, i.e. unique encounters at location VA vs CA, for service a vs b vs c, on [date], and by provider x vs y.

Thanks so much for any guidance!

ID	LOCATION	SERVICE	SERVICE DATE	PROVIDER
1​	VA	a	12/1/2021​	x
1​	VA	a	12/1/2021​	x
1​	VA	a	12/1/2021​	y
1​	VA	b	12/1/2021​	x
1​	VA	a	12/2/2021​	x
1​	VA	b	12/2/2021​	y
1​	VA	c	12/1/2021​	x
1​	VA	c	12/3/2021​	x
1​	VA	c	12/2/2021​	y
2​	CA	a	12/1/2021​	x
2​	CA	a	12/1/2001​	y
2​	CA	c	12/1/2021​	x
2​	CA	c	12/1/2021​	x
2​	CA	b	12/2/2021​	x
2​	CA	b	12/2/2021​	y
2​	CA	a	12/2/2021​	x
2​	CA	b	12/2/2021​	x

 

[offthelip]

this subroutine will give the count of the uniques in all 5 columns , if you just want the first 4 just change the j loop to 4, ditto 3, and 2. If you want to miss one out in the middle just put and if statement in to skip that loop

Sub Countoccurence()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ary = Worksheets("Sheet1").Range(Cells(1, 1), Cells(lastrow, 5))
   Dim Dic As Object
   Set Dic = CreateObject("Scripting.dictionary")
      
   For i = 1 To UBound(ary, 1)
        ' concatenate 5 columns
         tt = ""
         For j = 1 To 5
         tt = tt & ary(i, j)
         Next j
         Dic(tt) = i
   Next i
 MsgBox ("Number of unique values is " & Dic.Count)
 
End Sub

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	LOCATION	SERVICE	SERVICE DATE	PROVIDER		ID	Location	Service	Service Date	Provider		Unique count
2	1	VA	a	12/1/2021	x		*	VA	*	12/2/2021	*		3
3	1	VA	a	12/1/2021	x
4	1	VA	a	12/1/2021	y
5	1	VA	b	12/1/2021	x
6	1	VA	a	12/2/2021	x
7	1	VA	b	12/2/2021	y
8	1	VA	c	12/1/2021	x
9	1	VA	c	12/3/2021	x
10	1	VA	c	12/2/2021	y
11	2	CA	a	12/1/2021	x
12	2	CA	a	12/1/2021	y
13	2	CA	c	12/1/2021	x
14	2	CA	c	12/1/2021	x
15	2	CA	b	12/2/2021	x
16	2	CA	b	12/2/2021	y
17	2	CA	a	12/2/2021	x
18	2	CA	b	12/2/2021	x
Sheet4

Cell Formulas
Range	Formula
M2	M2	=ROWS(UNIQUE(FILTER(A2:E18,((A2:A18=G2)+(G2="*"))*((B2:B18=H2)+(H2="*"))*((C2:C18=I2)+(I2="*"))*((D2:D18=J2)+(J2="*"))*((E2:E18=K2)+(K2="*")))))

In the G2:K2 cells, put an asterisk ("*") in the cell if you don't want to restrict that column. Otherwise, put the value you want to see.

 

[sdk1013]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	LOCATION	SERVICE	SERVICE DATE	PROVIDER		ID	Location	Service	Service Date	Provider		Unique count
2	1	VA	a	12/1/2021	x		*	VA	*	12/2/2021	*		3
3	1	VA	a	12/1/2021	x
4	1	VA	a	12/1/2021	y
5	1	VA	b	12/1/2021	x
6	1	VA	a	12/2/2021	x
7	1	VA	b	12/2/2021	y
8	1	VA	c	12/1/2021	x
9	1	VA	c	12/3/2021	x
10	1	VA	c	12/2/2021	y
11	2	CA	a	12/1/2021	x
12	2	CA	a	12/1/2021	y
13	2	CA	c	12/1/2021	x
14	2	CA	c	12/1/2021	x
15	2	CA	b	12/2/2021	x
16	2	CA	b	12/2/2021	y
17	2	CA	a	12/2/2021	x
18	2	CA	b	12/2/2021	x
Sheet4

Cell Formulas
Range	Formula
M2	M2	=ROWS(UNIQUE(FILTER(A2:E18,((A2:A18=G2)+(G2="*"))*((B2:B18=H2)+(H2="*"))*((C2:C18=I2)+(I2="*"))*((D2:D18=J2)+(J2="*"))*((E2:E18=K2)+(K2="*")))))

In the G2:K2 cells, put an asterisk ("*") in the cell if you don't want to restrict that column. Otherwise, put the value you want to see.

This is great, thanks so much!
As a follow-up question to take this to the next level, how would you go about making that output table include all the options for location, service, date, and provider? Is that possible? I'm imagining a filter drop down for those columns so that I can select whatever exclusions readily. For example, a filter drop down for location having *, VA, and CA, and one for service having *, a, b, and c.

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	LOCATION	SERVICE	SERVICE DATE	PROVIDER		ID	Location	Service	Service Date	Provider		Unique count
2	1	VA	a	12/1/2021	x		*	VA	*	12/2/2021	*		3
3	1	VA	a	12/1/2021	x
4	1	VA	a	12/1/2021	y
5	1	VA	b	12/1/2021	x
6	1	VA	a	12/2/2021	x
7	1	VA	b	12/2/2021	y
8	1	VA	c	12/1/2021	x		ID	Location	Service	Service Date	Provider
9	1	VA	c	12/3/2021	x		1	VA	a	44531	x
10	1	VA	c	12/2/2021	y		2	CA	b	12/2/2021	y
11	2	CA	a	12/1/2021	x		*	*	c	12/3/2021	*
12	2	CA	a	12/1/2021	y				*	*
13	2	CA	c	12/1/2021	x
14	2	CA	c	12/1/2021	x
15	2	CA	b	12/2/2021	x
16	2	CA	b	12/2/2021	y
17	2	CA	a	12/2/2021	x
18	2	CA	b	12/2/2021	x
19
Sheet7

Cell Formulas
Range	Formula
M2	M2	=ROWS(UNIQUE(FILTER(A2:E18,((A2:A18=G2)+(G2="*"))*((B2:B18=H2)+(H2="*"))*((C2:C18=I2)+(I2="*"))*((D2:D18=J2)+(J2="*"))*((E2:E18=K2)+(K2="*")))))
K9:K11,I9:J12,G9:H11	G9	=IFERROR(INDEX(UNIQUE(A2:A18),SEQUENCE(ROWS(UNIQUE(A2:A18))+1)),"*")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2:K2	List	=G9#

The formula to get a unique list of values is just =UNIQUE(A2:A18) but I had to make it a bit more complicated to get the * on the list. Then once you have the unique lists (and you can put them anywhere, even on another sheet), you can use them in a Data Validation drop-down list.