If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Deleting Duplicates, All records unique
My column headers a id, filename, location, and description. All descriptions are unique. My filename column has duplicates. For example, flower010104.jpg is listed twice, with two different descriptions. I want to delete BOTH rows containing flower010104.jpg. So, I want to delete ROWS with duplicate filenames, regardless of the description being unique (which makes the 'record' unique). I have found that I can only filter by 'unique record', but ALL records are unique, due to the description. I need help. How can I do accomplish my task? -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500305 |
#2
|
|||
|
|||
Deleting Duplicates, All records unique
If this is impossible, please let me know! Doing this manually is taking forever! -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500305 |
#3
|
|||
|
|||
Deleting Duplicates, All records unique
mirdonamy,
Use another column with a formula like this in row2: =COUNTIF(B:B,B2)1 Where column B has your filenames. Then copy down to match your data table, then filter or sort based on that column, and delete rows where the value of your formula is TRUE. HTH, Bernie MS Excel MVP "mirdonamy" wrote in message ... My column headers a id, filename, location, and description. All descriptions are unique. My filename column has duplicates. For example, flower010104.jpg is listed twice, with two different descriptions. I want to delete BOTH rows containing flower010104.jpg. So, I want to delete ROWS with duplicate filenames, regardless of the description being unique (which makes the 'record' unique). I have found that I can only filter by 'unique record', but ALL records are unique, due to the description. I need help. How can I do accomplish my task? -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500305 |
#4
|
|||
|
|||
Deleting Duplicates, All records unique
That's a pretty impressive formula, but here's the odd thing... TRUE only brought up 22 records (all duplicate filenames, just as I wanted). However, it didn't bring up the other 700+ records that have duplicate filenames. I can't quite understand why this happened. Just a note, these filenames have a row filled in completely (all the way across) and the duplicates do not have any information filled out in other columns (other than the filename) column. Does this affect the formula? Bernie Deitrick Wrote: mirdonamy, Use another column with a formula like this in row2: =COUNTIF(B:B,B2)1 Where column B has your filenames. Then copy down to match your data table, then filter or sort based on that column, and delete rows where the value of your formula is TRUE. HTH, Bernie MS Excel MVP "mirdonamy" wrote in message ... My column headers a id, filename, location, and description. All descriptions are unique. My filename column has duplicates. For example, flower010104.jpg is listed twice, with two different descriptions. I want to delete BOTH rows containing flower010104.jpg. So, I want to delete ROWS with duplicate filenames, regardless of the description being unique (which makes the 'record' unique). I have found that I can only filter by 'unique record', but ALL records are unique, due to the description. I need help. How can I do accomplish my task? -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500305 -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500305 |
#5
|
|||
|
|||
Deleting Duplicates, All records unique
Here's another fairly quick way. I assume your data is not sorted by
filename and I presume you want to keep the sequence you have at the moment. Assume your four fields occupy columns A to D, and that the data starts in row 2 (after the headings) and goes down to row 5000. Add the heading "seq" in column E and in E2 enter 1. Highlight cells E2 to E5000 then Edit | Fill | Series and check Linear with a step value of 1. Click OK - this will fill a sequence down this column to enable you to get the data back into the same order. Highlight A1 to E5000 and sort the data using filename (column B). Add the heading "Check" in column F, and in cell F2 enter the following formula: =IF(OR(B2=B1,B2=B3),"duplicate","unique") Copy this down to F5000 (double-click the fill handle). Select Data | Filter | Autofilter (on). Filter column F for "duplicate". Highlight all visible rows between Row 1 and Row 5001, and Edit | Delete Row. Use the filter pull-down on column F to select "All", then Data | Filter | Autofilter (off). Re-sort the remaining data using column E (seq) for sort order. Finally, delete columns E and F. Hope this helps. Pete |
#6
|
|||
|
|||
Deleting Duplicates, All records unique
Note, you will get some #REF in column F after you have deleted the
rows, but this does not matter. Pete |
#7
|
|||
|
|||
Deleting Duplicates, All records unique
You are brilliant!!! Thank you so much! You saved my day and gave me back hours of my life! Thank you thank you! I am so appreciative! Arielle Pete Wrote: Here's another fairly quick way. I assume your data is not sorted by filename and I presume you want to keep the sequence you have at the moment. Assume your four fields occupy columns A to D, and that the data starts in row 2 (after the headings) and goes down to row 5000. Add the heading "seq" in column E and in E2 enter 1. Highlight cells E2 to E5000 then Edit | Fill | Series and check Linear with a step value of 1. Click OK - this will fill a sequence down this column to enable you to get the data back into the same order. Highlight A1 to E5000 and sort the data using filename (column B). Add the heading "Check" in column F, and in cell F2 enter the following formula: =IF(OR(B2=B1,B2=B3),"duplicate","unique") Copy this down to F5000 (double-click the fill handle). Select Data | Filter | Autofilter (on). Filter column F for "duplicate". Highlight all visible rows between Row 1 and Row 5001, and Edit | Delete Row. Use the filter pull-down on column F to select "All", then Data | Filter | Autofilter (off). Re-sort the remaining data using column E (seq) for sort order. Finally, delete columns E and F. Hope this helps. Pete -- mirdonamy ------------------------------------------------------------------------ mirdonamy's Profile: http://www.excelforum.com/member.php...o&userid=30348 View this thread: http://www.excelforum.com/showthread...hreadid=500305 |
#8
|
|||
|
|||
Deleting Duplicates, All records unique
Well, thanks very much for the feedback - I didn't expect such praise !
Pete |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filtering For Unique Records | thecurries | General Discussion | 1 | October 7th, 2005 03:52 AM |
Duplicates - deleting to get just unique ones | koncept | New Users | 3 | August 24th, 2005 06:41 PM |
filtering for unique records | KG | General Discussion | 7 | August 13th, 2005 06:07 PM |
expression to sum records with unique id number | Steely via AccessMonster.com | Setting Up & Running Reports | 1 | June 1st, 2005 10:26 PM |
Removing Duplicate Records - Keep Unique Records | Craig Walker | Database Design | 1 | May 18th, 2005 03:12 AM |