Delete Duplicates With Macro Keeping Only Newest By Date

Feb 14, 2011

I have a spreedsheet with 17,000 rows in it. In column D I have ID numbers and in column B I have the dates. There are multiple entries for each ID in column D which gives multiples dates each entry was given in column B. I need a VBA Macro that will identify duplicate entries in Column D and keep only the most recent entry by date in column B.

View 9 Replies


VBA To Remove Duplicates And Keep Newest Date

Dec 6, 2013

I have a pretty good size file here where I need to remove duplicated date BUT keep only the Newest Record.

What is Needed is to look at "test Group Identification" and compare it to "Actual Start Time" and keep only the Newest Record and remove any Exact Duplicated data

See example of Data.

Test Status
Test Group
Test Group Identification
Actual Start Time
Want I need

09-Jan-2013 15:48:25 EST

[Code] .....

View 3 Replies View Related

VBA Detect Duplicates - Keep Only Newest Ones

Feb 23, 2012

I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.

View 1 Replies View Related

Create A Macro That Will Autofilter My Starting Dates In I To The Newest Date Entered

Feb 11, 2008

i wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....

View 9 Replies View Related

Finding Duplicates And Keeping Them With Totals?

Apr 8, 2013

I have a database with ~18000 rows and 29 columns. I would like to filter the data by duplicates in one column, based on total, but keep the remaining data in the row. For example: I have account numbers listed in one column, often duplicates. I can get the total in a pivot table no problem, but need the other data associated with that account. I do not need to see all accounts, only duplicates for accounts listed say greater than 5 times. The data in columns B+ are important.

I have a HUGE vlookup I created to paste in the pivot table data (account numbers and totals) to run a look-up based on those numbers, but I see that running into problems when you run 4k+ look-ups.

I want to see accounts listed only 5+ times, include that total (as in a pivot table) and the remaining 28 columns. I have tried to run this in a pivot table completely, but still too much data to process (plus all the subtotals that I have to keep removing).

View 1 Replies View Related

Excel 2007 :: Where To Find Oldest To Newest Or Newest To Oldest Sort Option

Jun 4, 2014

I want to sort a column in Excel 2007 which is having dates. Normally, we use oldest to newest or newest to oldest option in sort & filter. But i am unable to find this option in my excel sheet. how can I find this option in Sort & Filter.

Note: I have imported this data column from Access 2007

View 9 Replies View Related

Selecting Newest Date From A Column

Dec 22, 2009

I want to seach and select the newest date in a column. There are green, red and black fonts in the column. I only want to only seach the cells with black fonts.

View 9 Replies View Related

Find Oldest And Newest Date

Dec 29, 2009

Is there a way to pull out the oldest date ((04) Apr 25, 1950) and the newest date ((02) Feb 14, 2050)...?

i have tried the Min function (min(P28:P31)) and the Max function (max(P28:P31)) both resulting in a "0"..

i have also searched this forum as well as others and found formulas that were posted by Aladin Akyurek..but those did not work either..


P28(02) Feb 14, 205029(04) Apr 01, 201330(04) Apr 25, 200931(04) Apr 25, 1950
Spreadsheet FormulasCellFormulaP28=IF(O28=FALSE,"Who Knows?","("&RIGHT(O28,2)&")"&" "&LEFT(O28,3)&" "&LEFT(RIGHT(O28,4),2)&", "&LEFT(RIGHT(O28,8),4))

Excel tables to the web >> Excel Jeanie HTML 4

Im Using Windows Xp With Sp2 With Microsoft Excel 2003

View 9 Replies View Related

Finding The Newest / Latest Date In Each Record

Aug 26, 2009

I have a table that contains names and dates. I need to find the most recent / latest date in each record. For example:

Names Dates
A 01/02/09
A 05/25/09
B 06/09/09
B 07/30/09
B 01/02/09
C 03/09/09
C 02/28/09
D 12/12/09

The results should be:
A 05/25/09
B 07/30/09
C 03/09/09
D 12/12/09

View 4 Replies View Related

Macro To Delete File Duplicates?

Feb 5, 2013

I have a macro at work that shows which files are duplicates (same name plus file size) it then produces a list of this with 3 fields file path(excluding file), file name and ext, and size. it produces a duplicate of the file for example:

file a
file a
file b
file b

i would like a macro to delete just 1 of theese (as deleteing both would be bad!! haha) and have no idea how to start this. other than going through all 1679 files that have a duplicate.

