I have an excel file with 12,000 rows containing serial numbers and dates.
The serial numbers are duplicated throughout the sheet as they have multiple dates. How can I delete duplicate serial number values, but keep the latest date value?
I have attached an example excel file.
Thanks!
Last edited by hpaum99; 03-27-2010 at 11:54 PM.
Hello hpaum99,
Welcome to the Forum!
Do you have just these 2 columns in original workbook as well?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Howdy neighbor! How's the weather up there in Delano?
Try this:
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hello hpaum99,
This macro is not effected by the 8192 cell limit imposed by the SpecialCells method. A button has been added to the sheet to run the macro.
Please Login or Register to view this content.
Leith, remember that the Special Cells limitation is 8192 areas, not cells. That can make a distinct difference. FYI.
Leith - I do have these columns (Columns D & N) in my sheet. There are multiple columns in the sheet, those are just the 2 that I wanted to focus on. Do I just copy that control over to my excel file and run it?
J - weather's good today! I'm new at this kind of excel work, I don't even know what to do with that code haha
thanks for the help!! i really appreciate it
Hello hpaum99,
Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.
To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Leith,
Thanks!! That was easy enough..
I ran the script, however I have more columns in my actual sheet. In the example I only posted 2 columns to make it easy, what do I need to change in the script so that the entire row is removed? The script is only affecting the cells in column a & b. The sheet goes up to Column W. There are duplicate values in all other columns but they can remain.
thanks,
Hiren
Mine updated, not sure if you've tried it or not, you may have missed it above originally:
Please Login or Register to view this content.
J - I ran the script and it gave me an error. I used the same steps that are in Leith's message.
Run time error '1400'
Sort Method of Range Class Failed.
When I debug, this code is highlighted:
Range("A2:Z" & LR).Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("B3"), _
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
thanks
Post up your actual workbook (dummy out the data if you need) so we can see why it acts differently in this workbook.
here you go!
Ah, see...significantly different. Having an Excel "List" already active makes this a much different creature.
This version will remove the list so the macro can edit the data, then reapply the list for you.
Please Login or Register to view this content.
Also, in the future, just start with a sample workbook more indicative of your needs. The initial workbook not only had no active LIST, the data was positioned differently. We could probably get it done in 1-2 posts with an accurate sample workbook.
That worked perfectly.
Thank you very much for the help! Appreciate it. I'll be sure to give full details next time.
Glad it worked out!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks