Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find & delete duplicated entries.
Using Excel 2002 I have a file tracking system which records who has taken
files out and when they are returned. In some cases files have been returned, but not marked as such and then taken out again by a different person. This results in the same file being shown as "out" with two different people. I need to delete the duplicated entries (entire row) which have the earlier "out" date, and move the remaining data up as it goes through the process. The data can be sorted by file name to bring possible duplicates together if necessary. Thanks, |
#2
|
|||
|
|||
Create a new column and number each row 1 to the last entry. (you are doing
this incase you make a mistake and need to resort the records by this index) Now sort the entire list by the file name, by status and by date. Sort in decending order by date You should see now the same file checked out twice right above and below each other. In another new column use the concatenate function and concatenate fields from the row to create a unique id concatenate(filename:outstatus) Copy this formula all the way down. Now copy and paste as value this concatenated column so the formulas are now repaced by the values (should be like filenameout) In a new column create a formula using the concatenated cell like =K2=K1. You should get a T or F status if this is True or False. Copy this formula to the bottom of the spreadsheet data. Copy and paste this column as a value. Now sort the entire list by the TF column True Items will be unique entries. The False items should be a duplicate value and can be deleted. Check your results and resort the list by the index you initially added. "Ken G." wrote: Using Excel 2002 I have a file tracking system which records who has taken files out and when they are returned. In some cases files have been returned, but not marked as such and then taken out again by a different person. This results in the same file being shown as "out" with two different people. I need to delete the duplicated entries (entire row) which have the earlier "out" date, and move the remaining data up as it goes through the process. The data can be sorted by file name to bring possible duplicates together if necessary. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find duplicate entries and delete them? | Excel Worksheet Functions | |||
Find duplicate entries | Excel Discussion (Misc queries) | |||
find row and delete | Excel Discussion (Misc queries) | |||
Find delete | Excel Discussion (Misc queries) | |||
How do I delete duplicate entries? | Excel Worksheet Functions |