View 9 Replies View Related

Find Duplicate In Certain Columns And Keep Newest By Date & Time

Jun 20, 2006

In the included workbook I've got a sheet that needs to be updated every time a userform is completed. So I intend to run a macro after the data is entered to find duplicates, and delete the older row.

so, column A must match then column C must match then look in columns AK & AL to find the newest entry to keep. I also need to delete any rows where column A is empty(light Green), no matter what other columns have data.

In my example sheet all the yellow's match for column A but the bright yellow's are the only "keepers", because one has a different number in "C" and the other is the newest.

Also remember that this is just part of a much bigger, multi-sheet book so I will need to I.D. this sheet in the code.

View 9 Replies View Related

Macro That Will Delete Duplicates On Entire Worksheets

Dec 29, 2011

I have a macro that delete empty cells in a column, what i wanted to do is while deleting the empty cells is that the macro will also delete duplicates in ENTIRE WORKSHEETS. My file has so many sheets so i need a macro that run or loop on my entire worksheets.

This is my code. that only runs in a single sheet.


Sub deleteblanks()
Columns("H").SpecialCells(xlBlanks).Delete (xlUp)
End Sub

[Code] ..............

View 9 Replies View Related

Macro To Find And Delete All Duplicates And Keep Only Unique Values

Sep 22, 2009

Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has

All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.

View 6 Replies View Related

VBA Macro Not Working To Copy Cells Then Delete Duplicates

May 29, 2013

I need it to clear the contents from the cells, then copy a number of sets of cells from selector, paste them in to AM call plan and then delete the duplicates. My code is getting stuck very early in the whole thing. The copy, paste sequences, were originally recorded as a macro, then added to trying to get it to work.

Here is the code, and the red line of code is where it is stopping:

Sub Call_Plan_Targets()
' Call_Plan_Targets Macro
Dim LR As Long
Application.ScreenUpdating = False
LR = ActiveSheet.UsedRange.Rows.Count


View 1 Replies View Related

Macro To Open "Newest" Created Folder

Aug 10, 2007

I've been working on a macro that opens me up to a certain path so I can select a file.

This is the path sofar:

TheFile = "\datawhse
My code takes me there, but I want to go a step further if it is possible.


View 9 Replies View Related

Sort By Date Keeping Duplicate Data In Column A Together

Feb 26, 2014

I need to sort my data by the oldest date first in colum L (NEED_DATE), but I need to keep the rows grouped by colum A (Material No). See example of data.

Material No
Tool No
Prodn Ordr No
Curr Oper No
Curr Workcenter Cd
Next Oper No
Next Workcenter Cd


View 2 Replies View Related

Macro To Delete Row By Date?

Jun 4, 2013

I am going to record a macro to clean up a report but the first thing I need to do is delete rows that contain dates before July 17, 2012. The dates are in column B. Is this possible in the macro?

View 5 Replies View Related

Adding Dates To Column And Keeping Data Associated With Proper Date?

Jun 19, 2013

I have a column with dates and times, and an associated column with data for all the dates and times. I want to add every hour of every day to the date/time column, but want the data associated with the times to stay in the row next to the correct time. How would I do this? In the attached file, i want to make coulmns A and B look like Coulnds E and F. I have hundreds (if not thousands) of rows, so i need a way to do this easily. Also, the times are not all evenly spaced or exactly on the hour. I dont need the times to be exactly on the hour or spaced out evenly, but i would like to have at least one time from every hour.

DBdate time alter.xlsx

View 2 Replies View Related

Macro - Delete Row If Column Has Certain Date

Feb 15, 2010

I generate a report in excel of about 63000 rows (this differs) there are several columns with dates, I wish to remove all the rows with a date before and after certain dates.

It seems I would need a loop for that, but it is unclear to me how this works, so far I have this:

Sub TestMacro2()
Selection.NumberFormat = "m/d/yyyy"
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.FreezePanes = True
For Each cell In Selection
If Date < 1 - 3 - 2009 Or Date > 2 - 3 - 2010 Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell
End Sub

View 13 Replies View Related

Macro To Delete Previous Date

Jun 23, 2007

I need a macro to loop through a column and delete any date prior to todays date, as well as all cells in the deleted date's row.

View 2 Replies View Related

House Keeping Macro

Mar 26, 2007

I want to create a kind of "Housekeeping Macro" that will run based on one single piece of information a number.

I want an inputbox to ask me for a number.

Based on that number I want the macro to do the following -

