how to count duplicates values in a day

AYSHANA · Apr 3, 2023

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

1

6

%XM

123

3/1/2023 21:53

BGRPS WITH %XM

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 · Apr 4, 2023

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

PHP:

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 · Apr 4, 2023

Hi. You can try it with:

PHP:

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 · Apr 4, 2023

Mario_R said:

Hi. You can try it with:

PHP:

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

4

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 · Apr 4, 2023

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?...