obtain distinct rows based on three columns

[gint32]

Hi People,

Happy 2017....

Can anyone help me to show only distinct rows based on 3 columns values, and also delete the rest(e.g duplicates)

please see below example columns (A,B,C)....[The real data columns that I need to work with(distinct) is actually not a,b,& c but "G,H & M". ]

The test example below would reduce to only show one value of each and delete the remaining (full Rows), no matter how long they are eg (A<>Z)

Column A B C D

08:30 - 11:30	Toms02	Nodups,01
08:30 - 11:30	Toms02	Nodups,01
08:30 - 11:30	Toms02	Nodups,01
08:30 - 11:30	Toms05	Nodups,04
08:30 - 11:30	Toms06	Nodups,05
08:30 - 11:30	Toms06	Nodups,05
08:30 - 11:30	Toms06	Nodups,05
08:30 - 11:30	Toms09	Nodups,08
08:30 - 11:30	Toms10	Nodups,09
08:30 - 11:30	Toms11	Nodups,10
08:30 - 11:30	Toms11	Nodups,10
08:30 - 11:30	Toms11	Nodups,10
08:30 - 11:30	Toms11	Nodups,10
08:30 - 11:30	Toms11	Nodups,10
08:30 - 11:30	Toms16	Nodups,15
16:15 - 17:15	Toms17	Nodups,16
13:30 - 15:30	Toms02	Nodups,01

'<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
reduced to the below

08:30 - 11:30	Toms02	Nodups, 01
08:30 - 11:30	Toms05	Nodups, 04
08:30 - 11:30	Toms06	Nodups, 05
08:30 - 11:30	Toms09	Nodups, 08
08:30 - 11:30	Toms10	Nodups, 09
08:30 - 11:30	Toms11	Nodups, 10
08:30 - 11:30	Toms16	Nodups, 15
16:15 - 17:15	Toms17	Nodups, 16
13:30 - 15:30	Toms02	Nodups, 01

Any help would be great!
Thanks in advance

I thought I could amend the following array ,that I found whilst surfing the net to suit, but I don't really understand or know enough of what its doing to amend it on my own:----

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20)+($A$2:$A$20=""), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$7)+($B$2:$B$7=""), 0))), INDEX($C$2:$C$12, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$12)+($C$2:$C$12=""), 0))), "")

or

=IF(COUNTIFS($A$1:$A2, $A2, $B$1:$B2, $B2, $C$1:$C2, $C2 )=1, "Distinct row", "")

then use

VBA

Option Explicit

Sub deleteblanksrows()
'
' deleteblanksrows Macro
'

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the D column in this example
            With .Cells(Lrow, "D")

                If Not IsError(.Value) Then


            If .Value = "" Then .EntireRow.Delete
            
            'This will delete each row if the cell is empty or have a formula that evaluates to ""
                    'If .Value = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column D, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

[Paul_Hossler]

Maybe this

Option Explicit

Sub DeDup()
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlNo
End Sub

[gint32]

Works a treat....thanks to all