count without duplicate

[kelvin_9]

greeting to all

i was wondering is there any formula that i can quickly return how many order SN total in daily without duplicate calculation?
example: 11 order on 12/29/2023, 18 order on 12/23/2023

Book1
	A		C		E
1	Order Sn		Created At		Updated At
2	231220121812472908		12/20/2023 12:18:11 PM		12/29/2023
3	231220121812472908		12/20/2023 12:18:11 PM		12/29/2023
4	231220121812472908		12/20/2023 12:18:11 PM		12/29/2023
5	231220121812472908		12/20/2023 12:18:11 PM		12/29/2023
6	231221132938506643		12/21/2023 1:29:38 PM		12/29/2023
7	231221132938506643		12/21/2023 1:29:38 PM		12/29/2023
8	231221132938506643		12/21/2023 1:29:38 PM		12/29/2023
9	231221133430881381		12/21/2023 1:34:30 PM		12/29/2023
10	231221123648304322		12/21/2023 12:36:48 PM		12/29/2023
11	231221120416786413		12/21/2023 12:04:16 PM		12/29/2023
12	231221121739646978		12/21/2023 12:17:39 PM		12/29/2023
13	231221120618872345		12/21/2023 12:06:18 PM		12/29/2023
14	231221120444923279		12/21/2023 12:04:44 PM		12/29/2023
15	231221120210753305		12/21/2023 12:02:10 PM		12/29/2023
16	231221125907305575		12/21/2023 12:59:07 PM		12/29/2023
17	231221122743920439		12/21/2023 12:27:43 PM		12/29/2023
18	231221122743920439		12/21/2023 12:27:43 PM		12/29/2023
19	231221122743920439		12/21/2023 12:27:43 PM		12/29/2023
20	231223234254746869		12/23/2023 11:42:54 PM		12/23/2023
21	231223233742614493		12/23/2023 11:37:42 PM		12/23/2023
22	231223222610680469		12/23/2023 10:26:10 PM		12/23/2023
23	231223202911106860		12/23/2023 8:29:11 PM		12/23/2023
24	231223202911106860		12/23/2023 8:29:11 PM		12/23/2023
25	231223183632783732		12/23/2023 6:36:32 PM		12/23/2023
26	231223183632783732		12/23/2023 6:36:32 PM		12/23/2023
27	231223183632783732		12/23/2023 6:36:32 PM		12/23/2023
28	231223180210735133		12/23/2023 6:02:10 PM		12/23/2023
29	231223175802304513		12/23/2023 5:58:02 PM		12/23/2023
30	231223171751820983		12/23/2023 5:17:51 PM		12/23/2023
31	231223171751820983		12/23/2023 5:17:51 PM		12/23/2023
32	231223152728698650		12/23/2023 3:27:28 PM		12/23/2023
33	231223141234889959		12/23/2023 2:12:34 PM		12/23/2023
34	231223134925477874		12/23/2023 1:49:25 PM		12/23/2023
35	231223124243904317		12/23/2023 12:42:43 PM		12/23/2023
36	231223120525808132		12/23/2023 12:05:25 PM		12/23/2023
37	231223104852546665		12/23/2023 10:48:52 AM		12/23/2023
38	231223032447570734		12/23/2023 3:24:47 AM		12/23/2023
39	231223005509634609		12/23/2023 12:55:09 AM		12/23/2023
40	231223005218848201		12/23/2023 12:52:18 AM		12/23/2023
41	231223005218848201		12/23/2023 12:52:18 AM		12/23/2023
42	231223005009861201		12/23/2023 12:50:09 AM		12/23/2023
43	231222234503753308		12/22/2023 11:45:03 PM		12/22/2023
44	231222232320607616		12/22/2023 11:23:20 PM		12/22/2023
45	231222232320607616		12/22/2023 11:23:20 PM		12/22/2023
46	231222225516431427		12/22/2023 10:55:16 PM		12/22/2023
47	231222222504727795		12/22/2023 10:25:04 PM		12/22/2023
48	231222221940669711		12/22/2023 10:19:40 PM		12/22/2023
49	231222201636458646		12/22/2023 8:16:36 PM		12/22/2023
50	231222201440797212		12/22/2023 8:14:40 PM		12/22/2023
51	231222201218388674		12/22/2023 8:12:18 PM		12/22/2023
52	231222201122283537		12/22/2023 8:11:22 PM		12/22/2023
53	231222201122283537		12/22/2023 8:11:22 PM		12/22/2023
54	231222200915788924		12/22/2023 8:09:15 PM		12/22/2023
55	231222200915788924		12/22/2023 8:09:15 PM		12/22/2023
56	231222194222275933		12/22/2023 7:42:22 PM		12/22/2023
57	231222193411603188		12/22/2023 7:34:11 PM		12/22/2023
58	231222193411603188		12/22/2023 7:34:11 PM		12/22/2023
59	231222193017662321		12/22/2023 7:30:17 PM		12/22/2023
60	231222192424400673		12/22/2023 7:24:24 PM		12/22/2023
61	231222192424400673		12/22/2023 7:24:24 PM		12/22/2023
62	231222192424400673		12/22/2023 7:24:24 PM		12/22/2023
63	231222190119537731		12/22/2023 7:01:19 PM		12/22/2023
64	231222190119537731		12/22/2023 7:01:19 PM		12/22/2023
65	231222190119537731		12/22/2023 7:01:19 PM		12/22/2023
66	231222185358884802		12/22/2023 6:53:58 PM		12/22/2023
67	231222184703164367		12/22/2023 6:47:03 PM		12/22/2023
68	231222183143967816		12/22/2023 6:31:43 PM		12/22/2023
69	231222181912930137		12/22/2023 6:19:12 PM		12/22/2023
70	231222181719731719		12/22/2023 6:17:19 PM		12/22/2023
71	231222180945231861		12/22/2023 6:09:45 PM		12/22/2023
72	231222180945231861		12/22/2023 6:09:45 PM		12/22/2023
73	231222180303661781		12/22/2023 6:03:03 PM		12/22/2023
74	231222180303661781		12/22/2023 6:03:03 PM		12/22/2023
75	231222175520895849		12/22/2023 5:55:20 PM		12/22/2023
76	231222175246466937		12/22/2023 5:52:46 PM		12/22/2023
77	231222175246466937		12/22/2023 5:52:46 PM		12/22/2023
Sheet2

 