Go to Ws1 - Membership
Seach down column A until it finds the number put into the Inputbox
When it finds it do the following

Go to Column N and enter todays date as a value.
Go to Column Q and Enter No
Delete the data in R:S and U:V and X:Y and AA:AB and AD:AE

Go to Column J This contains the name of another worksheet i.e. Data1

Go to the worksheet Data1

Search down Column C and find all instances of that the the number entered into the inputbox (there could be several).

Go to each instance and delete the number.

View 9 Replies View Related

Keeping Column As TEXT When Running Macro?

Jan 29, 2014

I have a string of numbers and characters and I have a Macro that I copy-pasted from somewhere online that REMOVES any character that is not a number.

Is a great Macro that transforms:
"-a209 03 . 1 - 22 46" to 2090312246
I can send the code on request.

However the problem comes when running the macro into that string excel transforms it DIRECTLY into a Numeric. That is we are missing the first two, or first characters when they are zeros.

For example: "007 01 1.32.08" becomes 70113208 in stead of: 0070113208

And this is a problem.

I have tried to define the cell as text before running the macro. Fails. I have tried to re-define the cell afterwards as text but the zeros do not appear. Fails.

Including in the Macro a last minute section that forces the cell to remain as text...(seems easy! but I have do not know how to program in VBA)

View 3 Replies View Related

Excel Macro Delete Rows Where Date Criteria Is In Another Sheet

Feb 28, 2014

I am trying to adapt a macro which deletes all rows in Worksheet 1 where the date in column K differs from a date in Worksheet 2. The date in Worksheet 2 will change each day.

I think the macro below could be used, but don't understand how to replace the "Value = ":70:" with a reference to the master date in Worksheet 2.

Sub test() Dim lr As Long, i As Long lr = Range("A1").End(xlDown).Row
For i = lr To 1 Step -1 If Cells(lr, 1).Value = ":70:" Then
Cells(lr, 1).EntireRow.Delete End If lr = lr - 1 Next i End Sub

View 2 Replies View Related

How To Delete Both Duplicates

Apr 10, 2014

I've attached a test excel to this post. Column C contains several identical email addresses. I need ALL the rows containing the duplicate data removed, not leaving one remaining as Remove Duplicate Values normally does.

I'm going to be doing this with many Excels all with differing amounts of data surrounding column C. I need a reliable way to achieve this, regardless of how many extra data columns exist. If there is a duplicate of data in column C, all rows containing that data need to go, regardless of what other data may also be present in those rows.

View 10 Replies View Related

Delete The Duplicates

Jun 15, 2006

I'm new to this and trying to write my own code (quit satisfying actually), I realise there's a lot of threads on duplicates but I couldn't really solve my problem. My code looks like the following

Private Sub Dubletter_Click()

Dim i As Long, lastrow As Long
Dim cell As range

For Each cell In Sheets("LR").range(Cells(3, 2), Cells(Rows.Count, 2))
lastrow = Cells(Rows.Count, 2).End(x1Up).Row
For i = lastrow To 3 Step -1
If Cells(r, 2).Value = cell Then
cell.EntireRow.Delete .....................

View 9 Replies View Related

Delete Duplicates That Have Sum Zero

Feb 26, 2007

I want to create a macro that will look at different account numbers and delete those rows that have a sum of zero for that same account number. In other words, I am working with an accounting spreadsheet which shows both accounts payable and receivable. When payment is received for an account, I would like the macro to delete it, leaving only outstanding accounts. For instance: ....

View 7 Replies View Related

MACRO Saveas Keeping Current File Open

Nov 13, 2008

I have the following Macro that I run assigned to a Button.

Sub AskAndDo()
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD")
End If
End If
End Sub.......

View 9 Replies View Related

Delete Duplicates From A Column

Jun 19, 2013

I have a column with text values, I would like to remove all the duplicates. How can I do that??

View 4 Replies View Related

How To Delete The Emails That Have Duplicates

Oct 24, 2013

I have two columns with emails. I would like to delete the emails that have duplicates. I want to delete them from both columns.

View 3 Replies View Related

Delete Duplicates In Column D Only?

Jun 3, 2014

how to Delete Duplicates in Column D with a macro. It would search all of Column D and look for Duplicates and if it finds an exact value match it will delete one of the rows containing the duplicates. This is running in between a much longer macro which is the reason why I can't do it with conditional formatting.

View 12 Replies View Related

Copyrights 2005-15, All rights reserved