Delete row after comparing field

bobkap

Active Member
Joined
Nov 22, 2009
Messages
313
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I have many rows of data as shown below. How do I compare each row with the same name and keep the row with the most current date? In this example I need to keep row for Mary 2/20/2020 and delete the other row for Mary. Keep Bill's 4-5-2020 and delete the rest. Keep Sue's 7-22-2020 and delete the rest.

1594255243449.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Assuming you have header in row 1 and your data starts in row 2,:
varios 08jul2020.xlsm
AB
1NAMEDATE
2Mary20/02/2020
3mary17/03/2017
4bill04/04/2019
5bill17/02/2020
6bill05/04/2020
7bill12/05/2017
8sue06/05/2017
9sue22/07/2020
10sue08/04/2015
11sue12/05/2016
12sue22/08/2019
Data


try this:
VBA Code:
Sub DeleteByDate()
  Dim a As Variant, dic As Object
  Dim i As Long, lr As Long, rng As Range
  
  lr = Range("A" & Rows.Count).End(3).Row
  a = Range("A2", Range("B" & lr)).Value2
  
  Set rng = Range("A" & lr + 1)
  Set dic = CreateObject("Scripting.Dictionary")
  dic.comparemode = vbTextCompare
  
  For i = 1 To UBound(a)
    If Not dic.exists(a(i, 1)) Then
      dic(a(i, 1)) = a(i, 2) & "|" & i + 1
    Else
      If Val(Split(dic(a(i, 1)), "|")(0)) < a(i, 2) Then
        Set rng = Union(rng, Range("A" & Val(Split(dic(a(i, 1)), "|")(1))))
        dic(a(i, 1)) = a(i, 2) & "|" & i + 1
      Else
        Set rng = Union(rng, Range("A" & i + 1))
      End If
    End If
  Next
  rng.EntireRow.Delete
End Sub
 
Upvote 0
An alternative solution is to use Power Query/get and Transform. Here is the Mcode.

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Latest Date", each List.Max([Date]), type date}})
in
    #"Grouped Rows"

Book1.xlsx
AB
1NameLatest Date
2Mary3/17/2020
3Bill4/5/2020
4Sue7/22/2020
Sheet4
 
Upvote 0
Assuming you have header in row 1 and your data starts in row 2,:
varios 08jul2020.xlsm
AB
1NAMEDATE
2Mary20/02/2020
3mary17/03/2017
4bill04/04/2019
5bill17/02/2020
6bill05/04/2020
7bill12/05/2017
8sue06/05/2017
9sue22/07/2020
10sue08/04/2015
11sue12/05/2016
12sue22/08/2019
Data


try this:
VBA Code:
Sub DeleteByDate()
  Dim a As Variant, dic As Object
  Dim i As Long, lr As Long, rng As Range
 
  lr = Range("A" & Rows.Count).End(3).Row
  a = Range("A2", Range("B" & lr)).Value2
 
  Set rng = Range("A" & lr + 1)
  Set dic = CreateObject("Scripting.Dictionary")
  dic.comparemode = vbTextCompare
 
  For i = 1 To UBound(a)
    If Not dic.exists(a(i, 1)) Then
      dic(a(i, 1)) = a(i, 2) & "|" & i + 1
    Else
      If Val(Split(dic(a(i, 1)), "|")(0)) < a(i, 2) Then
        Set rng = Union(rng, Range("A" & Val(Split(dic(a(i, 1)), "|")(1))))
        dic(a(i, 1)) = a(i, 2) & "|" & i + 1
      Else
        Set rng = Union(rng, Range("A" & i + 1))
      End If
    End If
  Next
  rng.EntireRow.Delete
End Sub

AWESOME!!! Many thanks. I know some very basic macro commands, but none of these. Not to push my luck or sound ungrateful, if you ever have a moment I would be forever grateful if you might explain what the rows that start with "dic" means. I assume dic stands for dictionary? I have long wanted to understand how the dictionary function works but have never been able to figure it out.
 
Upvote 0
An alternative solution is to use Power Query/get and Transform. Here is the Mcode.

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Latest Date", each List.Max([Date]), type date}})
in
    #"Grouped Rows"

Book1.xlsx
AB
1NameLatest Date
2Mary3/17/2020
3Bill4/5/2020
4Sue7/22/2020
Sheet4

Thanks! I just cannot seem to grasp Power Query and Mcode.
 
Upvote 0
Look at the link in my signature for more information.

The Steps I took

Highlight your table in Excel.
Click on Data Tab-->Get & Transform or Power Query depending on your version of excel.
From Range or Table

In the PQ Editor.
Transform Tab-->Group-->Group on Name
New Column = Latest Date
Action = Max
Column is Date

On Home Tab Close and Load

Note: Before all of the above in row 1. I named column A as Name and column B as Date.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top