Hi - I would like to do a search in my Excel Doc by finding duplicate records from the column Product Name. If there are duplicates, I don't want to just delete the field I want to delete the entire row. Is there a way to do that? The duplicate formula only deletes the field and then mismatches all my records. Any ideas? Thanks!
Here is how I would approach it, if Product Name is in column C, then I'd put this in another cell =countif($C$2:C2,C2) and copy down. This will put a 1 in every row that does not have a duplicate. Then I'd copy and paste special values where the numbers were. Then resort based on the values in descending order and you'd have the duplicates at the top, then highlight those rows and delete.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Here is another approach - along the same lines as Sambo Kid.
=IF(COUNTIF($C$2:C2,C2)>1,"D",1)
This will force a "D" (for delete) where there are multiples.
Now highlight the new column, and hit F5 - you will see the Go To dialog box. Click on Special... and Click the Formulas radio button. Remove the tick next to Numbers and click OK.
All the Ds will be selected.
Now just Delete the rows.
The only advantage of this method is that you don't have to re-sort your data.
I hope this helps, please let me know!
Regards,
David
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
Hi Terp,
Try:
If you haven't used macros before you'll need to go to:
Please Login or Register to view this content.
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)
Then - Copy the code to the clipboard
Open your Workbook
Press ALT + F11 to open the Visual Basic Editor.
Select "Module" from the Insert menu
Type "Option Explicit" then paste the code under it
With the cursor between Sub and End Sub press F5 (F8 to Single Step)
OR
Press ALT + Q to close the code window.
Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks