Remove duplicate rows, and keep newest row based on date column
Bjarke Mønsted
I have a huge list of data in excel (250.000+ rows) in the following format:
Number Value1 Date Value2
40325 1 21/01/11 18.10 2
65485 3 22/01/11 16.47 2
40325 9 25/01/11 19.00 0
70912 8 27/01/11 16.43 2
I need to remove duplicate rows based on column 1 (Number), and have no problem doing this using "Data/Remove Duplicates" in Excel, but I need to make sure that I remove the row with the oldest date, and keep the newest, based on column 3 (Date).
In the example above, I would need to remove row 1 and keep row 3, since row 3 is the newest.
I have 4.800 rows with duplicates, so a manual sorting/removing would be a very time consuming job.
Any good suggestions? And tricks to help me out? Thanks a lot in advance :)
nixda
The trick is to sort your table before using Remove duplicates. Excel always keeps the first data set of a duplicated row. All consecutive rows are removed.
In your case:
Set up a helper column and fill it with numerical values. Start by 1 and use autofill till the end of our table
Make sure your date column is formatted as date and Excel recognize them as date. Otherwise your sorting wouldn't work
Choose
Custom sort(depends on your Excel version). Sort your whole table by date column from Newest to Oldest. That's the important part
Use
Remove duplicatesand select only your Number column which holds your criteria to check for duplicates. Deselect all other columns
Choose
Custom Sortagain and sort your table by that Helper column we have added at the beginning to get your original row order back
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
TOP Ranking
- 1
pump.io port in URL
- 2
Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)
- 3
Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background
- 4
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 5
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 6
Spring Boot JPA PostgreSQL Web App - Internal Authentication Error
- 7
ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3
- 8
Using Response.Redirect with Friendly URLS in ASP.NET
- 9
Can a 32-bit antivirus program protect you from 64-bit threats
- 10
Double spacing in rmarkdown pdf
- 11
How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?
- 12
3D Touch Peek Swipe Like Mail
- 13
Bootstrap 5 Static Modal Still Closes when I Click Outside
- 14
Assembly definition can't resolve namespaces from external packages
- 15
Vector input in shiny R and then use it
- 16
Emulator wrong screen resolution in Android Studio 1.3
- 17
Svchost high CPU from Microsoft.BingWeather app errors
- 18
Graphics Context misaligned on first paint
- 19
Python connect to firebird docker database
- 20
Is this docker-for-mac password dialog legit?
- 21
How to save models trained locally in Amazon SageMaker?
Comments