I have a set of inventory records (84,000 user workstations) that have some duplicate records (~2%). The database is suppose to have a single record per workstation (unique ID). Each record has a date associated with it, and the duplicate records each have a different date. I need to keep the most recent record (based on date) and discard the duplicate records with the old dates.
Last edited by dajlwj; 01-12-2012 at 09:29 AM. Reason: Attached a sample file
Please attach a sample workbook so we can help you faster.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Thanks Arlette, I've attached an example file to my original post. For clarity the duplicated items are hilighted in red. I need only the most recent (based on date) which means only one of the either 2 or 3 entries having the same device name.
-Dave
Use this code -
Please Login or Register to view this content.
Arlette,
I'm afraid I don't know how to implement this solution. I was hoping for a solution that used cascaded or nested excel functions that I could enter into a cell, then perhaps select only those records flagged having the most recent date.
Thanks,
-Dave
I am not very fluent in complicated formulae, but can provide a solution with VBA.
All you have to do is press Alt+F11, you will get a code window. On the left hand side, you will see Microsoft excel objects. Right click and select insert -> Module. Copy the above code into that module. Go back to your excel sheet, select View -> Macros and select the macro to be run.
I've have a solution that works for me. I'd be interested in a critique if you have the time. If you open the test file in my previous post, the columns are in the following order - Device, OS, Date, Type. In my solution I moved the Date over one column to put it next to the Device. This made the array for the VLOOKUP compact. The one other thing that was needed was to sort the list by Date from newest to oldest. This guarenteed that the first Date that existed for a given Device was the most recent. I next inserted a column (NewestDate) with the formula =IF((VLOOKUP(A2,A:B,2,FALSE)=B2), B2, "old") to identify old dates. The Dups column is left in for testing.
My interest was to discover an Excel function solution to do this. I appreciate the code solution previously posted, and I also know this is possible to accomplish manually using spread sheet menu options.
-Dave
DAVE - THANK YOU TIMES 1 BILLION! You saved me hours of work!!!!
Hi Arlette,
Thanks for the code, it is working for me as well. But one more thing, how to alter your code, suppose the codes and values are in different columns, for example:
codes in column D (instead of column A for duplicated record in your code)
Values in column H (instead of column C for dates in your code)
Try this
Please Login or Register to view this content.
Re: remove duplicate records based on most recent date
debtor -test1.xlsm
Hi arlu1201,
Please attach a sample workbook, i got error message "run time erro 9 - subscript out of range" by trying to use your code
duplicated items in column D
Keep the most recent date in column M
I don't know how to edit your code, thanks help in advance
A subscript out of range error is related to the sheet names in the code not matching with the sheet names in the file. Ensure that the sheet names match and then run the code. It should work.
Hi Arlu,
I have used this code and it is working.For 5000 rows of data,I ran the code but found still few duplicate records.
For this data ,ran 3 times of this code .Then I got the proper result.
Actually I have do this scenarios for 50000 records,How to do it.Because while running this code ,it is taking too much time
My scenario is Column E -duplicate date(Instead of D)
Coulmn B-Insted of column A
taps135,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
Based on your sample
Yes based on the sample
So, have you tried the attached?
Yes based on the sample
taps135,
You are pursuing your question here http://www.excelforum.com/excel-form...test-date.html so dont discuss it in this thread. You are duplicating efforts.
Hi AB33
Thanks for the solution. It is working fine
Same question I have asked in this http://www.excelforum.com/excel-form...test-date.html .
So we will discuss in that thread
Hello Everyone,
First of all I'd like to apologize for bringing up the old topic back, but I really want to THANK dajlwj for posting his excel sample to remove duplicate records based on date.
You saved my life.
Thank you!
J.
Who is dajlwj?
The thread was not only moved to a different section, but also closed long time ago.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks