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.
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:.......................
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
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.
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)
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.........................
Situation: I would like to compare the information between two worksheets and delete the rows that contain the same data in multiple columns, on a row by row comparison.
IE: I have two worksheets, each have identical row headers, with 5 columns each.
Company Load Date Load Time Load Description Amount ?Report? Store#44 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1 Store#44 5/14/2009 02:34:21 AM SLATOUR $20.00 WS1 Store#45 5/14/2009 01:55:41 AM GCHANDLER $100.00 WS1 Store#46 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
If column A(Company), B(Load Date), and E(Amount) for record 35 in worksheet one, match the same columns for a record in worksheet two, both records are deleted/highlighted/marked with an x in an additional column/anything.
Alternately, I can combine the data in both worksheets into one large worksheet, if that would make the solution easier. And or adding a column that idenifies which record came from which report.
Basically I have two similar reports; each contain a few rows of transactions that the other does not, I need to separate the matching transactions from the unique transactions, in order to balance the two.
I have tried using the Remove Duplicates function but it saves one of the matching records (they should add an opiton to delete matching records aswell keeping only truly unique records), I dont understand how to work Conditional Formatting to get it to do what I want, I dont know macros, or vlookups.
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.
Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:
ExampleCell1 ExampleCell2 ExampleCell3 UID = example
ExampleCell1 ExampleCell2 ExampleCell3
ExampleCell1 ExampleCell2 ExampleCell3 UID = example
That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".
Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.
Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.
I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.
Sub TestDeleteRows()
Dim Firstrow As Long Dim LastRow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long
With Application CalcMode = .Calculation .Calculation = xlCalculationManual . ScreenUpdating = False End With
Delete the entire row where any cell in that row is equal to cell "b15" in worksheet "Metal Type". I would like it to run across all the worksheets hidden or unhidden, in the workbook, (except for worksheet "Metal Type").
If possible I would also like it to prompt the user to accept the contents of cell "b15" in worksheet "Metal Type" or enter a new string.
Also can it alert the user when "no matches were found"
I have a need to call a sheet from another sheet, copy two pages from the newly opened, existing sheet to a new workbook, search one sheet of the new workbook for any entries that don't contain a variable I specify, which is the first four digits of the field, and delete them. The sheets represent a daily view of jobs (identified by the "RptDate" variable) and an entire monthly list of jobs, which I want to delete all of them not from the date specified in the variable "RptDate". The fields in column A contain the date in "mmdd" format as the first four digits, and I have that specified in the "B1" celll of the originating workbook.
I found some code in this link Search Column Delete Row If Value Found that looked good. I had to modify it since I needed to search for a variable (RptDate) and needed to search column A and start on row 8 of that column. My current code looks like this:
Sub Macro1()
Dim RptDate As String Dim RptMonth As String Dim iLastRow As Long Dim i As Long RptDate = Range("B1").Text RptMonth = Range("B2").Text Workbooks.Open Filename:=(RptMonth) Sheets( Array(RptDate, "Total Database")).Copy iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 8 Step -1 If Left(Cells(i, "A").Value, 4) <> RptDate Then Rows(i).Delete End If Next i End Sub
It works fine right up to the "Rows(i).Delete" line. It errors out with a "run-time error 1004, delete method of range class failed" and highlights the above line. I know the fields I'm pulling the variables from are working right since it opens the correct workbook off of one of them.
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.
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.
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.
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.
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
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:
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)
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
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.
I would like to achieve is a Yes there are Duplicates & NO there aren't any in Column H. If Column A,B,C,D & E ALL Match & there is another Row that Exactly Matches A,B,C,D & E Then Return a YES or NO in Column H. Though I DO WANT ONE ROW TO STAY as I will Delete the Rest of the DUPLICATES.
The problem is a little complex, I have a large spreadsheet (10,000+ entries) and a smaller spreadsheet (100 or so entries). I merged them together, and now I need to find a way to delete all rows that are unique.
Reasoning: I accidentally deleted a few hundred product orders from our system, and need to find a way to re- import them from a backup SQL dump we have, but not the whole table
I have trouble in deleting the duplicate rows. I have a code to find the duplicate values
Public Sub Unique_Proj() 'This is the first step which takes out the unique projects from the base data 'Call Work_Assignment 'this selects the unique projects in the sheet Application. ScreenUpdating = False Dim A, E, B(), n As Long Sheets("Projects").Select With ActiveSheet A = . Range("g2", .Range("g" & Rows.Count).End(xlUp)).Value Redim B(1 To UBound(A, 1), 1 To 1) With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare For Each E In A If Not IsEmpty(E) And Not .exists(E) Then n = n + 1: B(n, 1) = E: .Add E, Nothing End If Next End With Sheets("Unique Projects").Select Range("G3:G" & Rows.Count).ClearContents Range("G3").Resize(n).Value = B
End With Application.ScreenUpdating = True End Sub
However, this gives me a list of values in that perticular column only.
What I want is either delete the complete row which is duplicate OR select the entire range of values which are unique (based on the column searched) and paste it in a new sheet. The second option is more better for me.
