Match & Delete (find And Remove Identical Rows)

Feb 24, 2009

I have over 2000 rows with 20 columns and i need to find and remove identical rows. I can do a match with MATCH() by concatenating the columns and comparing them as a whole but i need to apply this to the whole table and i need it to remove all the duplicate rows it finds.

View 5 Replies


Find And Remove Identical Entries In A List

Feb 13, 2007

I regularly have to add a few new lines to what is in fact a very simple data base I've had running for a long time in Excel. About 1200 lines now, one line per person. I add a dozen or so lines (i.e. people) at a time in a different colour.
When I re-sort the whole thing I run my eye down the list to spot partial double (i.e. duplicate) entries (the new ones in their own colour helps). Then I delete the double entries one by one. Pretty stoooopid, in'it?

How can I do this better, faster and more accurately with Excel to find just two duplicated data ( NAME and ID NUMBER) in a person's line (there are 15 columns altogether)? Or: Where and what can I search for (I've just spent nearly an hour trying to find an answer by myself, but don't really know what to search for)?

View 3 Replies View Related

How To Find Duplicate Rows That Are Not EXACT Identical

Dec 6, 2013

Is it possible to find duplicate rows that are not EXACT identical. Conditional formatting can find duplicate rows but they must be identical exactly(ofcourse)(even extra SPACE,COMMA,DOT wont find duplicates)

I have name and address(street address) columns. I don not want my invitation cards or mails go to same address twice.

As you can see in the file ..all 5 records belong to same address.(address entered in diff styles,some comma,space extra..etc) 4 and 5 are Son and Wife of 1st person. Row 5 and 6 have same text but one extra space in 5th row makes it unique so i need to eliminate such records with same address So can i set some formula so that duplicate rows will be highlighted if any 3-4 words match ?

Attached File : inwell.xlsx‎

View 2 Replies View Related

Identical Data Does Not Match

Feb 20, 2008

I have a list of data which doesnot match with my database and am hence failing to understand what to do.

2 sheets attached.

Was trying to find the employee ids of the advisors from the database.

View 9 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

Remove/Delete Rows By Color

May 7, 2008

I have a spreadsheet with multiple colored rows (grey rows are company headers, white rows are orders). I would like to find and delete any multiple company headers (i.e. grey rows followed by another grey row) and keep only the company headers that have orders (i.e. grey rows follwed by white rows). I have attempted the following macro but it doesn't seem to be validating the initial "If" since I keep receiving my "no good" ...

View 3 Replies View Related

Match Data From Two Almost Identical Lists

Aug 20, 2009

I'm working on two almost identical lists but because of their nature, some rows are repeated on one of the sheets and so on. I need help matching some cells onto the other sheet. I tried the VLOOKUP formula but it doesn't work since some rows are repeated... I need the match to link especifically a Name with it's correspondent Value, regardless of the position of the name in either of the sheets and whether it is repeated or not.

View 7 Replies View Related

Find A Match From Sheet1 In Sheet2 And Delete

Jun 12, 2014

Any way to do the following macro. I am trying to search column A on Sheet 1 and see if it finds the match on Sheet 2. If it finds the match it will delete the row on Sheet 2 and move all the information up and then move onto the next value in Column A in Sheet 1.


View 3 Replies View Related

Match Identical Numbers In Two Columns And Display Unmatched Results?

Feb 25, 2014

I have two columns with hundreds of identical data and i need to match in column A with an identical data in column B.

The tricky thing is there are many identical data. Each identical data needs to match with another identical data in column B and then, remove matched data.

So, i need to display whats left unmatched in column A and in column B.Match Identical Data.xlsx

Here is an example:

Column A


Column B


Ending results should be showing unmatched "Column A: 100" & "column B:500"

I also attached a excel.

View 1 Replies View Related

Macro To Copy Rows Based On Moving Date And Paste Rows Into Identical Sheet

Jan 28, 2014

I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)

e.g. 28/1/2014

I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.

View 9 Replies View Related

Delete Rows Where The Numbers Don't Match?

Mar 3, 2014

column "b" in the attached file has numbers that match some numbers in column "c" I need to delete all the rows that done match the numbers in column "c" that are used in column "b"

for example no " 53" is in column "c" but not column "b" so they need deleting

View 7 Replies View Related

Delete Rows That Match Criteria

Feb 2, 2009

I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.

View 5 Replies View Related

Find Identical Lists And Sum

Jan 15, 2009

I need a formula to sum column C if data in column A and Column B are found anywhere else in the list. For Example


Column D would show

View 9 Replies View Related

VLookup :: Find An Identical Value

Apr 20, 2006

=If(ISNA(VLOOKUP(B2, 'TableB'!$Y$2:$Y$100,1,FALSE)) , "",Column A in tableB)

I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B.

Also if there is 2 occurrences of column B in table B, i want the field in table A to show both values.

View 9 Replies View Related

How To Delete Multiple Rows But That Match In Columns

May 22, 2014


I am trying to delete rows that contain the same addresses BASED ON COLUMNS.

Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.

I want to keep the ones that DO NOT MATCH.


View 1 Replies View Related

VBA Delete Duplicate Rows If Entire Row Is A Match

Feb 25, 2014

I'm trying to put together a script which on the sheet "Resource" compares every cell in the range B:U, starting at row 8, and where all cells match, keep the first row, but delete the duplicate proceeding rows.

I've found the code below here: [URL] ...., which I thought I may be able to adapt, but for me to include all the columns in the 'If.Evaluate' section of code seems to perhaps not the most efficient way of doing this.

[Code] ......

View 5 Replies View Related

Delete Rows That Dont Match Criteria

Jun 19, 2007

I have a file which has some data I want to keep and some data I want to strip out.

The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove.

Example file shown below

> 1 year12341234123412341234

View 9 Replies View Related

Delete Rows Based On Partial Match

Aug 11, 2007

I currently have a speadsheet that looks similar to the following:

SVRW_67 784598 475395u
SVRW_34 fdsjsjdf 734978
CAT_56 ghdrhad gaghadh
CAT_67 48578 8943539

I'm trying to create a macro that will delete all rows with "CAT" in the REF column.
I've searched this site and struggled trying to adapt other methods listed and got nowhere.

View 3 Replies View Related

Delete Columns / Rows By Partial Match

Feb 19, 2008

i would like to find code for a macro(s) to delete selected columns and delete row by partial string..

due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..

View 8 Replies View Related

Find All Identical Dates On Worksheet

Dec 1, 2009

I need to find ALL occurrences of a date on a worksheet, but could not find a macro on our forum.
I found this macro on the web.

View 6 Replies View Related

Find Identical Item, Look For True 2 Columns Over

Feb 19, 2010

I’m putting together a blackjack odds calculator. I could use some help with a formula. If I have an A,7 (ace low) it needs to return a 0 (or false) for a “Possible Hand” because further down the column A,7 (ace hi) has a true “End Hand Total”

I was going to use an IF and a VLOOKUP (looking lower on the column), but then I cannot sort the column. Is there a formula that will find the hand and check for true 2 columns over from identical hand. There are 169 starting hand combinations. The attached worksheet only has 31 – for simplification.

View 2 Replies View Related

Tables Must Have Identical Rows

Jun 7, 2007

i have got two tables each on a different sheet. The column headings are not the same but in both tables column A contain names. Users can enter names in column A in the first table, and these should be copied to the second table. However, users can insert rows in the first table which should automatically be copied to the second table to ensure the tables haven an identical number of rows (names)

View 2 Replies View Related

Delete Rows If Column Has Fewer Than X Matches To Part String Match

May 5, 2008

I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.

Data set example:

In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.

Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete

I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.

View 6 Replies View Related

Find And Delete Rows In Vba

Mar 15, 2007

Basically i have a column which runs like this:

Company Name:


Basically, what I am after doing is getting a script to look through that column and delete any rows that do not have "Name1" in Column A.

So far, i've sorted the column so all the blank cells appear at the top, and the "Name1" cells appear at the bottom. Now what I was going to do was use a loop, and I got as far as this before becoming confused .....

View 3 Replies View Related

Find Value Then Delete All Rows Above

Jul 18, 2007

Looking for some VBA code that will look for the first instance of a certain value in Column A, (Begins with 9128),then delete all rows above that row. Then I need to look for a value in Column A that begins with 9129, and delete all rows below that. I've attached a sample workbook to illustrate.

View 3 Replies View Related

Deleting Specific Rows That Are Identical

Mar 8, 2012

I want to delete some rows that are identical. Aside from creating a macros to do this, is there a way to find a row with a specific criteria and delete it if value returns TRUE?

View 1 Replies View Related

Sum Identical Rows Meeting Criterion

Aug 10, 2007

In each row of my data set, columns A thru E contain different three-digit numbers, in ascending order (e.g. 012 312 344 798 880). No number is repeated more than once in a single row, and no two rows contain the same set of five numbers. Column F contains a value (these are what I'd like to sum).

The variables I want to create a formula with are each three-digit numbers, call them X and Y. I want to find the pairs of rows where one row contains X in one of the first five columns, and the other row contains Y and the same other four values as were contained in the four non-X cells in the X-row (note that X and Y may or may not be in the same column, since each row is in ascending order). If X=344 and Y=955, an example of a match would be one row with 012 312 344 798 880 and another row with 012 312 798 880 955. For each X and Y combo there could be many such matching pairs of rows (with different sets of the other four values). I'd like to sum all the values in the X rows of such pairs (and then I'd do the same with a different formula for the Y rows).............

View 3 Replies View Related

VBA Find Blank Row And Then Delete 15 Rows Down?

Sep 11, 2013

I need to loop through data when it finds a blank it need to delete that row and 15 rows down and loop through whole data set find blank row and delete 15 rows down.

It's been years since I did any VBA, and I forgot. Do I need to use offset to acheive this task?

View 6 Replies View Related

Find A Word And Delete All Rows Above It

Dec 18, 2013

I get a report each week that has a bunch of unwanted header info. It used to be static and the macro just deleted the first 49 rows. I recently found out that the header format changed and it's been deleting crucial data.

I need something that will find the words "Medical Center" and delete all the rows above it, however many that may be...

View 4 Replies View Related

Find Value In Column & Delete All Rows Below

Feb 23, 2009

I have searched high and low for a more efficient macro than the current loop i have. I have a range that varies in size but always follows the same format. After i reach a certain value output from a formula, "4", I want to delete all lines below that line. So I want to find value "4" then move one row down and delete all lines to line 2004. I have attached the macro that i compiled but it takes a couple of minutes to run.

Sub manual()
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
With ActiveSheet
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView.................

View 2 Replies View Related

Copyrights 2005-15, All rights reserved