MAx Date multiple criteria

[nahmja]

Hi I am looking for some one help..my data is as below.

DATE	Dept	BRANCH	Transaction	Terminal ID
8-Oct-15	TT	MCT	IN	21666446
8-Oct-15	TT	NIZ	OUT	21666434
8-Oct-14	TT	MCT	OUT	21666446
26-Mar-11	TT	MCT	OUT	21666760
26-Mar-11	TT	MCT	OUT	24600600
27-Mar-14	TT	MCT	OUT	24600600
27-Mar-11	TT	MCT	IN	21666471

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Required result would be as below.

DATE	Dept	BRANCH	Transaction	Terminal ID
8-Oct-15	TT	NIZ	OUT	21666434
8-Oct-15	TT	MCT	IN	21666446
27-Mar-14	TT	MCT	OUT	24600600
27-Mar-11	TT	MCT	IN	21666471
26-Mar-11	TT	MCT	OUT	21666760

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[MickG]

Try this:-
Results start "H1".

[COLOR="Navy"]Sub[/COLOR] MG08Oct24
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(Ray, 1)
[COLOR="Navy"]If[/COLOR] Not .Exists(Ray(Rw, 5)) [COLOR="Navy"]Then[/COLOR]
n = n + 1
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
        nray(n, Ac) = Ray(Rw, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
.Add Ray(Rw, 5), n
[COLOR="Navy"]Else[/COLOR]
    [COLOR="Navy"]If[/COLOR] Ray(Rw, 1) > nray(.Item(Ray(Rw, 5)), 1) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
            nray(.Item(Ray(Rw, 5)), Ac) = Ray(Rw, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw


[COLOR="Navy"]With[/COLOR] Range("H1").Resize(n, 5)
    .Value = nray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nahmja]

Thanks Mick,

your quick response, basically i have two suppurate files, how i can use this ?

 

[MickG]

Do you mean 2 separate Workbooks , and you want the results in the seconds workbook or 2 separate sheets and you want the results on a separate sheet.

 

[nahmja]

Second workbook.

Do you mean 2 separate Workbooks , and you want the results in the seconds workbook or 2 separate sheets and you want the results on a separate sheet.

 

[mathematician]

Try this chunk of code also. Slightly edited from macro recorder code. Remember to backup your data before attempting this

Sub SortRemoveDup()
    ' Figure out the current last row with data assuming num of columns dont change
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Sort by date newest to oldest "AND THEN" by Terminal ID smallest to largest
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ' Remove duplicates based on Terminal ID
    ActiveSheet.Range("$A$1:$E$" & LastRow).RemoveDuplicates Columns:=5, Header:=xlYes
End Sub

 

[mathematician]

Try this chunk of code also. Slightly edited from macro recorder code. Remember to backup your data before attempting this

Sub SortRemoveDup()
    ' Figure out the current last row with data assuming num of columns dont change
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Sort by date newest to oldest "AND THEN" by Terminal ID smallest to largest
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ' Remove duplicates based on Terminal ID
    ActiveSheet.Range("$A$1:$E$" & LastRow).RemoveDuplicates Columns:=5, Header:=xlYes
End Sub

Ignore this and use the one below

Sub SortRemoveDup()
    ' Figure out the current last row with data assuming num of columns dont change
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row


    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    
    ' Sort by date newest to oldest "AND THEN" by Terminal ID smallest to largest
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("$A$1:$A$" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("$E$1:$E$" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:E" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Remove duplicates based on Terminal ID
    ActiveSheet.Range("$A$1:$E$" & LastRow).RemoveDuplicates Columns:=5, Header:=xlYes


End Sub

 

[MickG]

Run the Code within the Data Workbook/Data sheet ensuring the Second WorkBook is Open.
Change WKbook details as shown in code.

[COLOR="Navy"]Sub[/COLOR] MG11Oct56
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(Ray, 1)
[COLOR="Navy"]If[/COLOR] Not .Exists(Ray(Rw, 5)) [COLOR="Navy"]Then[/COLOR]
n = n + 1
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
        nray(n, Ac) = Ray(Rw, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
.Add Ray(Rw, 5), n
[COLOR="Navy"]Else[/COLOR]
    [COLOR="Navy"]If[/COLOR] Ray(Rw, 1) > nray(.Item(Ray(Rw, 5)), 1) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
            nray(.Item(Ray(Rw, 5)), Ac) = Ray(Rw, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw


'[COLOR="Green"][B]Change workbook Details to suit in line below[/B][/COLOR]
[COLOR="Navy"]With[/COLOR] Workbooks("Second_Wkbk.xlsm").Sheets("Sheet1").Range("A1").Resize(n, 5)
    .Value = nray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick