Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to file compare Excel worksheets
I add records, daily, to a master file. Sometimes a record is a duplicate.
At this point, I sort by one column and then scroll down looking for duplicate records, which I delete. Is there an easier way? I don't want to have duplicate records. THanks, Walt |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to file compare Excel worksheets
What I usually do is in a new column put: =IF(AND(A2=A1),"DUP","") This only works if you are trying to compare a column with unique IDs. However, if you're looking at a list of names where some names might be duplicated (example: Two people named James, but one lives in USA and the other lives in Germany) try something like this: =IF(AND(A2=A1,B2=B1,C2=C1,D2=D1),"DUP","") You're limited to 30 different ANDs... but that usually does the trick. ...then copy/paste the formula down. This makes it much easier to identify duplicates when you scroll down. And if you want to go a step further: You can then copy the new column and pastespecial (values) and then re-sort the data by this new column in descending order. Delete the duplicate rows then re-sort by your original column. This only checks to see if the cell is equal to the one above it so it only works after everything is sorted by your original sort column. Probably easier to do it manually if you only have 20 or 30 records, but it'd save you a lot of time if you have 100s or 1000s of them. I hope that makes sense. wrdennig Wrote: I add records, daily, to a master file. Sometimes a record is a duplicate. At this point, I sort by one column and then scroll down looking for duplicate records, which I delete. Is there an easier way? I don't want to have duplicate records. THanks, Walt -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=534582 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to file compare Excel worksheets
THanks. My file has 7 columns. Each row is unique, except for the
duplicates. I'd like to search for duplicate information in column 6 (telephone numbers) . . . how would I write that script? And then what are the key strokes to make the compare happen? Can I then delete the duplicate row? And how do I get it to go to the next duplicate? Sorry for all the questions. I'm a very un-geeky guy . . . walt "Ikaabod" wrote: What I usually do is in a new column put: =IF(AND(A2=A1),"DUP","") This only works if you are trying to compare a column with unique IDs. However, if you're looking at a list of names where some names might be duplicated (example: Two people named James, but one lives in USA and the other lives in Germany) try something like this: =IF(AND(A2=A1,B2=B1,C2=C1,D2=D1),"DUP","") You're limited to 30 different ANDs... but that usually does the trick. ...then copy/paste the formula down. This makes it much easier to identify duplicates when you scroll down. And if you want to go a step further: You can then copy the new column and pastespecial (values) and then re-sort the data by this new column in descending order. Delete the duplicate rows then re-sort by your original column. This only checks to see if the cell is equal to the one above it so it only works after everything is sorted by your original sort column. Probably easier to do it manually if you only have 20 or 30 records, but it'd save you a lot of time if you have 100s or 1000s of them. I hope that makes sense. wrdennig Wrote: I add records, daily, to a master file. Sometimes a record is a duplicate. At this point, I sort by one column and then scroll down looking for duplicate records, which I delete. Is there an easier way? I don't want to have duplicate records. THanks, Walt -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=534582 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to file compare Excel worksheets
Sort the data using telephone number as the sort field. Put a heading
like "check" in H1 and a variation of Ikabod's formula in H2: =IF(G2=G1,"Duplicate","Unique") which will report the second and subsequent duplicates. Copy this formula down the column. Then click Data | Filter | Autofilter to apply filters to each column, and use the filter pull-down for column H to select "Duplicate". Highlight the visible rows and Edit | Delete Row, then use the filter pull-down to select "All". Your duplicate records will now have been deleted, and you can remove the filters and delete column H. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Pasword protected Excel file encrypted, how do I read this file? | Excel Discussion (Misc queries) | |||
Weird File Open/Save As Behavior | Excel Discussion (Misc queries) | |||
Excel file automatically opens | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel |