Remove all older rows ...

[l0shmi]

Hello good people,

I have a huge list of data in excel (200.000+ rows) in the following format:

Number Value1 Date User
INC40325 1 9/9/2016 2:52:11 AM user1
INC65485 3 9/3/2016 7:57:58 AM user2
INC40325 9 9/9/2016 1:39:49 PM user3
INC70912 8 9/5/2016 9:09:36 PM user1

I need to remove all older rows based on column 1 (Number) and keep only the newest.
In the example above, I would need to remove row 1 and keep row 3, since row 3 is the newest.
I would appreciate any good suggestions, since I don’t know where to start thank you.

 

[wigi]

Hi

Combining speed and simplicity, I would go for this solution:

- create a pivot table. In the rows, put the field "Number". In the data section, put the "Date" field. Summarize these date values by MAX.
- add 1 column to your data, and do a vlookup to the 2-column pivot table. If the date for a given number is smaller than what the VLOOKUP returns, then mark this record
- use an autofilter to delete all marked records

 

[clabulis]

Perhaps just turning on filters, sorting the "Date" field Newest to Oldest, then remove duplicates based on the "Number" field?

If you have your data in a specific order already, then create a temporary helper column called "Order" and number it 1 through x before sorting. After sorting, you can sort the "Order" field smallest to largest to get the original order back.

 

[wigi]

That's a better solution clabulis, good point !

 

[Flatron]

Sorry for my english but you can try in here video


https://www.youtube.com/watch?v=vEFJE4O1Ck8

 

[l0shmi]

Thank you guys for quick reply. I am testing clabulis solution and it's seems it's working for me . Appreciate all your suggestions!!!