Question description:
This user has given permission to use the problem statement for this
blog.
Im trying to delete cells with duplicate names, but with different dates associated with them. One column has the name and another the date. How can i delete all the duplicate name cells while sorting for the most recent date?
Solved by C. C. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
03/07/2018 - 06:57
Hello, welcome to got it.
Excelchat Expert
03/07/2018 - 06:58
I understand you want to delete duplicate names and the names have a date associated with them.
User
03/07/2018 - 07:00
well kind of
User
03/07/2018 - 07:00
ill show an example
Excelchat Expert
03/07/2018 - 07:00
Thank you, that would be very helpful.
User
03/07/2018 - 07:01
id like to conditionally delete the date that is most recent
User
03/07/2018 - 07:01
so id like to delete the row from 2016
Excelchat Expert
03/07/2018 - 07:02
I'm sorry you have mentioned about deleting the most recent date, but 2016 isn't the most recent one, 2017 is.
User
03/07/2018 - 07:02
so basically, keep the latest dates for nathan and bob while deleting the duplicate name rows with an associated earlier date
User
03/07/2018 - 07:02
sorry, i want to keep the most recent date
Excelchat Expert
03/07/2018 - 07:03
I got it now. Thanks.
Excelchat Expert
03/07/2018 - 07:04
Although please note that I can only write a formula by which you will be able to filter out the most recent entry and then you have to delete the old entries manually.
Excelchat Expert
03/07/2018 - 07:04
Excel formulas can't delete data, you have to go with VBA/Macro do delete automatically.
Excelchat Expert
03/07/2018 - 07:04
Then again VBA/Macro is out of our service scope.
User
03/07/2018 - 07:05
ok but it would be able to sort the rows based on the latest date?
Excelchat Expert
03/07/2018 - 07:05
Yeah, you can sort it.
User
03/07/2018 - 07:07
what would that formula be?
Excelchat Expert
03/07/2018 - 07:07
You can sort the data without applying a formula.
Excelchat Expert
03/07/2018 - 07:07
Here is how to do it.
User
03/07/2018 - 07:07
right i know that
User
03/07/2018 - 07:07
but i am having different issues
User
03/07/2018 - 07:07
i can sort based on name and then date
User
03/07/2018 - 07:07
and then i get the organized data like i have here
Excelchat Expert
03/07/2018 - 07:08
Yeah, you can do that. Yeah.
User
03/07/2018 - 07:08
but then if i want to remove the duplicates, how would i sort those out?
User
03/07/2018 - 07:08
basically conditional sorting
Excelchat Expert
03/07/2018 - 07:08
Okay, I will write a formula for it. Please give me some time.
Excelchat Expert
03/07/2018 - 07:10
After sorting, use this formula in like in column C. =COUNTIF($A$1:A1,A1)
Excelchat Expert
03/07/2018 - 07:10
The value 1 in column C is the name with most recent entry.
Excelchat Expert
03/07/2018 - 07:11
Now if we apply a filter on column C and then deselect 1 from the filter, we will get the names with older dates. And can delete them.
Excelchat Expert
03/07/2018 - 07:12
Here, please see now.
User
03/07/2018 - 07:13
but that deleted the more recent date from row 2
Excelchat Expert
03/07/2018 - 07:14
Actually we have filtered out the most recent dates so that we can only see the older entries and delete them.
User
03/07/2018 - 07:14
ahh ok that makes sense
Excelchat Expert
03/07/2018 - 07:14
Once we perform the delete, clear the filter to see the most recent entries.
User
03/07/2018 - 07:14
so what is the function saying
User
03/07/2018 - 07:15
county($A$2:A2,A2)
User
03/07/2018 - 07:15
*countif
Excelchat Expert
03/07/2018 - 07:16
The function is counting how many times a name is there in column A.
Excelchat Expert
03/07/2018 - 07:16
The first occurrence would give 1, 2nd occurence would give 2.
Excelchat Expert
03/07/2018 - 07:16
And so on.
Excelchat Expert
03/07/2018 - 07:17
Since we have already sorted by names and then by date, this will assign 1 to the most recent occurrence of each name.
Excelchat Expert
03/07/2018 - 07:17
Making sense?
User
03/07/2018 - 07:17
can we resort so i can see if the new /18 date works?
User
03/07/2018 - 07:17
makes sense though
Excelchat Expert
03/07/2018 - 07:17
Yeah, please sort again.
User
03/07/2018 - 07:20
let me try this with my data
Excelchat Expert
03/07/2018 - 07:20
Sure, take your time.
User
03/07/2018 - 07:21
thank you so much
User
03/07/2018 - 07:21
this worked well
User
03/07/2018 - 07:21
much appreciated, thank you for your time
Excelchat Expert
03/07/2018 - 07:21
Thanks for the appreciation.
Excelchat Expert
03/07/2018 - 07:22
Please rate me 5 star after closing this chat. I believe there is a close button on your side.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
