Deleting Duplicate Rows :: Compare 2 Cells Per Row
Sep 22, 2008
I have a spreadsheet that I need to delete duplicate rows in. However, in order to determine if a row is a duplicate I need to check 2 cells per row. In the attached file you will see that each row has 4 cells. I need to compare the cells in columns B and C with the B and C cells of the Row beneath. If the B and C cells match then it is considered a duplicate and one of the rows needs to be deleted. Also, there may be multiple duplicate rows.
For an example see rows 17, 18 and 19. I only need 1 row to remain.
I am looking for a vb script that would analyze a file with thousands of rows and delete the duplicates.
View 12 Replies
ADVERTISEMENT
Dec 17, 2007
I have a long list (over 1000 lines) of numbers, some of which are duplicated. Up to now I have been sorting them, adding in a simple check column to see if the number is a duplicate of the one below, and then manually deleting this cell.
Is there a better way to do this using VBA? The only way I have managed to find is by deleting the entire row, but I cannot do this as I have additional data to the right that I need to keep.
I also cannot install any add-ons as this on a work computer.
View 9 Replies
View Related
Feb 4, 2014
I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).
As filter I would like to be used A,B,C columns.
EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
View 1 Replies
View Related
Oct 14, 2009
In my Excel 2003 worksheet I need a macro to search column B for duplicate entries. The data in column B is both numeric and string. If there are any duplicate entries, then I need the duplicate rows to be deleted.
View 4 Replies
View Related
Aug 5, 2007
I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.
When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go
Name-----number
Dave 555-1212
Dave 555-1212
John 536-2343
Smith 423-2312
needs to become
Name-----number
John 536-2343
Smith 423-2312
I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows,
View 10 Replies
View Related
Aug 8, 2007
I found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]
Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?
For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.
View 9 Replies
View Related
Aug 6, 2012
I have a sheet whereby in column A i have company names and column B how much is invested in this company so:
A B
AAPL 10
GOOG 5
MSFT 15
APPL 5
IBM 20
GOOG 10
What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.
View 9 Replies
View Related
Jan 16, 2008
I have many rows that contain some common data but I need to be able to select the row with the most recent data and delete the others. For example, a car part is assigned a unique number which never changes, it may be ordered several times and in different quantities, for each car part I want to select the most recent order and delete any others. Is there a simple way of doing this?
View 9 Replies
View Related
Jun 26, 2009
In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.
the row of numbers looks like this ....
View 9 Replies
View Related
Nov 28, 2013
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
View 1 Replies
View Related
Jul 30, 2009
I need the macro for deleting duplicate rows only it should keep one lowest value row.
for e.g.
Column A Column B
TS1234 100
TS1234 50
TS1234 200
Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.
View 12 Replies
View Related
Mar 19, 2014
Below is an example of an Excel sheet I'm working with: [URL]
Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.
So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.
Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.
Can this be achieved by Conditional Formatting or Macro or VBA?
View 1 Replies
View Related
Jul 30, 2014
The following code compare two sheets and remove the duplicate rows to a new sheet. In this code I have to create the third sheet manually.
I need the "New Sheet" to be create automatically with the name of "Dup".
Code:
Option Explicit
Sub nomatchnoty32()
Dim X, i As Long, j As Long, Y(), Dic As Object, k&, Z, name1$, name2$
Const shSource1 As String = "sheet1"
Const shSource2 As String = "Sheet2"
[Code] .........
View 6 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 14, 2014
I am new to macro and just trying to learn. I have a spreadsheet with 20000 rows and 8 columns. I am trying to identify equal rows based on the values of columns C, D, E and F. then I need to separate equal bunches with a blank row. Then I need to copy the ID number from the first cell of column B of each bunch and paste it for the rest of the rows in that bunch. I have written the following code but this does not put the ID of the first cell in a bunch for the rest of the rows in that bunch.
[Code] .........
View 10 Replies
View Related
Sep 1, 2008
I am trying to a method to delete rows that contain EL or LE in a specific cell in a set column as this will reduce my file down by nearly a third. Currently by doing it using a autofilter it kills my computer as there is so much data. Is there an easy way to reduce this?
Trade ID456465ELColumn B456456LE Column B8454321LEColumn B456456ELColumn B5464564LColumn B4564546LEColumn B
View 9 Replies
View Related
Apr 8, 2014
I have a normally easy task that is causing me some grief. In column f of my data I either have a number or this "____________". My goal is to delete the entire row if that line is present but my code is not finding that value in my range.
I am wondering is this some format value or something, but it shows up in the formula line as a line. Doesn't appear to be an underline, but I could be wrong. Below is my code.
[Code] ........
View 9 Replies
View Related
Oct 27, 2007
Deleting rows that have dependent cells.
I have one sheet that uses a very simple formula for copying certain values from one cell to another sheet “=VALUE(Sheet1!B5)”.
My problem is that rows have to be deleted in sheet1 as the information eventually becomes expired and the following is displayed in my cell in
sheet 2 “=VALUE (Sheet1!#REF!)” Is there a work round this, to prevent the formula showing a #REF! Value.
Row 5 is replaced and the same formula has to be re-entered. Excel 97 used in our office unfortunately.
View 10 Replies
View Related
Nov 29, 2009
I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want.
Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.
View 9 Replies
View Related
Apr 17, 2013
I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.
Here's an example of what I'm working with:
5
10
15
20
50
2
4
6
8
20
This is what I need the final product to look like:
5
10
15
20
50
2
4
6
8
20
So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?
View 5 Replies
View Related
Aug 3, 2008
I was trying to create this certain tool using excel which queries data from database. I was successful in obtaining the data I need and paste it to my spreadsheet, however, there are some requirements I cannot create a solution with. Below is a description of my Excel file (which is also attached):
- There are 9 columns on my spreadsheet
- Number of records per data refresh are indefinite, for information in database are constantly being updated
- Several cells on the first column have exactly the same information
The requirements I cannot do are:
- For all cells on first column that have similar values, their corresponding data on column 5 should be concatenated
- Delete rows that have same data for column 1 except the first record found, place concatenated data on column 5 of first record
Example:..................
View 4 Replies
View Related
Feb 14, 2013
I want to establish a link from my worksheets. Using the conventional link method I was able to link the values from my first worksheet to the second worksheet. My problem is when I delete a particular row. The reference of the second worksheet will have an error #REF! since I deleted those cells. Is there any way that I can link my two worksheets without any error that even if I deleted a particular cell/row the reference is still intact?
View 2 Replies
View Related
Dec 12, 2013
Whenever you insert / delete row or cut cells out in Mon/Tue sheets it's causing issues in the table in the Weekly view tab.
Is there any way to prevent it? I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner.
I gave you an idea by deleting a row range in Mon tab which causes #REF error.
View 9 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
May 1, 2012
I have a sheet which has the following Columns:
Name..........Sales..........Returns..........Net
These are columns B,C,D,E,
Because the data is imported, it shows one row for the person's sales and another for their returns like this:
Dave...........100...............0...............(Blank)
Dave..............0.............-20..............(Blank)
Fred............200...............0...............(Blank)
Andy..............0............-500..............(Blank)
What I am trying to do is get the net sales of 80 in either of Dave's rows in the Net column, then I can hide the duplicate row. However, this is complicated by the fact that names without returns, (like Fred in this example,) do not have a second row. (This also applies to those names without sales, but with returns, like Andy).
View 6 Replies
View Related
Nov 12, 2013
I have a report with about 7000 rows in it. I need a macro that will find all rows where column A and column B are the same as another rows column A and column B and delete both rows.
View 4 Replies
View Related
Apr 26, 2008
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.
(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)
To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.
I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
View 4 Replies
View Related
Apr 29, 2013
VB:
Sub aa()
Dim roow As Long
Dim i As Long [code]...
View 3 Replies
View Related
Oct 7, 2008
I have 2 columns A and B.
There are 1000 records in column A and 500 records in column B.
I would like to compare both columns for duplicate entries and deleting all instances of those record in both columns, leaving behind records that were not duplicated to begin with in their respective columns.
View 5 Replies
View Related
Oct 29, 2007
I have a spreadsheet in which invoices are listed. Some of these invoices have been cancelled with a negative invoice, so I want to match the negative invoice to the corresponding positive invoice so that accurate averages of all the data can be attained.
RefSurnameNameMonthTimesheet Rate PHValue
24963DoeJohnJun-0712-Jun-0719.5975.15
30693DoeJohnJun-0720-Jun-0719.5975.15
30693DoeJohnJun-0725-Jun-0719.5975.15
30693DoeJohnJun-076-Jul-0719.5975.15
30693DoeJohnJul-079-Jul-0719.5975.15
30693DoeJohnJul-0713-Jul-0719.5-975.15
41270DoeJohnJul-0713-Jul-0720.761120.95
30693DoeJohnJul-0726-Jul-0719.5-975.15
41270DoeJohnJul-0726-Jul-0720.761120.95
30693DoeJohnJul-076-Aug-0719.5-975.15
41270DoeJohnJul-076-Aug-0720.761120.95
30693DoeJohnAug-076-Aug-0719.5-975.15
41270DoeJohnAug-076-Aug-0720.761120.95
41270DoeJohnAug-0710-Aug-0720.761120.95
View 9 Replies
View Related
