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
ADVERTISEMENT
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
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
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
Aug 19, 2014
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx
View 1 Replies
View Related
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
Oct 28, 2009
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:
If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
View 5 Replies
View Related
Jun 17, 2014
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
or
A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
View 5 Replies
View Related
Jun 12, 2008
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
View 3 Replies
View Related
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
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
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
Nov 27, 2007
I have a list of about 1,000 rows with multiple columns. Each row represents a unique person and the columns represent codes that were assigned to them. I have merged two sets of codes together to create this list, so some people (rows) may have duplicate codes. I would like to be able to delete all duplicate values from each individual person (row) so any given code only shows up once for a person.
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
Jul 6, 2007
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.
View 3 Replies
View Related
Oct 19, 2007
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
View 9 Replies
View Related
Jun 4, 2008
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.
View 7 Replies
View Related
Dec 21, 2012
I am looking for some VBA code that will look through a column and delete all but the last duplicate value. The rows are not to be deleted. The duplicate also may not be consecutive (i.e they may appear in rows 2, 3 and 72 - the one in 72 would need keeping).
I did find the following macro which seemed to do the job until I realised it wasn't working if the values weren't consecutive.
Code:
Sub Dupe_Killer_Keep_Last()
Dim lrow As Long
Application.Calculation = xlCalculateManual
[Code]....
View 9 Replies
View Related
Jul 25, 2007
I have a column of data where duplicate records appear, can I get a macro to review the column and delete the duplicate record, I need the line item number to still show although the serial number has been deleted, please see example below-
line_number Serial_number
14FM12A1QWINC-1A
14FM12A1QWINC-1B
14FM12A1QWINC-1C
14FM12A1QWINC-1D
14FM12A1QWINC-1E
14FM12A1QWINC-1F
14FM12A1QWINC-1G
14FM12A1QWINC-1H
14FM12A1QWINC-1I
14FM12A1QWINC-1J
14FM12A1QWINC-1Jdelete serial number in column B, keep line number 1 in column A
24FM12A1QWINC-2A
24FM12A1QWINC-2B
24FM12A1QWINC-2C
24FM12A1QWINC-2D
24FM12A1QWINC-2E
24FM12A1QWINC-2Edelete serial number in column B, keep line number 2 in column A
34FM12A1QWINC-3A
34FM12A1QWINC-3Adelete serial number in column B, keep line number 3 in column A
44FM12A1QWINC-4A
44FM12A1QWINC-4Adelete serial number in column B, keep line number 4 in column A
View 9 Replies
View Related
May 28, 2013
I got 2 sheets one with Employees name in col A number of hours worked in col B. Second sheet is download form time clock which has Name in Col A and Hours worked in Col B and other details in other columns. I want MACRO to delete matching rows depend on Col A and Col B .
View 9 Replies
View Related
Jul 28, 2013
with deleting duplicate entries in a spreadsheet that contains two names, a code number and a date. All rows with a duplicate number in column “B” should be deleted. Write a micro code? I have a few thousand of entries that need to be knocked out.
A sample file is attached : Example.xls
View 5 Replies
View Related
Feb 18, 2010
I need to filter the following file.
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
View 2 Replies
View Related