[kelvin_9]

sorry for misunderstanding,

F1 will be today(12/29/2023), F2 will be next(12/23/2023)..... etc
i want the formula on G1, G2.....

thanks for your reply

 

[BishopDesigns]

To not mess with your original data I copied Column C to Column D and Column A to Column E then ran this macro.

Sub CountUnique()
'
' CountUnique Macro
'

'
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range("$D$1:$E$77").RemoveDuplicates Columns:=Array(1, 2), Header _
        :=xlYes
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C4:R77C5", Version:=8).CreatePivotTable TableDestination:= _
        "Sheet2!R1C7", TableName:="PivotTable3", DefaultVersion:=8
    Sheets("Sheet2").Select
    Cells(1, 7).Select
    With ActiveSheet.PivotTables("PivotTable3")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable3").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Updated At")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Order Sn"), "Sum of Order Sn", xlSum
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of Order Sn")
        .Caption = "Count of Order Sn"
        .Function = xlCount
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotSelect "'Updated At'[All]", _
        xlLabelOnly + xlFirstRow, True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Updated At")
        .PivotItems("(blank)").Visible = False
    End With
    Range("F1").Select
End Sub

 

[felixstraube]

How about?:

Libro1.xlsx
	A	B	C	D	E	F	G	H
1	Order Sn		Created At		Updated At			Total # of unique order Sn
2	231220121812472000		20/12/2023 12:18		29/12/2023 14:45			53
3	231220121812472000		20/12/2023 12:18		29/12/2023 14:45
4	231220121812472000		20/12/2023 12:18		29/12/2023 14:45		Date	# of Unique Order Sn by date
5	231220121812472000		20/12/2023 12:18		29/12/2023 14:45		20/12/2023	1
6	231221132938506000		21/12/2023 13:29		29/12/2023 12:39		21/12/2023	10
7	231221132938506000		21/12/2023 13:29		29/12/2023 12:39		22/12/2023	24
8	231221132938506000		21/12/2023 13:29		29/12/2023 12:39		23/12/2023	18
9	231221133430881000		21/12/2023 13:34		29/12/2023 12:35
10	231221123648304000		21/12/2023 12:36		29/12/2023 11:24
11	231221120416786000		21/12/2023 12:04		29/12/2023 11:12
12	231221121739646000		21/12/2023 12:17		29/12/2023 11:11
13	231221120618872000		21/12/2023 12:06		29/12/2023 10:23
14	231221120444923000		21/12/2023 12:04		29/12/2023 10:20
15	231221120210753000		21/12/2023 12:02		29/12/2023 10:18
16	231221125907305000		21/12/2023 12:59		29/12/2023 10:15
17	231221122743920000		21/12/2023 12:27		29/12/2023 10:14
18	231221122743920000		21/12/2023 12:27		29/12/2023 10:14
19	231221122743920000		21/12/2023 12:27		29/12/2023 10:14
20	231223234254746000		23/12/2023 23:42		23/12/2023 23:43
21	231223233742614000		23/12/2023 23:37		23/12/2023 23:37
22	231223222610680000		23/12/2023 22:26		23/12/2023 22:26
23	231223202911106000		23/12/2023 20:29		23/12/2023 20:29
24	231223202911106000		23/12/2023 20:29		23/12/2023 20:29
25	231223183632783000		23/12/2023 18:36		23/12/2023 18:36
26	231223183632783000		23/12/2023 18:36		23/12/2023 18:36
27	231223183632783000		23/12/2023 18:36		23/12/2023 18:36
28	231223180210735000		23/12/2023 18:02		23/12/2023 18:03
29	231223175802304000		23/12/2023 17:58		23/12/2023 17:59
30	231223171751820000		23/12/2023 17:17		23/12/2023 17:19
31	231223171751820000		23/12/2023 17:17		23/12/2023 17:19
32	231223152728698000		23/12/2023 15:27		23/12/2023 15:27
33	231223141234889000		23/12/2023 14:12		23/12/2023 14:13
34	231223134925477000		23/12/2023 13:49		23/12/2023 13:49
35	231223124243904000		23/12/2023 12:42		23/12/2023 12:43
36	231223120525808000		23/12/2023 12:05		23/12/2023 12:05
37	231223104852546000		23/12/2023 10:48		23/12/2023 11:07
38	231223032447570000		23/12/2023 03:24		23/12/2023 03:25
39	231223005509634000		23/12/2023 00:55		23/12/2023 00:55
40	231223005218848000		23/12/2023 00:52		23/12/2023 00:54
41	231223005218848000		23/12/2023 00:52		23/12/2023 00:54
42	231223005009861000		23/12/2023 00:50		23/12/2023 00:50
43	231222234503753000		22/12/2023 23:45		22/12/2023 23:45
44	231222232320607000		22/12/2023 23:23		22/12/2023 23:24
45	231222232320607000		22/12/2023 23:23		22/12/2023 23:24
46	231222225516431000		22/12/2023 22:55		22/12/2023 22:55
47	231222222504727000		22/12/2023 22:25		22/12/2023 22:26
48	231222221940669000		22/12/2023 22:19		22/12/2023 22:20
49	231222201636458000		22/12/2023 20:16		22/12/2023 20:17
50	231222201440797000		22/12/2023 20:14		22/12/2023 20:15
51	231222201218388000		22/12/2023 20:12		22/12/2023 20:14
52	231222201122283000		22/12/2023 20:11		22/12/2023 20:12
53	231222201122283000		22/12/2023 20:11		22/12/2023 20:12
54	231222200915788000		22/12/2023 20:09		22/12/2023 20:11
55	231222200915788000		22/12/2023 20:09		22/12/2023 20:11
56	231222194222275000		22/12/2023 19:42		22/12/2023 19:43
57	231222193411603000		22/12/2023 19:34		22/12/2023 19:34
58	231222193411603000		22/12/2023 19:34		22/12/2023 19:34
59	231222193017662000		22/12/2023 19:30		22/12/2023 19:30
60	231222192424400000		22/12/2023 19:24		22/12/2023 19:26
61	231222192424400000		22/12/2023 19:24		22/12/2023 19:26
62	231222192424400000		22/12/2023 19:24		22/12/2023 19:26
63	231222190119537000		22/12/2023 19:01		22/12/2023 19:02
64	231222190119537000		22/12/2023 19:01		22/12/2023 19:02
65	231222190119537000		22/12/2023 19:01		22/12/2023 19:02
66	231222185358884000		22/12/2023 18:53		22/12/2023 18:54
67	231222184703164000		22/12/2023 18:47		22/12/2023 18:47
68	231222183143967000		22/12/2023 18:31		22/12/2023 18:33
69	231222181912930000		22/12/2023 18:19		22/12/2023 18:19
70	231222181719731000		22/12/2023 18:17		22/12/2023 18:17
71	231222180945231000		22/12/2023 18:09		22/12/2023 18:10
72	231222180945231000		22/12/2023 18:09		22/12/2023 18:10
73	231222180303661000		22/12/2023 18:03		22/12/2023 18:05
74	231222180303661000		22/12/2023 18:03		22/12/2023 18:05
75	231222175520895000		22/12/2023 17:55		22/12/2023 17:55
76	231222175246466000		22/12/2023 17:52		22/12/2023 17:52
77	231222175246466000		22/12/2023 17:52		22/12/2023 17:52
Hoja1

Cell Formulas
Range	Formula
H2	H2	=SUM((FREQUENCY(MATCH($A$2:$A$77,$A$2:$A$77,0),MATCH($A$2:$A$77,$A$2:$A$77,0))>0)*1)
H5:H8	H5	=SUM((FREQUENCY(MATCH($A$2:$A$77,$A$2:$A$77,0)*(INT($C$2:$C$77)=$G5),MATCH($A$2:$A$77,$A$2:$A$77,0)*(INT($C$2:$C$77)=$G5))>0)*1)-1

 

[kelvin_9]

To not mess with your original data I copied Column C to Column D and Column A to Column E then ran this macro.

Sub CountUnique()
'
' CountUnique Macro
'

'
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range("$D$1:$E$77").RemoveDuplicates Columns:=Array(1, 2), Header _
        :=xlYes
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C4:R77C5", Version:=8).CreatePivotTable TableDestination:= _
        "Sheet2!R1C7", TableName:="PivotTable3", DefaultVersion:=8
    Sheets("Sheet2").Select
    Cells(1, 7).Select
    With ActiveSheet.PivotTables("PivotTable3")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable3").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Updated At")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Order Sn"), "Sum of Order Sn", xlSum
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of Order Sn")
        .Caption = "Count of Order Sn"
        .Function = xlCount
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotSelect "'Updated At'[All]", _
        xlLabelOnly + xlFirstRow, True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Updated At")
        .PivotItems("(blank)").Visible = False
    End With
    Range("F1").Select
