Delete Duplicate Rows Containing The Oldest Date And Time Entries?

May 23, 2014

I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.

My master sheet is set out as such...
A, B, C, D,
UserID, Date, Time, Response

The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").

I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").

View 5 Replies


ADVERTISEMENT

How Do I Conditionally Delete Duplicate Entries?

Aug 8, 2008

I have 2 columns of data, apprx. ~25,000 rows.

Col 1 is user IDs and Col 2 is there status (pending, conditional, approved, rejected)

Col1 IDs are not unique because they can have multiple statuses associated with them in Col2. An ID can go from pending to conditional to either approved/rejected and all these are included in the raw data file. I want to remove all duplicate ID rows and keep the ID row with the last known status.

For example:

View 14 Replies View Related

Delete Duplicate Entries, Keep Last And Delete First

Feb 15, 2010

I found this sample code that works from top to bottom of a spreadsheet. But I need something that will delete the first entry and keep the last entry. My data is sent from one spreadsheet to a Master and sometimes the details can be sent twice, if the responsible person forgets to enter one line of production. The criteria should be the first 5 Columns of the sheet.

Sub Dupe_Killer()
Dim str As String
Dim str2 As String
Dim c As Integer
Dim i As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("SAMPLE").Select
rw = Cells(2, 1).End(xlDown).Row
'Sort Data by Date, Location & Number
Range(Cells(1, 1), Cells(1, 14)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _
, Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ....................................

View 2 Replies View Related

Macro To Detect And Delete Multiple / Duplicate Entries?

Nov 28, 2013

I need a Macro to do the following:

In column A I have a list of Acronyms from A2:A90000 and more

In column B I have the corresponding acronyms spelt out from B2:B90000 and more

When I run the macro, it shoud detect the multiple/duplicate Acronyms and it's corresponding descriptions, DELETE the multiples/duplicates and move the cells up.

View 5 Replies View Related

Trim Oldest Entries From Text File To Allow For New

Apr 30, 2008

I am writing to a text file and only want to allow 10,000 lines. There is only one value on each line. How can I trim the top of this text file to allow for more entries to be appended to it and still keep it at no more that 10,000 entries?

The lines look like

"1001-1"
"1002-3"
"1003-3"
"1004-4"
"1005-5"
"1002-6"
"1001-10"
"1004-7"
"1015-5"
"0002-6"

View 14 Replies View Related

Highlight Duplicate Entries In Same Date?

Apr 24, 2014

highlighting the duplicate entries in the same date comparing C:C data.

View 3 Replies View Related

Delete Duplicate Rows :: Duplicate Company Names

Dec 11, 2008

I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.

Attached is a sample of that spreadsheet.

View 5 Replies View Related

Delete Duplicate Cells Or Rows With A Duplicate Cell

Nov 1, 2007

I feel as though I have spent enough time searching the previous posts to ask this question.

I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.

COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner

I want to end up with rows that each contain unique info in COLUMN B.

View 9 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.

Col A
100
101
102
105
100
101
102
105

Col D
5
4
2
4
1
2
3
1

After my code is run, I need for my spreadsheet to look like this

Col A
100
101
102
105

Col D
6
6
5
5

I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.

Here is my code thus far....

-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)

[Code]....

View 9 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.

Col A
100
101
102
105
100
101
102
105

Col D
5
4
2
4
1
2
3
1


After my code is run, I need for my spreadsheet to look like this
Col A
100.........................

View 9 Replies View Related

Duplicate Entries In Rows - Add Accompanying Data To New Columns?

Jun 4, 2013

I have attached a before and after image of what I am looking to accomplish.

In the before image, you can see that there are 3 rows of data

- a header

- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)

In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data

- a header

- the repeating model number

What I did however in the after image, as can be seen, is I took the data that appeared on the repeating row (Row 3 from the Before image), and included the PRICE, COST, BEGIN, and END values in new columns on row 2.

The model number is the same of course, which is why I did it that way.

If the model numbers were different, I would have simply left it alone, as-is.

Is there a way to accomplish this via some functions or maybe even a VBA script?

