Problems with matching duplicates and spotting differences

XLmuppet

New Member
Joined
Aug 13, 2014
Messages
10
I hope someone can assist? I have a table with 2500 rows relating to people who have a unique reference number. Some of these users are duplicated by reference number but have other fields in the table (start date in this case) which are not duplicates. I would like to have a formula to group the duplicates together and then mark them (via txt in a new column) if their start dates differ.

i.e in this example

492495​
09/10/2017 15:45​
492495​
26/02/2018 19:12​
492495​
01/03/2018 15:56​
492495​
05/04/2018 15:11​
669250​
09/05/2017 00:57​
669250​
15/04/2020 17:48​
669250​
15/04/2020 17:48​

I would like to indicate that 492495 has all different dates but 669250 has two matching dates.

Any help gratefully received.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,
Would this work?
Book1
ABC
1refdateduplicate
249249509/10/2017 15:45FALSE
349249526/02/2018 19:12FALSE
449249501/03/2018 15:56FALSE
549249505/04/2018 15:11FALSE
666925009/05/2017 00:57FALSE
766925015/04/2020 17:48TRUE
866925015/04/2020 17:48TRUE
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2)>1
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
VBA approach. Assuming data is in columns A and B, try:
VBA Code:
Sub XLmuppet()
    Application.ScreenUpdating = False
    Dim key As Variant, rng As Range, rngList As Object, rngList2 As Object, Val As String
    Cells(1, 1).Sort Key1:=Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Set rngList = CreateObject("Scripting.Dictionary")
    Set rngList2 = CreateObject("Scripting.Dictionary")
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Not rngList.Exists(rng.Value) Then
            rngList.Add rng.Value, Nothing
        End If
    Next
    For Each key In rngList
        With Cells(1, 1)
            .CurrentRegion.AutoFilter 1, key
            For Each rng In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Val = rng.Value
                If Not rngList2.Exists(Val) Then
                    rngList2.Add key:=Val, Item:=rng.Row
                Else
                    rng.Offset(, 1) = "X"
                    Cells(rngList2(Val), 3) = "X"
                End If
            Next rng
        End With
    Next key
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top