End Sub

thank you for your reply, BishopDesigns

is it means, only column D and E 2 columns and run the macro?
it stopped here:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet2!R1C4:R77C5", Version:=8).CreatePivotTable TableDestination:= _
"Sheet2!R1C7", TableName:="PivotTable3", DefaultVersion:=8

 

[kelvin_9]

How about?:

Libro1.xlsx
	A	B	C	D	E	F	G	H
1	Order Sn		Created At		Updated At			Total # of unique order Sn
2	231220121812472000		20/12/2023 12:18		29/12/2023 14:45			53
3	231220121812472000		20/12/2023 12:18		29/12/2023 14:45
4	231220121812472000		20/12/2023 12:18		29/12/2023 14:45		Date	# of Unique Order Sn by date
5	231220121812472000		20/12/2023 12:18		29/12/2023 14:45		20/12/2023	1
6	231221132938506000		21/12/2023 13:29		29/12/2023 12:39		21/12/2023	10
7	231221132938506000		21/12/2023 13:29		29/12/2023 12:39		22/12/2023	24
8	231221132938506000		21/12/2023 13:29		29/12/2023 12:39		23/12/2023	18
9	231221133430881000		21/12/2023 13:34		29/12/2023 12:35
10	231221123648304000		21/12/2023 12:36		29/12/2023 11:24
11	231221120416786000		21/12/2023 12:04		29/12/2023 11:12
12	231221121739646000		21/12/2023 12:17		29/12/2023 11:11
13	231221120618872000		21/12/2023 12:06		29/12/2023 10:23
14	231221120444923000		21/12/2023 12:04		29/12/2023 10:20
15	231221120210753000		21/12/2023 12:02		29/12/2023 10:18
16	231221125907305000		21/12/2023 12:59		29/12/2023 10:15
17	231221122743920000		21/12/2023 12:27		29/12/2023 10:14
18	231221122743920000		21/12/2023 12:27		29/12/2023 10:14
19	231221122743920000		21/12/2023 12:27		29/12/2023 10:14
20	231223234254746000		23/12/2023 23:42		23/12/2023 23:43
21	231223233742614000		23/12/2023 23:37		23/12/2023 23:37
22	231223222610680000		23/12/2023 22:26		23/12/2023 22:26
23	231223202911106000		23/12/2023 20:29		23/12/2023 20:29
24	231223202911106000		23/12/2023 20:29		23/12/2023 20:29
25	231223183632783000		23/12/2023 18:36		23/12/2023 18:36
26	231223183632783000		23/12/2023 18:36		23/12/2023 18:36
27	231223183632783000		23/12/2023 18:36		23/12/2023 18:36
28	231223180210735000		23/12/2023 18:02		23/12/2023 18:03
29	231223175802304000		23/12/2023 17:58		23/12/2023 17:59
30	231223171751820000		23/12/2023 17:17		23/12/2023 17:19
31	231223171751820000		23/12/2023 17:17		23/12/2023 17:19
32	231223152728698000		23/12/2023 15:27		23/12/2023 15:27
33	231223141234889000		23/12/2023 14:12		23/12/2023 14:13
34	231223134925477000		23/12/2023 13:49		23/12/2023 13:49
35	231223124243904000		23/12/2023 12:42		23/12/2023 12:43
36	231223120525808000		23/12/2023 12:05		23/12/2023 12:05
37	231223104852546000		23/12/2023 10:48		23/12/2023 11:07
38	231223032447570000		23/12/2023 03:24		23/12/2023 03:25
39	231223005509634000		23/12/2023 00:55		23/12/2023 00:55
40	231223005218848000		23/12/2023 00:52		23/12/2023 00:54
41	231223005218848000		23/12/2023 00:52		23/12/2023 00:54
42	231223005009861000		23/12/2023 00:50		23/12/2023 00:50
43	231222234503753000		22/12/2023 23:45		22/12/2023 23:45
44	231222232320607000		22/12/2023 23:23		22/12/2023 23:24
45	231222232320607000		22/12/2023 23:23		22/12/2023 23:24
46	231222225516431000		22/12/2023 22:55		22/12/2023 22:55
47	231222222504727000		22/12/2023 22:25		22/12/2023 22:26
48	231222221940669000		22/12/2023 22:19		22/12/2023 22:20
49	231222201636458000		22/12/2023 20:16		22/12/2023 20:17
50	231222201440797000		22/12/2023 20:14		22/12/2023 20:15
51	231222201218388000		22/12/2023 20:12		22/12/2023 20:14
52	231222201122283000		22/12/2023 20:11		22/12/2023 20:12
53	231222201122283000		22/12/2023 20:11		22/12/2023 20:12
54	231222200915788000		22/12/2023 20:09		22/12/2023 20:11
55	231222200915788000		22/12/2023 20:09		22/12/2023 20:11
56	231222194222275000		22/12/2023 19:42		22/12/2023 19:43
57	231222193411603000		22/12/2023 19:34		22/12/2023 19:34
58	231222193411603000		22/12/2023 19:34		22/12/2023 19:34
59	231222193017662000		22/12/2023 19:30		22/12/2023 19:30
60	231222192424400000		22/12/2023 19:24		22/12/2023 19:26
61	231222192424400000		22/12/2023 19:24		22/12/2023 19:26
62	231222192424400000		22/12/2023 19:24		22/12/2023 19:26
63	231222190119537000		22/12/2023 19:01		22/12/2023 19:02
64	231222190119537000		22/12/2023 19:01		22/12/2023 19:02
65	231222190119537000		22/12/2023 19:01		22/12/2023 19:02
66	231222185358884000		22/12/2023 18:53		22/12/2023 18:54
67	231222184703164000		22/12/2023 18:47		22/12/2023 18:47
68	231222183143967000		22/12/2023 18:31		22/12/2023 18:33
69	231222181912930000		22/12/2023 18:19		22/12/2023 18:19
70	231222181719731000		22/12/2023 18:17		22/12/2023 18:17
71	231222180945231000		22/12/2023 18:09		22/12/2023 18:10
72	231222180945231000		22/12/2023 18:09		22/12/2023 18:10
73	231222180303661000		22/12/2023 18:03		22/12/2023 18:05
74	231222180303661000		22/12/2023 18:03		22/12/2023 18:05
75	231222175520895000		22/12/2023 17:55		22/12/2023 17:55
76	231222175246466000		22/12/2023 17:52		22/12/2023 17:52
77	231222175246466000		22/12/2023 17:52		22/12/2023 17:52
Hoja1