By the way, if we have to dump all of this onto a new sheet, then so be it.

View 5 Replies View Related

Delete Time Entries Under 5 Minutes

Jun 9, 2009

i have a slight problem i have this script which i want to run on all worksheets which are numbered (i.e. 1,2,3,4 etc) and to delete the rows in the F128 range which is under 00:05:00. I just cant figure it out to get it working.

View 2 Replies View Related

Show Date/time Between Entries

Oct 11, 2008

Basically each line signifies a date & time that a deposit was placed and a date & time that a withdrawal was actioned. My goal is to be able to determine what kind of average response time we have between the time a withdrawal was requested and the time a withdrawal was actioned.

View 2 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows?

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.

My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.

So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5

[Code]....

My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.

View 4 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5

Should be processed and come out like this:.......................

View 2 Replies View Related

Conditional Date & Time Stamp Entries

Oct 23, 2007

I have a column (D) where I put percentages complete of a task. When that reaches 100% I want to put the date in column (C).

I found many samples online but nothing I can modify to do this.. This one is pretty straight forward but I'm not sure how to have it look for the value and not just empty or not. I would be greatful for any help you can offer.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Date
Target.offset(0, -1).numberformat = "yyyy-mm-dd"
End If
End Sub

View 6 Replies View Related

VBA To Delete Duplicate Rows?

Aug 18, 2012

VBA code that will delete all the rows where cell "C" is duplicated

I have look on the net and there is loads of complicated codes out there but I am looking for something fast.

View 2 Replies View Related

How To Delete Duplicate Rows

Jan 23, 2014

I am trying to delete duplicate rows and have had some success using a VBA from MR Excel threads. However, i would like to add multiple sheets to be compared. When i change the vba to use array, the next line fails with error 438, not a supported objectproperty method.....

Here is the code:

Sub CheckWest()
Dim LR As Long, i As Long
With Sheets("west")
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If IsNumeric(Application.Match(.Range("C" & i).Value, Sheets("Removals").Columns("C"), 0)) Then .Rows(i).Delete
Next i
End With
End Sub

This checks the sheet "removals" column "c", any duplicates found in sheet "west" column "c", row is deleted. I would like to add additional sheets, how to correctly change this line of the VBA?

With Sheets(array("west", "east", "north"))?? I beileive this is what i tried and the next line errored.

View 9 Replies View Related

How To :: Delete Duplicate Rows?

Mar 21, 2007

How would I go about deleting duplicate rows?

I've searched the board already and I couldn't find this.

I have a sheet with different data in each column, song titles, writers, owners and so on.

say this information runs from column A to H down 10,000 rows, occasionally information is duplicated in certain rows, except for column A.

I need to be able to search the sheet and delete any extra rows that are duplicated.

So I'll end up with the exact data in each row only appearing once.

View 9 Replies View Related

Delete Both Sets Of Duplicate Date?

Oct 8, 2008

I currently have about 3000 rows of data, much of it being duplicate data. Is there a way to have it search for duplicate data, and have it delete BOTH sets of duplicate data? For example, say this is what is in my spreadsheet:....

I would want it to delete both John Smith records and both Ted Nugent records, leaving Bob Bobbert's and Michael Jordan's records.

View 4 Replies View Related

Delete Both Original And Duplicate Rows?

May 25, 2014

My worksheet that will have duplicate data inserted into it. Once inserted I need to delete the new duplicate row and the row it duplicated. The attachment is a copy of what the data looks like with the first tab showing what it will look like with inserted data and the second tab is what I want it to look like at the end. I will not need the deleted data again so it does not need to be on a new sheet, that is just for an example. The name of the tabs will also be different so I want to be able to run it on any worksheet. This is the code I am using to find and delete the duplicate but I can't get it to delete the original. I used "First, MI, Last, and DOB" because those are the ones that won't be duplicated where others will. This is a list of about 15,000 entries. There should never be an incident where there is triplicate data but I can't say for sure.

[Code] .....

Attached File : Practice.xlsx‎

