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

 

[Mario_R]

I show you an adaptation:

Sub Macro2()
Dim a, C As Range, j%, i&, Tmp
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
    Tmp = 0 + Split(C(, 3), "/")(0)
    a(j, Tmp) = 1 + a(j, Tmp)
    i = 1 + i
  End If
Next
[h5].Resize(2, UBound(a, 2)) = a
End Sub

 

[AYSHANA]

I show you an adaptation:

Sub Macro2()
Dim a, C As Range, j%, i&, Tmp
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
    Tmp = 0 + Split(C(, 3), "/")(0)
    a(j, Tmp) = 1 + a(j, Tmp)
    i = 1 + i
  End If
Next
[h5].Resize(2, UBound(a, 2)) = a
End Sub

it is showing error time 13 type mismatch

 

[Mario_R]

As I mentioned to you before: I have to see your workbook.

 

[AYSHANA]

As I mentioned to you before: I have to see your workbook.

BGRPS STATISTICS.xlsm
	B			E				I
1	Orders			PT Number				Result Date Time
2	BGRPS			114815747				3/1/2023 22:56
3	BGRPS			2544687				3/1/2023 13:04
4	BGRP			2653362				3/1/2023 20:24
5	BGRPS			2677296				3/1/2023 19:21
6	%XM			2677296				3/1/2023 21:53
7	BGRPS			37619276				3/1/2023 16:24
8	BGRPS			48654639				3/1/2023 21:22
9	BGRP			52734471				3/1/2023 20:49
10	BGRPS			52853039				3/1/2023 10:36
11	BGRPS			5686415				3/1/2023 19:46
12	%XM			69958360				3/1/2023 19:36
13	BGRPS			86999290				3/1/2023 14:31
14	BGRPS			920448956				3/1/2023 20:50
15	BGRPS			920835843				3/1/2023 13:10
16	BGRPS			921902904				3/1/2023 12:56
17	BGRPS			922736988				3/1/2023 13:03
18	BGRP			923613613				3/1/2023 13:42
19	%XM			923704836				3/1/2023 13:34
20	%XM			923724902				3/1/2023 13:50
21	BGRPS			923813987				3/1/2023 13:06
22	BGRPS			923837124				3/1/2023 11:20
23	IAT			923837124				3/1/2023 0:53
24	BGRPS			923859710				3/1/2023 14:59
25	%XM			924084883				3/1/2023 15:50
26	BGRPS			924093752				3/1/2023 15:48
27	BGRPS			924094574				3/1/2023 15:07
28	BGRPS			924096095				3/1/2023 12:50
29	%XM			924105113				3/1/2023 22:09
30	BGRPS			924106781				3/1/2023 12:32
31	BGRPS			924106892				3/1/2023 19:37
32	%XM			924109777				3/1/2023 11:53
33	BGRPS			924111083				3/1/2023 22:35
34	BGRPS			10480609				3/2/2023 16:34
35	BGRPS			10522425				3/2/2023 3:56
36	%XM			1106210				3/2/2023 7:46
37	BGRPS			114815747				3/2/2023 1:05
38	%XM			2651351				3/2/2023 13:07
39	%XM			2677296				3/2/2023 5:25
raw daily

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			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
13	BGRPS	86999290	01/03/23 14:31
14	BGRPS	920448956	01/03/23 20:50
15	BGRPS	920835843	01/03/23 13:10
16	BGRPS	921902904	01/03/23 12:56
17	BGRPS	922736988	01/03/23 13:03
18	BGRP	923613613	01/03/23 13:42
Sheet1

Cell Formulas
Range	Formula
A2:A18	A2	='raw daily'!B2
B2:B18	B2	='raw daily'!E2
C2:C18	C2	='raw daily'!I2

 

[bebo021999]

@AYSHANA, have you tried my UDF in #2?

 

[Mario_R]

I made the macro much more "robust", ie:

Text_1.xlsm
\_____________________/​

a) Start by copying the data from the 'raw daily' sheet.

b) I don't know why you commented before that you had 'text'. But in the dates column you have -actually- dates: so in that sense I went back to version 1 of the macro.

c) When I see more data, I notice that you have not only BGRPS and %XM but also IAT, for example.

d) This forces me to modify the logic of the macro.

e) Finally I incorporated a detail that I had not contemplated: the year that is in cell J2.

Would you comment?...

Sub Macro3()
Dim a, C As Range, j%, i&, Tmp
Application.ScreenUpdating = False
Rem ------------------->
Sheets("Sheet1").[a1].CurrentRegion.Offset(1).Delete xlShiftUp
Rem ------------------->
With Sheets("raw daily")
  With .Range("B1", .Range("B1").End(xlDown))
    Union(.Cells, .Columns(4), .Columns(8)).Copy
    Sheets("Sheet1").[a1].PasteSpecial xlPasteValuesAndNumberFormats
  End With
End With
Rem ------------------->
ReDim a(1 To 2, 1 To 12)
For i = 2 To [a1].End(xlDown).Row - 1
  Set C = Cells(i, 1)
  If C(1, 2) = C(2, 2) And Year(C(1, 3)) = Range("J2") Then
    Select Case True
      Case C = "BGRPS" And C(2) = "BGRPS"
        j = 1
        a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
        i = 1 + i
      Case C = "BGRPS" And C(2) = "%XM"
        j = 2
        a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
        i = 1 + i
    End Select
  End If
Next
Rem ------------------->
[h5].Resize(2, UBound(a, 2)) = a: Range("A2").Select
End Sub

 

[AYSHANA]

thank you

 

[AYSHANA]

i have added another role which is (%xm with %xm) i also extended the range but still it showing subscript out of range

Sub Macro3()
Dim a, C As Range, j%, i&, Tmp
Application.ScreenUpdating = False
Rem ------------------->
Sheets("DOUBLE").[a1].CurrentRegion.Offset(1).Delete xlShiftUp
Rem ------------------->
With Sheets("raw daily")
  With .Range("B1", .Range("B1").End(xlDown))
    Union(.Cells, .Columns(4), .Columns(8)).Copy
    Sheets("DOUBLE").[a1].PasteSpecial xlPasteValuesAndNumberFormats
  End With
End With
Rem ------------------->
ReDim a(1 To 3, 1 To 12)
For i = 2 To [a1].End(xlDown).Row - 1
  Set C = Cells(i, 1)
  If C(1, 2) = C(2, 2) And Year(C(1, 3)) = Range("J2") Then
    Select Case True
      Case C = "BGRPS" And C(2) = "BGRPS"
        j = 1
        a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
        i = 1 + i
      Case C = "BGRPS" And C(2) = "%XM"
        j = 2
        a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
        i = 1 + i
        
        Case C = "%XM" And C(2) = "%XM"
        j = 3
        a(j, Month(C(, 3))) = 1 + a(j, Month(C(, 3)))
        i = 1 + i
            
    End Select
  End If
Next
Rem ------------------->
[h5].Resize(3, UBound(a, 3)) = a: Range("A2").Select
End Sub

 

[Mario_R]

Try putting your workbook on an external server (as I did) to see the same as you.