duplicate removal.

mrxwanttobe

New Member
Joined
Apr 27, 2018
Messages
23
Office Version
  1. 2007
Platform
  1. Windows
Hello all. Please note first column is a customer number and last column is a date. I want to remove all the duplicates but only keep the row with the most recent date so in my example only the row with the 01/11/20 date. Is this possible? Than you all in advance.
1962​
40​
40​
7/8/16 22:29​
1962​
60​
100​
8/26/16 21:48​
1962​
100​
100​
10/13/17 22:32​
1962​
100​
100​
1/11/20 22:32​
1962​
40​
40​
11/2/18 22:51​
1962​
40​
40​
4/13/19 22:56​
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source, {{"DT", type datetime}}, "en-US"),
    Filter = Table.SelectRows(DT, let latest = List.Max(DT[DT]) in each [DT] = latest)
in
    Filter
IDValue1Value2DTIDValue1Value2DT
1962404007/08/2016 22:29196210010001/11/2020 22:32
1962601008/26/16 21:48
196210010010/13/17 22:32
196210010001/11/2020 22:32
1962404011/02/2018 22:51
196240404/13/19 22:56
 
Upvote 0
hi. I am very new to Excel and have never done VBA. Is that what this is and possibly can you tell me how to copy, paste and run this code on my sheet? Once again, thank you.
 
Upvote 0
I also just found Power Query is for Excel 2010 and later and I have 2007. any other options?
 
Upvote 0
so update your Account details about excel version and OS (less troubles and wasted posts)
and maybe someone else will give you a solution
 
Upvote 0
@sandy666 But this power query works only on the latest date. I think you have to deal with the IDs in column 1. I mean if you put different IDs you will get the same result (only the latest date for any ID) and I think the OP means to have the latest date for each ID separately,
 
Upvote 0
Found my Office 10 and will install and give the power query a try. My sheet has over 20000 rows and 100's of customer id's in column "A". Will this query not work? Yasser Khalil is correct. I want to delete all the duplicates with the oldest dates so the only row left has the latest date.
 
Upvote 0
Remember to update your profile (Account details) about Excel version and OS - as I said : less troubles and wasted posts
and I don't know what is Office 10, but I assume you mean Office 2010 , if so you'll need Power Query add-in (link in the post#4)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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