how to count duplicates values in a day

[AYSHANA]

good day!
i would like to count (how many duplicate pt number I have in a day based on (orders)

thank you.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Orders	PT Number	Result Date Time				HOW MANY DUPLICATE PT NUMBER I HAVE IN A DAY BASED ON ORDERS
2	BGRPS	11	3/1/2023 22:56							2023
3	BGRPS	12	3/1/2023 13:04
4	BGRP	12333	3/1/2023 20:24				ROLE	JAN	FEB	MAR	APR	MAY
5	BGRPS	123	3/1/2023 19:21				BGRPS WITH BGRPS	0	0	1
6	%XM	123	3/1/2023 21:53				BGRPS WITH %XM	0	0	2
7	BGRPS	3455	3/1/2023 16:24
8	BGRPS	5556	3/1/2023 21:22
9	BGRP	6677	3/1/2023 20:49
10	BGRPS	777	3/9/2023 16:57
11	BGRPS	234	3/9/2023 3:04
12	%XM	234	3/9/2023 20:54
13	BGRPS	666	3/10/2023 20:32
14	BGRPS	6655	3/10/2023 10:22
15	BGRPS	543	3/10/2023 6:36
16	BGRPS	245	3/10/2023 7:40
17	BGRPS	245	3/10/2023 6:43
18	BGRP	3445	3/10/2023 23:03
19	%XM	4556	3/10/2023 8:51
20	%XM	5554	3/1/2023 13:50
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	duplicates	text	NO

 

[bebo021999]

Use UDF.
Right click tab's name, ViewCode then Insert/Module, paste below code into. Save file as .xlsm

Format: =dup(DataRange,month,year,1 or 2)
In H5:
=dup($A$2:$C$20,H$4,$J$2,1)
H6:
=dup($A$2:$C$20,H$4,$J$2,2)
Copy both accross

Option Explicit
Function Dup(ByVal rng As Range, month As Range, year As Range, n As Integer)
Dim StartD, EndD, i&, rng2, dic1 As Object, dic2 As Object, key1, key2, c1&, c2&
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
StartD = DateValue("01-" & month & "-" & year)
EndD = WorksheetFunction.EoMonth(StartD, 0)
rng2 = rng.Value
For i = 1 To UBound(rng2)
    If rng2(i, 3) >= StartD And rng2(i, 3) <= EndD Then
        If rng2(i, 1) = "BGRPS" Then
           If Not dic1.exists(rng2(i, 2)) Then
                dic1.Add rng2(i, 2), 1
            Else
                dic1(rng2(i, 2)) = dic1(rng2(i, 2)) + 1
            End If
        End If
        If rng2(i, 1) = "%XM" Then
           If Not dic2.exists(rng2(i, 2)) Then
                dic2.Add rng2(i, 2), 1
            Else
                dic2(rng2(i, 2)) = dic2(rng2(i, 2)) + 1
            End If
        End If
    End If
Next
For Each key1 In dic1.keys
    If dic1(key1) > 1 Then
        c1 = c1 + 1
    Else
        For Each key2 In dic2.keys
            If key1 = key2 Then
                c2 = c2 + 1
                Exit For
            End If
        Next
    End If
Next
Dup = IIf(n = 1, c1, c2)
End Function

 

[Mario_R]

Hi. You can try it with:

Sub Macro1()
Dim a, C As Range, j%, i&
ReDim a(1 To 2, 1 To Range("G4", [g4].End(xlToRight)).Columns.Count - 1)
For i = 2 To [a1].End(xlDown).Row - 1
  Set C = Cells(i, 1)
  If C(1, 2) = C(2, 2) Then
    If C = C(2) Then j = 1 Else j = 2
    a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
    i = 1 + i
  End If
Next
[h5].Resize(2, UBound(a, 2)) = a
End Sub

 

[AYSHANA]

Hi. You can try it with:

Sub Macro1()
Dim a, C As Range, j%, i&
ReDim a(1 To 2, 1 To Range("G4", [g4].End(xlToRight)).Columns.Count - 1)
For i = 2 To [a1].End(xlDown).Row - 1
  Set C = Cells(i, 1)
  If C(1, 2) = C(2, 2) Then
    If C = C(2) Then j = 1 Else j = 2
    a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
    i = 1 + i
  End If
Next
[h5].Resize(2, UBound(a, 2)) = a
End Sub

it is working

but i didnt know from where the count in dec (2332) is coming from, it should be blank


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a, C As Range, j%, i&
ReDim a(1 To 2, 1 To Range("G4", [g4].End(xlToRight)).Columns.Count - 1)
For i = 2 To [a1].End(xlDown).Row - 1
Set C = Cells(i, 1)
If C(1, 2) = C(2, 2) Then
If C = C(2) Then j = 1 Else j = 2
a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
i = 1 + i
End If
Next
[h5].Resize(2, UBound(a, 2)) = a
End Sub

BGRPS STATISTICS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Orders	PT Number	Result Date Time
2	BGRPS	114815747	01/03/23 22:56							2023
3	BGRPS	2544687	01/03/23 13:04
4	BGRP	2653362	01/03/23 20:24				ROLE	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	TOTAL
5	BGRPS	2677296	01/03/23 19:21				BGRPS WITH BGRPS			15									2332
6	%XM	2677296	01/03/23 21:53				BGRPS WITH %XM			22	1
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	='raw daily'!B2
B2:B6	B2	='raw daily'!E2
C2:C6	C2	='raw daily'!I2

 

[Mario_R]

In the cells of the columns A:C you can see (top left) some red "dots", right?...

I have the 'suspicion' that these cells do not contain numbers or dates but "texts" that look like numbers or dates respectively.

To check: Can you upload a small sample of your workbook to the Forum to see -exactly- what you have there?...

 

[AYSHANA]

BGRPS STATISTICS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Orders	PT Number	Result Date Time
2	BGRPS	114815747	01/03/23 22:56							2023
3	BGRPS	2544687	01/03/23 13:04
4	BGRP	2653362	01/03/23 20:24				ROLE	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
5	BGRPS	2677296	01/03/23 19:21				BGRPS WITH BGRPS			16									1813
6	%XM	2677296	01/03/23 21:53				BGRPS WITH %XM			23
7	BGRPS	37619276	01/03/23 16:24
8	BGRPS	48654639	01/03/23 21:22
9	BGRP	52734471	01/03/23 20:49
10	BGRPS	52853039	01/03/23 10:36
11	BGRPS	5686415	01/03/23 19:46
12	%XM	69958360	01/03/23 19:36
Sheet1

Cell Formulas
Range	Formula
A2:A12	A2	='raw daily'!B2
B2:B12	B2	='raw daily'!E2
C2:C12	C2	='raw daily'!I2

 

[AYSHANA]

yes it is a text

 

[Fluff]

In the cells of the columns A:C you can see (top left) some red "dots", right?...

Those are the way that XL2BB signifies the cell contains a formula.

 

[Mario_R]

Those are the way that XL2BB signifies the cell contains a formula.

I understand, thank you.
What happens is that the data referenced by the formulas of the 'daily raw' sheet are those that are in text.

 

[AYSHANA]

I understand, thank you.
What happens is that the data referenced by the formulas of the 'daily raw' sheet are those that are in text.

Yes