Data transferring

[siddiquera]

I am having an issue with with sorting files and deleting the duplicates. I need to manipulate the data in a way so it only takes the most recent. Col. B is the date format and the data in A is what I need. Consider the first two rows, I
only need the one in bold and italics.
A B C
12686 19731231 ACFC
90399 20041005 ACFC
79998 19951019 AFCB
93224 20100107 AFCB
81488 19980331 ALLB
91731 20070131 ALLB
86566 19990121 ALNC
86814 19990423 AMNB
88719 20001026 AMRB
88323 20000621 ANCX
90274 20040719 ANCX
81471 19950327 ANNB
85453 19971001 ANNB
15070 19830901 AROW
15078 19721214 AROW

Thank You

 

[baitmaster]

welcome to the board

you'll get better responses if you can be more specific about what you are trying to do. You've mentioned "files" (workbooks in a folder?), but show [what looks like] 3 columns of data as an example

Is this just 3 columns of data, and you want to delete any rows where column C is duplicated, and column B is not the largest value for that duplicate?

A macro could be written to do this, but please confirm that is what you want done. Would probably also need to know about anything else on the sheet

 

[siddiquera]

Yes. You nailed it.

 

[Comfy]

Here you go:

Sub Dupes()
Dim i As Long, j As Long, LR As Long, NewLR As Long
Dim Unique As Variant
 
LR = ActiveSheet.UsedRange.Rows.Count
Range(Cells(2, 3), Cells(LR, 3)).Copy Cells(LR + 3, 4)
Range(Cells(LR + 3, 4), Cells(LR + 3, 4).End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    Cells(LR + 3, 5).FormulaArray = "=MAX(IF(R2C3:R16C3=RC[-1],R2C2:R16C2))"
    Cells(LR + 3, 5).AutoFill Destination:=Range(Cells(LR + 3, 5), Cells(ActiveSheet.UsedRange.Rows.Count, 5))
Unique = Range(Cells(LR + 3, 4), Cells(LR + 3, 5).End(xlDown))
Range(Cells(LR + 3, 4), Cells(LR + 3, 5).End(xlDown)).ClearContents
    For i = LBound(Unique) To UBound(Unique)
        NewLR = ActiveSheet.UsedRange.Rows.Count
        Range(Cells(1, 1), Cells(LR, 3)).AutoFilter Field:=3, Criteria1:=Unique(i, 1)
        For j = NewLR To 2 Step -1
            If Rows(j).Hidden = False Then
               If Not Cells(j, 2) = Unique(i, 2) Then Rows(j).EntireRow.Delete
            End If
        Next j
    Next i
Cells(1, 1).AutoFilter
End Sub

I have made the assumption that your sheet looks exactly as your example, so this isn't very dynamic but could be changed if needed.

 

[Comfy]

Argh!

Almost worked.

Will post updated vers after changing the MAxIF function range...... Sorry.

Sub Dupes()
Dim i As Long, j As Long, LR As Long, NewLR As Long
Dim Unique As Variant
 
LR = ActiveSheet.UsedRange.Rows.Count
Range(Cells(2, 3), Cells(LR, 3)).Copy Cells(LR + 3, 4)
Range(Cells(LR + 3, 4), Cells(LR + 3, 4).End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    Cells(LR + 3, 5).FormulaArray = "=MAX(IF(R2C3:R" & LR & "C3=RC[-1],R2C2:R" & LR & "C2))"
    Cells(LR + 3, 5).AutoFill Destination:=Range(Cells(LR + 3, 5), Cells(ActiveSheet.UsedRange.Rows.Count, 5))
Unique = Range(Cells(LR + 3, 4), Cells(LR + 3, 5).End(xlDown))
Range(Cells(LR + 3, 4), Cells(LR + 3, 5).End(xlDown)).ClearContents
    For i = LBound(Unique) To UBound(Unique)
        NewLR = ActiveSheet.UsedRange.Rows.Count
        Range(Cells(1, 1), Cells(LR, 3)).AutoFilter Field:=3, Criteria1:=Unique(i, 1)
        For j = NewLR To 2 Step -1
            If Rows(j).Hidden = False Then
               If Not Cells(j, 2) = Unique(i, 2) Then Rows(j).EntireRow.Delete
            End If
        Next j
    Next i
Cells(1, 1).AutoFilter
End Sub

There you go

 

[siddiquera]

I ran the macro function but it deleted all of the data. Unless I am doing something wrong.

 

[Comfy]

The macro assumes your data is in columns A B and C.

If not or somehow you actual sheet is different to your example, it will not work.