Macro To Delete Duplicate Values But Keep Last One?
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
ADVERTISEMENT
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
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
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
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 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
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
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
Dec 17, 2009
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
View 3 Replies
View Related
Oct 27, 2008
I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.
I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet can the entire row be deleted from the Outstanding sheet.
View 9 Replies
View Related
May 16, 2006
I use the following macro to delete the columns with duplicate dates in it. It works ok but when a date is duplicate more than once – say 4 time – it only delete 2 of the 4. I want it to delete all duplicates (entire column) and only leaf the last one to the right.
Sub DeleteExtraDates()
Dim c
For Each c In Selection
If c = c. Offset(0, 1) Then
c.EntireColumn.Delete
End If
Next c
End Sub
View 6 Replies
View Related
Apr 21, 2009
See attached doc "Find and Delete.xlsx". Inside, the value in F12 is a very important cell. Using the text value from F12, I first want to use visual basic code to find the range in column B because it will vary. This means the 'text words' column might have 50 words or it might have 2000. I have racked my brain trying to find a universal way to find a variable range.
Then I would like to search for the exact text value within this range in column
B. For example, in F12 is the text word 'windows xp registry cleaner' and you can see that this value is in B9. There will only be one exact match in column B.
I then would like the vb script to delete and remove this found value, then move left one space in column A to also delete the ID number associated with the text word. Then shift both rows up to fill the empty gap. I have tried numerous different codes but to be honest, I'm not really sure what I'm looking for. I've tinkered with find and replace, vlookup, match, find, search, and many more.
View 2 Replies
View Related
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
Dec 21, 2013
I have one sheet that have +/-5000 data in 4 table ... all table is fill with just only 2 column, first column is Code Number and second column is Quantity.
There are many duplicates of different entries in Column 1st in all table range and I need to add up their quantities to new table in new sheet...
This is for example:
(Sheet1 name="INPUT") (table1=A:B) (table2=E:F) (table3=I:J)
A B E F I J
AB123 100 ABC123 20 AA1 10
AC278 50 BCD547 30 AA2 10
AB123 30 FGE988 30 AB3 10
DE586 120 ABC123 50 AA2 10
RE158 80 BCD547 120 AA1 10
DE586 50 ABC123 70 AD4 10
Then I want to use macro vba to create new sheet(Summary Report),copy,paste,sort and sum all duplicate...
It should look like this
(Sheet1 name="Summary_Report")
A B E F I J
AB123 130 ABC123 140 AA1 20
AC278 50 BCD547 150 AA2 20
DE586 170 FGE988 30 AB3 10
RE158 80 AD4 10
View 9 Replies
View Related
May 2, 2008
I have a VBA Macro that loops through about 100,000 rows in an Excel file and removes rows that have a duplicate cell value. The macro takes about an hour and a half to run. Are there any ways to make the Macro run faster? Any ways in general to make VBA macros run faster?
View 9 Replies
View Related
Oct 13, 2006
I have data that looks like this:
day# id amount
1 56575 0
1 56675 0
1 56680 0
1 56683 0
1 56681 0
1 51810 0..............
How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....
View 2 Replies
View Related
Feb 20, 2009
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
View 2 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
Apr 13, 2013
I need a User Defined Function (UDF) to Vlookup duplicate values in separate Columns.
I can't use an Array function as the data is huge and it takes lot of time to calculate.
I have attached a Input and the desired output in the file attached.
UDF_MACRO.xlsx
View 9 Replies
View Related
Jan 14, 2014
I am currently working for data validation team. I have to validate tons of data every week so it would be great if i could remove dupicates.
I want to compare two worsheets, sheet 1 has the order number with description (which i have to find) for the current month and sheet two has got standard report from the past week giving all the order numbers with no description. I need a VBA based macro to find all matching order numbers which are in sheet 1 column A, in sheet2 column A and return the corresponding value in column B in sheet 2 and also return the description in Column C.
View 6 Replies
View Related
May 4, 2006
I need a Macro that Copies all the duplicate values in a column (and their corresponding rows) over to a new spreadsheet.
For example,
1 A B C D E F G
1 Z X Y D R M T
2 B 5 7 8 9 0 4
Because 1 is a repeated value in the first column, the macro copies the first two rows over to a new spreadsheet.
View 8 Replies
View Related
Jan 27, 2014
I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
View 2 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
Sep 4, 2008
Macro to delete a row if it contains a particular value in a column. Say for example I have the following:
a b c d
1 x
2 x
3
4 x
I want to be able to put into my macro code that says delete all rows that contain an X in column B. So this would leave just Row 3 remaining.
I could also sort this data so that I get:
a b c d
1 x
2 x
3 x
4
That way I just have to delete all the rows less than and equal to 3. Ultimately I'm trying to get a way for the macro to copy the rows that DONT have a value in a particular column. But I figured it might be easier to find a macro which would delete.
View 9 Replies
View Related
Jun 24, 2006
Column A Column B
1 b
1 1
1 2
3 4
I need a macro that if value in column b matches with value in column a, delete it both the value in column b and a and put the deleted value into column c. now my value in my columns is a combination of numbers and letters and it can have this characteristic too: `2076 or `FI7890
View 3 Replies
View Related
Mar 28, 2009
I need some assistance with a simple macro. Im clueless when it comes to macros.
Heres what I have:
column "e" contains the year built for homes. Some of the fields contain "9999" or "0000" or blank. I need to delete these entire rows. If the year is between 1900 and 2020 I would consider it valid and keep the row.
So I need a macro that says something like this:
if column "e" is not between 1900 and 2020 delete the entire row.
View 11 Replies
View Related
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
Jun 11, 2014
Need a Macro to delete all rows containing values less than or equal 300 in column A
View 5 Replies
View Related
