Looking to remove duplicates based on a date criteria

[DJMCCOURT]

Hi There,

I am looking for a macro to sort a whole sheet on one column in order A-Z then to remove lines if data in that column has duplicate information in it but then i would like it to remove the later entry or entries based on a date.

So the column i want it to sort on is Column D "17 Digit Chassis No" then leave only the earliest dated record based on the date in Column H "Purch/Add Date".

I have tried to decipher other macros on here to suit my criteria but i have failed miserably each time, but you cant blame a guy for trying!!

Could anyone assist please?

SAMPLE DATA BELOW (Cant seem to get it into a table on the forum, im obviously missing something.

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Batch No[/TD]
[TD="width: 64"]Make[/TD]
[TD="width: 64"]Model[/TD]
[TD="width: 64"]17 Digit Chassis No[/TD]
[TD="width: 64"]Registration Date
(DD/MM/YYYY)[/TD]
[TD="width: 64"]Stock Number[/TD]
[TD="width: 64"]Branch[/TD]
[TD="width: 64"]Purch/Add Date[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036106[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208692[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036125[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208693[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036207[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208708[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036210[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208684[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036245[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208690[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036411[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208689[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036412[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208705[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036435[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208699[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036537[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208698[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036602[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208685[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036616[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208707[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036626[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208704[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036635[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208706[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036653[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208697[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036742[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208703[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036759[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208753[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036812[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208741[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036844[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208686[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036863[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208754[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036864[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208747[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036887[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208701[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036995[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208696[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037055[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208702[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037060[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208695[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037061[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208700[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037070[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208742[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037093[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208761[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037097[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208764[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037119[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208687[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037258[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208680[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037277[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208743[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037341[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208745[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037359[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208734[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037370[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208752[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037592[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208677[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037638[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208759[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037679[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208736[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037751[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208756[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037774[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208739[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037819[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208762[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037839[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208788[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037869[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208740[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037890[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208749[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037926[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]208744[/TD]
[TD]AU[/TD]
[TD]23/04/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036995[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]8415[/TD]
[TD]FE[/TD]
[TD]28/06/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037060[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]8414[/TD]
[TD]FE[/TD]
[TD]28/06/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037341[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]3190[/TD]
[TD]FS[/TD]
[TD]28/06/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6037097[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]4390[/TD]
[TD]GB[/TD]
[TD]28/06/2019[/TD]
[/TR]
[TR]
[TD]VX65[/TD]
[TD]Vauxhall[/TD]
[TD]Vauxhall Adam Hatchback Special Ed 1.2i Energised 3dr[/TD]
[TD]W0V0MAP08K6036602[/TD]
[TD]30/05/2019[/TD]
[TD="align: right"]12601[/TD]
[TD]GU[/TD]
[TD]28/06/2019[/TD]
[/TR]
</tbody>[/TABLE]



Thanks
Dave

 

[DJMCCOURT]

My apologies, i didnt see you had joined the thread Sandy666

Appreciate your help.

 

[DJMCCOURT]

are you talking about post#5 ?

Yes sorry sandy666 > yours is spot on, apologies again for not spotting it was someone else posting a response.

Cheers
Dave

 

[sandy666]

the post#5 is very small and it is difficult to notice

so try PowerQuery aka Get&Transform

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Batch No", type text}, {"Make", type text}, {"Model", type text}, {"17 Digit Chassis No", type text}, {"Registration Date ", type date}, {"Stock Number", Int64.Type}, {"Branch", type text}, {"Purch/Add Date", type date}}),
    SortDate = Table.Sort(Type,{{"Purch/Add Date", Order.Descending}}),
    DeDup = Table.Distinct(SortDate, {"17 Digit Chassis No"}),
    SortChassis = Table.Sort(DeDup,{{"17 Digit Chassis No", Order.Ascending}})
in
    SortChassis[/SIZE]

and file with your example

 

[DJMCCOURT]

are you talking about post#5 ?

Yes sandy66 that is perfect, could you provide me with the macro you used please?
Cheers
Dave

 

[sandy666]

refresh thread and read above your last post

 

[mumps]

@DJMCCOURT
I think you may have been referring to sandy666's response.

Try this version of the macro:

Sub DeleteDups()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet, RngList As Object, item As Variant, fRow As Long, lRows As Long, rCount As Long
    Set ws = Sheets("BAT")
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("D1:D" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("H1:H" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:H" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In ws.Range("D2", ws.Range("D" & ws.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Rng.Row
        End If
    Next
    MsgBox RngList.Count
    For Each item In RngList
        With ws.Cells(1).CurrentRegion
            .AutoFilter 4, item
            rCount = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
            fRow = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
            lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            If lRow > fRow And rCount > 1 Then
                ws.Rows(fRow + 1 & ":" & lRow).EntireRow.Delete
            End If
        End With
    Next item
    ws.Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[DJMCCOURT]

the post#5 is very small and it is difficult to notice

so try PowerQuery aka Get&Transform

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Batch No", type text}, {"Make", type text}, {"Model", type text}, {"17 Digit Chassis No", type text}, {"Registration Date ", type date}, {"Stock Number", Int64.Type}, {"Branch", type text}, {"Purch/Add Date", type date}}),
    SortDate = Table.Sort(Type,{{"Purch/Add Date", Order.Descending}}),
    DeDup = Table.Distinct(SortDate, {"17 Digit Chassis No"}),
    SortChassis = Table.Sort(DeDup,{{"17 Digit Chassis No", Order.Ascending}})
in
    SortChassis[/SIZE]

and file with your example

Hi sandy666, being a novice to this, where do i paste that code? into a new module or.....

Also when i download the file, that sheet doesnt have the macro in it that i can see? Should it, or am i missing something here?

Thanks
Dave

 

[sandy666]

this is not a vba but PowerQuery M-code

in the file
Ribbon - Data - Show Queries - double click on table on the right side - on the ribbon from new window find Advanced Editor then you'll see M-code

easier way
select your whole range with data (without empty rows!) and from Data tab choose From Table, first copy code from the post and replace with code in Advanced Editor
make sure the name of your source table is the same as in the code

you did not say what version of excel you use so it's hard to find details or even if it works for you

 

[sandy666]

I've to go but if you've a problem with M-code and PowerQuery you can always use mumps's vba (with all due respect mumps )

 

[DJMCCOURT]

refresh thread and read above your last post

Sorry, i only have office 2013 so dont have that option im afraid.