View 2 Replies View Related

Delete Duplicate Rows, With A Twist

Dec 4, 2008

I can delete duplicate rows easily enough, but I can't seem to capture a couple variables where I would not want to delete the selected row. There are no headings, all data starts in Row 1.

I start in Row 1 Columns C and D, compare that to Row 3 Columns C and D (Every other row). If it is a duplicate entry in both columns, delete that row and the row below it. Pick up at next row and continue. See attached Excel file (Excel 2003). The rows with fonts colored RED are the only rows from this data that should get deleted.

View 2 Replies View Related

Delete Duplicate Rows Vb Needs To Run On All Worksheets

Apr 20, 2009

Hi i have the below code which runs on deleting duplicate code which i found in an excel manual. I am having some trouble as i want to convert the code so that it will run on all worksheets i have which are numbered such as 1,2,3,4 etc.

This is so it makes the workbook easy maintenance for when deleting or adding worksheets which happens quite regularly.

View 7 Replies View Related

Delete Rows With Duplicate Values

May 1, 2006

I have a spreadsheet with three colums of data. The first column contains
records which have occasional phone number duplication- see blelow:

(555) 000-0000DataA1ValueA1
(555) 000-0000DataA2ValueA2
(555) 555-9770DataA3ValueA3
(555) 555-4464DataA4ValueA4
(555) 555-4464DataA5ValueA5
(555) 555-4720DataA6ValueA6
(555) 555-8823DataA7ValueA7
(555) 555-3834DataA8ValueA8
(555) 555-4125DataA9ValueA9

What I need to do is (somehwhat) automate the process of filtering or
deleting out all rows which have duplicate data in the first column, but not
second or third columns. I'm sure it's been done...I tried the Excel
out-of-the-box.

View 14 Replies View Related

Delete Duplicate Rows If 3 Conditions Are Same?

Jul 1, 2012

I have a sheet caled "Data", with thousands of rows.

I want to delte any rows that have duplicate rows only if the following data is in the same row but if in column A, coloumn C and column H have the same data

A B C D E F H I
w 1 1 3 4 5 6 7
e 1 1 1 3 4 5 8
w 2 1 5 2 5 6 7

The last row would need to be deleted as dupilcates are A C and H

The data is not sorted.

View 3 Replies View Related

Delete Duplicate Rows Keep The One With Highest Value?

Aug 5, 2012

I need to delete duplicate rows in the "Description" column but keep the highest number in the "Order" column. In addition, I need to combine the duplicate amounts for (Budget, Commit, PTD, Avail) into the one line item that is left after removing the duplicates. This is what the spreadsheet would look like initially:

Order
Description
Budget
Commit

[Code]....

View 6 Replies View Related

Remove Or Delete Duplicate Rows

Aug 24, 2008

I have 6 columns on my data sheet. F column is Amount column.

If there are rows where all the columns A,B,C,D and E duplicate then I need that row to be deleted, BUT before that the value from the column F should be summed to the duplicating row's F column.

I attached a simple example file, where you can see my point better. (I need the code to work with thousands of rows, and not only with 5 rows which i put on the axample)

View 9 Replies View Related

Delete Duplicate Rows With Condition

Nov 1, 2006

how to delete duplicate rows except the first and the last rows using macro..

I have data like this and i want to delete those rows except the first and the last rows

31/10/2006
31/10/2006
31/10/2006
31/10/2006

I use the below macros from btadams posted 27th January 2003 but only for delete the last row

Sub DleteDups()
Dim Cell As Range

Do While ActiveCell.Offset(1, 0) <> ""
If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If
Loop
End Sub

View 9 Replies View Related

Delete Duplicate Rows Without Having To Look Manually

Apr 10, 2007

I have 4 colums of data (A2:D5684) with the data sorted ascending by column A. I would like to delete the rows with duplicate information without having to manually look through the 5684 lines.

View 5 Replies View Related

Delete Duplicate Rows Including 1st Row

May 16, 2007

delete duplicate rows and leave only the LAST row. The codes I have seen leave the first row.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved