how to remove duplicate day entries

[bigdan]

I'm dealing w/ a file about 1800 rows long.

This is the log of who entered the gym at any given time. Now each time someone enters that's recorded. Which means if someone enters 3x a day I get 3 entries.

I want to remove the duplicates so that if I enter twice today and twice tomorrow I only see one entry for each date (per person).

An example of duplicates below. The date and time is col A, name is col B.
It doesn't matter which of the 3 entries is gotten rid of.

7/13/2011 9:23:50 AM Howard, John
7/13/2011 9:58:14 AM Howard, John
7/13/2011 2:01:00 PM Howard, John

 

[Sthrncali]

Excel has a built in function to deal with this called:

Remove Duplicates.

Very easy step by step guide here:

http://office.microsoft.com/en-us/e...te-rows-from-a-list-in-excel-HA001034626.aspx

 

[bigdan]

thanks for this. this is close but not fully complete.

this removes exact duplicates. i.e. if the same person came in on august 2nd at the same time then this would remove it. but if he came in on same day but different time, as would of course be the case, then it doesnt catch it.

one way around this would be to split the first cell into 2, date in one column time in the other, remove the times, and just concentrate on the dates with names. how does one do that?

alternatively there may be a way to remove duplicate days directly?

 

[Sthrncali]

When you remove duplicates, Select both columns then click Remove Duplicates. Now a window displays and says "Which col has duplicates?" and by default both columns will have a check box next to them.

Just uncheck the Col that contains date/time, but leave the Col with the names checked, then click OK.

Excel will ignore the date/time col and delete every row that has a dup name, leaving you with one name left.

 

[bigdan]

got it thanks!

not sure how it works on this forum, when my question is done do i say thread closed or something, or is this automatic or not necessary?