Looking for unique values with 2 conditions

[Zibi]

Hello,

can someone please help me? I need to look through 300K rows and identify unique count if 2 conditions are meet. Column A has dates which would be used to identify as unique column B contains Day mane witch it is the 1 st criteria and column C has store ID witch it is 2 criteria.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Day	STORE_ID			Store	Day	Day	Day	Day	Day	Day	Day
2	12/28/2017	Thursday	1			2	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday
3	12/28/2017	Thursday	2
4	12/28/2017	Thursday	2			Output	4	0	0		3	0	0
5	12/28/2017	Thursday	3
6	1/4/2018	Thursday	5
7	1/4/2018	Thursday	2
8	1/4/2018	Thursday	1
9	1/11/2018	Thursday	2
10	1/11/2018	Thursday	2
11	1/18/2018	Thursday	1
12	1/18/2018	Thursday	5
13	1/25/2018	Thursday	6
14	1/25/2018	Thursday	2
15	1/25/2018	Thursday	2
16	12/28/2017	Monday	1
17	12/28/2017	Monday	8
18	12/28/2017	Monday	9
19	12/28/2017	Monday	6
20	1/4/2018	Monday	5
21	1/4/2018	Monday	2
22	1/4/2018	Monday	1
23	1/11/2018	Monday	2
24	1/11/2018	Monday	2
25	1/18/2018	Monday	1
26	1/18/2018	Monday	5
27	1/25/2018	Monday	6
28	1/25/2018	Monday	2
29	1/25/2018	Monday	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Day	STORE_ID			Store	Day	Day	Day	Day	Day	Day	Day
2	12/28/2017	Thursday	1			2	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday
3	12/28/2017	Thursday	2
4	12/28/2017	Thursday	2			Output	4	0	0	0	3	0	0
5	12/28/2017	Thursday	3
6	1/4/2018	Thursday	5
7	1/4/2018	Thursday	2
8	1/4/2018	Thursday	1
9	1/11/2018	Thursday	2
10	1/11/2018	Thursday	2
11	1/18/2018	Thursday	1
12	1/18/2018	Thursday	5
13	1/25/2018	Thursday	6
14	1/25/2018	Thursday	2
15	1/25/2018	Thursday	2
16	12/28/2017	Monday	1
17	12/28/2017	Monday	8
18	12/28/2017	Monday	9
19	12/28/2017	Monday	6
20	1/4/2018	Monday	5
21	1/4/2018	Monday	2
22	1/4/2018	Monday	1
23	1/11/2018	Monday	2
24	1/11/2018	Monday	2
25	1/18/2018	Monday	1
26	1/18/2018	Monday	5
27	1/25/2018	Monday	6
28	1/25/2018	Monday	2
29	1/25/2018	Monday	2
30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula G4 {=SUM(SIGN(FREQUENCY(IF(($C$2:$C$29=$F$2)*($B$2:$B$29=G2),$A$2:$A$29),$A$2:$A$29)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the array formula in G4, confirm with Control+Shift+Enter. Then drag right as needed. Let us know how it works.

 

[Zibi]

Hi Eric, thanks for the reply to my post, I did try that formula. It works but for large data set i am working with it takes forever to calculate. I am hoping that VBA would be able to do this more efficient.
again Thank you.

 

[Eric W]

You didn't say if you wanted a UDF (a user defined function to use in place of the formula in G4), or a regular macro that calculates the values and places them on the sheet. The regular macro would be faster, and cause less delay in your spreadsheet, but it only runs when called. I took that approach.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:

Sub CountUniques()
Dim MyRange As Range, MyTable As Range, MyTab As Variant, MyDictofDicts As Object, i As Long, MyData As Variant

    Set MyRange = Sheets("Sheet4").Range("A:C")
    Set MyTable = Sheets("Sheet4").Range("F1:M4")
    
    MyData = MyRange.Resize(MyRange.Resize(1, 1).Offset(Rows.Count - 1).End(xlUp).Row).Value
    MyTab = MyTable.Value
    
    Set MyDictofDicts = CreateObject("Scripting.Dictionary")
    For i = 1 To 7
        MyDictofDicts.Add MyTab(2, i + 1), CreateObject("Scripting.Dictionary")
    Next i
    
    For i = 2 To UBound(MyData)
        If MyData(i, 3) = MyTab(2, 1) Then MyDictofDicts(MyData(i, 2))(MyData(i, 1)) = 1
    Next i
    
    For i = 1 To 7
        MyTab(4, i + 1) = MyDictofDicts(MyTab(2, i + 1)).Count
    Next i
    MyTable = MyTab
    
End Sub

Change the ranges in red to match your sheet. Switch back to Excel. Press Alt-F8, choose CountUniques, and press Run.

Let me know if this works for you, or if you want a UDF.

 

[Zibi]

Hi Eric It works perfect, Thank you for your help you are awesome

 

[Eric W]

You're welcome!