Cell Formulas
Range	Formula
H2	H2	=SUM((FREQUENCY(MATCH($A$2:$A$77,$A$2:$A$77,0),MATCH($A$2:$A$77,$A$2:$A$77,0))>0)*1)
H5:H8	H5	=SUM((FREQUENCY(MATCH($A$2:$A$77,$A$2:$A$77,0)*(INT($C$2:$C$77)=$G5),MATCH($A$2:$A$77,$A$2:$A$77,0)*(INT($C$2:$C$77)=$G5))>0)*1)-1

thank you for your reply, felixstraube

first of all, you have changed the date format to mm/dd/yyyy from dd/mm/yyyy
second, i have try your formula but it dosent work well like you, anything i did wrong?
last but not least, i copied column a to column m and then remove duplicate, it show 28 instead of your 27 because of the first and second row

Mrh Online - Order with SOH (Prompt) Production - Pick Up - Limited Store - Hourly2023-12-29-20-01-01.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Order Sn		Created At		Updated At								231220120020194113
2	231220120020194113		12/20/2023 12:00:20 PM		12/29/2023			27					231220120020194113
3	231220120020194113		12/20/2023 12:00:20 PM		12/29/2023								231220120041991140
4	231220120020194113		12/20/2023 12:00:20 PM		12/29/2023								231220120043888627
5	231220120041991140		12/20/2023 12:00:41 PM		12/20/2023		12/29/2023	0					231220120058221219
6	231220120043888627		12/20/2023 12:00:43 PM		12/20/2023		12/28/2023	0					231220120141307378
7	231220120043888627		12/20/2023 12:00:43 PM		12/20/2023		27/12/2023	0					231220120207370783
8	231220120058221219		12/20/2023 12:00:58 PM		12/29/2023		26/12/2023	0					231220120222477999
9	231220120141307378		12/20/2023 12:01:41 PM		12/29/2023								231220120232797837
10	231220120141307378		12/20/2023 12:01:41 PM		12/29/2023								231220120236501293
11	231220120141307378		12/20/2023 12:01:41 PM		12/29/2023								231220120236570287
12	231220120141307378		12/20/2023 12:01:41 PM		12/29/2023								231220120241652868
13	231220120141307378		12/20/2023 12:01:41 PM		12/29/2023								231220120244585174
14	231220120207370783		12/20/2023 12:02:07 PM		12/20/2023								231220120247238681
15	231220120207370783		12/20/2023 12:02:07 PM		12/20/2023								231220120248886282
16	231220120207370783		12/20/2023 12:02:07 PM		12/20/2023								231220120248998590
17	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120253268594
18	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120257174214
19	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120301611839
20	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120303280168
21	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120305387157
22	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120308963548
23	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120309602478
24	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120312547785
25	231220120222477999		12/20/2023 12:02:22 PM		12/29/2023								231220120314205563
26	231220120232797837		12/20/2023 12:02:32 PM		12/29/2023								231220120322898580
27	231220120236501293		12/20/2023 12:02:36 PM		12/29/2023								231220120329221484
28	231220120236570287		12/20/2023 12:02:36 PM		12/29/2023								231220120329697299
29	231220120241652868		12/20/2023 12:02:41 PM		12/29/2023
30	231220120244585174		12/20/2023 12:02:43 PM		12/29/2023
31	231220120247238681		12/20/2023 12:02:47 PM		12/20/2023
32	231220120247238681		12/20/2023 12:02:47 PM		12/20/2023
33	231220120248886282		12/20/2023 12:02:48 PM		12/20/2023
34	231220120248886282		12/20/2023 12:02:48 PM		12/20/2023
35	231220120248886282		12/20/2023 12:02:48 PM		12/20/2023
36	231220120248998590		12/20/2023 12:02:48 PM		12/20/2023
37	231220120248998590		12/20/2023 12:02:48 PM		12/20/2023
38	231220120248998590		12/20/2023 12:02:48 PM		12/20/2023
39	231220120248998590		12/20/2023 12:02:48 PM		12/20/2023
40	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
41	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
42	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
43	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
44	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
45	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
46	231220120253268594		12/20/2023 12:02:53 PM		12/29/2023
47	231220120257174214		12/20/2023 12:02:57 PM		12/20/2023
48	231220120257174214		12/20/2023 12:02:57 PM		12/20/2023
49	231220120257174214		12/20/2023 12:02:57 PM		12/20/2023
50	231220120301611839		12/20/2023 12:03:01 PM		12/20/2023
51	231220120301611839		12/20/2023 12:03:01 PM		12/20/2023
52	231220120303280168		12/20/2023 12:03:03 PM		12/20/2023
53	231220120303280168		12/20/2023 12:03:03 PM		12/20/2023
54	231220120303280168		12/20/2023 12:03:03 PM		12/20/2023
55	231220120305387157		12/20/2023 12:03:05 PM		12/20/2023
56	231220120308963548		12/20/2023 12:03:08 PM		12/29/2023
57	231220120309602478		12/20/2023 12:03:09 PM		12/20/2023
58	231220120309602478		12/20/2023 12:03:09 PM		12/20/2023
59	231220120312547785		12/20/2023 12:03:12 PM		12/20/2023
60	231220120312547785		12/20/2023 12:03:12 PM		12/20/2023
61	231220120314205563		12/20/2023 12:03:14 PM		12/20/2023
62	231220120314205563		12/20/2023 12:03:14 PM		12/20/2023
63	231220120314205563		12/20/2023 12:03:14 PM		12/20/2023
64	231220120322898580		12/20/2023 12:03:22 PM		12/29/2023
65	231220120322898580		12/20/2023 12:03:22 PM		12/29/2023
66	231220120322898580		12/20/2023 12:03:22 PM		12/29/2023
67	231220120322898580		12/20/2023 12:03:22 PM		12/29/2023
68	231220120322898580		12/20/2023 12:03:22 PM		12/29/2023
69	231220120322898580		12/20/2023 12:03:22 PM		12/29/2023
70	231220120329221484		12/20/2023 12:03:29 PM		12/20/2023
71	231220120329221484		12/20/2023 12:03:29 PM		12/20/2023
72	231220120329221484		12/20/2023 12:03:29 PM		12/20/2023
73	231220120329221484		12/20/2023 12:03:29 PM		12/20/2023
74	231220120329221484		12/20/2023 12:03:29 PM		12/20/2023
75	231220120329697299		12/20/2023 12:03:29 PM		12/20/2023
76	231220120329697299		12/20/2023 12:03:29 PM		12/20/2023
77	231220120329697299		12/20/2023 12:03:29 PM		12/20/2023
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM((FREQUENCY(MATCH($A$2:$A$77,$A$2:$A$77,0),MATCH($A$2:$A$77,$A$2:$A$77,0))>0)*1)
H5:H8	H5	=SUM((FREQUENCY(MATCH($A$2:$A$77,$A$2:$A$77,0)*(INT($C$2:$C$77)=$G5),MATCH($A$2:$A$77,$A$2:$A$77,0)*(INT($C$2:$C$77)=$G5))>0)*1)-1

thank you very much for your guidance

 

[BishopDesigns]

thank you for your reply, BishopDesigns

is it means, only column D and E 2 columns and run the macro?
it stopped here:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet2!R1C4:R77C5", Version:=8).CreatePivotTable TableDestination:= _
"Sheet2!R1C7", TableName:="PivotTable3", DefaultVersion:=8

I believe you need to change all occurrences of "Sheet2" to the name of the tab you have in your document

 

[RoryA]

In order to get a distinct count in a pivot table, you would have to load the data into the data model, not use a regular pivot table.

 

[felixstraube]

Here is the file I created.
Hope this works. I changed the format like you need it mm/dd/yyyy.
And counted the distinct values for each date by hand using remove duplicates. I get the values the formula yields

CountWithoutDuplicates.zip

Let me know if this works.

 

[kelvin_9]

Here is the file I created.
Hope this works. I changed the format like you need it mm/dd/yyyy.
And counted the distinct values for each date by hand using remove duplicates. I get the values the formula yields

CountWithoutDuplicates.zip

Let me know if this works.

thank you very much for your reply, felixstraube

anything i mess up which my fomula below is not wokring
ps: data range set as 1000, ctrl alt enter
=SUM((FREQUENCY(MATCH($A$2:$A$1000, $A$2:$A$1000, 0) * (INT($C$2:$C$1000) = $AF7), MATCH($A$2:$A$1000, $A$2:$A$1000, 0) * (INT($C$2:$C$1000) = $AF7)) > 0) * 1) - 1

thank you very much