Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Match & Delete (find And Remove Identical Rows)

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Find And Remove Identical Entries In A List
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 Replies!   View Related
Identical Data Does Not Match
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 Replies!   View Related
Match Data From Two Almost Identical Lists
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 Replies!   View Related
Remove/Delete Rows By Color
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 Replies!   View Related
Remove Or Delete Duplicate Rows
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 Replies!   View Related
Delete Rows That Match Criteria
I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.

View Replies!   View Related
VLookup :: Find An Identical Value
=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 Replies!   View Related
Find Identical Lists And Sum
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 Replies!   View Related
Delete Rows That Dont Match Criteria
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 Replies!   View Related
Delete Columns / Rows By Partial Match
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 Replies!   View Related
Delete Rows Based On Partial Match
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 Replies!   View Related
Find All Identical Dates On Worksheet
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 Replies!   View Related
Find Identical Item, Look For True 2 Columns Over
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 Replies!   View Related
Tables Must Have Identical Rows
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 Replies!   View Related
Sum Identical Rows Meeting Criterion
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 Replies!   View Related
Delete Rows If Column Has Fewer Than X Matches To Part String Match
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 Replies!   View Related
Deleting Duplicate Rows When ALL Columns Are Identical
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 Replies!   View Related
Find And Delete Rows In Vba
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 Replies!   View Related
Find Value Then Delete All Rows Above
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 Replies!   View Related
Find Value On Sheet & Delete Rows Below
I am trying to select all the data in every row below where the first "flat" entry in the worksheet is but it will only go to the bottom of the data in the column where it finds flat. Some data in other columns extends beyond this so it is not working for me. It would work for me to select everything from where it finds flat to the very last row 65536 but I can't figure out how to do that with what I have. I am then deleting all this data.

Cells.Find(What:="flat", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select

View Replies!   View Related
Find Text & Delete All Rows Below
I have a report that comes in a txt file. After importing into excel i am left with a bunch of garbage that i dont need. This report is anywhere from 5-15 pages depending on how much product was made that shift. I only need the information off of the 1st page. My question is how can i Find the first occurance of specific text (Site) (will be in column A) and have it select that row and all rows below and delete them.
My biggest issue is the first page can be anywhere from 40-60 rows so I need to find the text (Site) and delete everything below it, which could be anywhere from 500-1500 rows.

View Replies!   View Related
Find Value In Column & Delete All Rows Below
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 Replies!   View Related
Find Macro To Delete Rows: 3 Questions
I have a worksheet that is several thousand rows long. It is sectioned into various "topics" (e.g. Revenue, Profit); each topic (there are about 100) has 10 companies. The 10 companies are the same for each topic. For instance, if 2 of the companies are Microsoft and Intel, then there will be a Microsoft row and an Intel row within each topic.

I am trying to write some code to delete every instance of a company (the user indicates the company in a form). So, for instance, if I want to delete Microsoft, the user would enter the stock ticker, MSFT, into the form and press the appropriate command button. When the command button is pressed, the code should find each row that contains the ticker MSFT (the tickers happen to be in column B) and delete it.

I have written the following unsuccessful
Private Sub CommandButton1_Click()

Dim Ticker As String
Ticker = TextBox1.Value

'If IsText(Ticker) Then


Cells.Find(What:=Ticker, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate..........

View Replies!   View Related
Auto Delete Rows To The Top Of The Sheet After Find
1) Highlight column A, and do a search on the word "Item"
2) Once it finds the word item, move 2 rows above it, and delete all rows above the word "Item"

The word Item at times can be on any row, and its very random, but its always in column, A. I tried doing it with a regular macro, but it seems to remember the row that I used, rather than two rows above the word "Item".

View Replies!   View Related
Find Rows Containing Specific Data And Delete
I have a large report that I pull each month listing which products we sold last month. There are certain products I don't want included in the report. Until I can have the report re-written, I'm looking for a macro or a function that will find all the rows that contain these product numbers in column M and delete the entire row. The product numbers I need to delete are always the same, but some months one or more of them might not be in the report if they weren't sold.

In Summary:

Find in column M the product numbers:

Delete those rows containing the product numbers above

View Replies!   View Related
Find The Last Row Without Data In Columns E And F And Delete The 10 Rows Below Them
I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?

View Replies!   View Related
Find Text In Column & Delete All Rows Below
I am trying to create a macro that will delete everything below a certain word.

The code I tried to use is the following. This produced an error:

If Range(Cells(1, 1), Cells(180, 1)). Find(" Interest Expense").Row = 0 Then
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" NET INCOME").Row + 1
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" Interest Expense").Row + 1
End If
Rows(iStart & ":" & FinalRow).Delete shift:=xlUp

View Replies!   View Related
Find All Instances To Delete X Number Of Rows
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.

Sub cleancsv()
lastRow = Range("A1").End(xlDown).Row
pmpt = InputBox(Prompt:="What text are you looking for?", _
Title:="Text", Default:="i.e.: Finished Goods Inventory")
numrows = InputBox(Prompt:="How many rows to delete (counting original):", _
Title:="Number of Rows", Default:="i.e.: 1")
Set cell = Range("A1:A" & lastRow). Find(pmpt)
If Not cell Is Nothing Then
For l = cell.Row To cell.Row + numrows
Range("A" & l).EntireRow.delete
Next l
End If

View Replies!   View Related
Find Data And Delete Remaining Rows (For Each Loop)
In the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.

View Replies!   View Related
Macro - To Delete Whole Rows After We 'control' Find Something From A Specific Column
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...

View Replies!   View Related
Macro To Find Match Delete It From The Cell Display It In Different Cell
I have a huge database. I am looking for a macro that can search for a particular word. If a match is found, the macro should do two things simultaneously... (i) delete the contents of the cell in which the match is found........(ii) display the same contents 1 row below and 1 coloumn to the left of this cell where the match is found. For e.g....

View Replies!   View Related
Find And Match: Delete Any Row On The Old Side That Is Not Contained On The New Side
i have 2 excel worksheets. One of them contains over 20,000 rows of entries (old side) and the other contains about 900 rows of entries (new side). Both contain the same 9 column headings. I've shortened the list in my workbook example.

What i need to do is excel to delete any row on the old side that is not contained on the new side. I don't need the rows to match fully but the column A's (Doc number) are what i want it to check against. I've included a summarised version of the database and the old and new are separated by a heavy blue line. Need to be left with the same rows on either side of that blue line at the end.

View Replies!   View Related
Delete Multiple Rows :: Match In Multiple Columns
I need to find all rows that have columns that match in all 3 of the columns.

I then need to delete all but the last row in each "group" of rows.


1 q w e
2 q w r
3 q w r
4 q w r

Delete rows 2+3

View Replies!   View Related
Find The Data In Some Rows That Match With One Cell And Automatically Fill The Data
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni

View Replies!   View Related
How To Delete Rows Using A "FIND" Formula
I have a ColA that houses Customer Names...

I need to delete any ROW that doesn't have a customer name SOMEWHERE in the ENTIRE Customer Description...

Say this is the COL A, """Campbell Soup/CA (Queensway)"""
if "Campbell" is in row... then DON'T Delete....

i was thinking how the =find() formula gives a number signifing a "find" within a text, but I don't know how to use that in VBA.

View Replies!   View Related
Copy Rows To Worksheet If Cross Match & Another If No Match
I have 2 worksheets named sheet1 and need_to_delete that are in the same format: 7 digit number, 5 digit alpha numeric, text, dollar amount. I need to copy every row into sheet2 where there are no matches in the column A of the two source worksheets, and copy every row that does have a match in column A into sheet 3. I also need to keep the rows in their current order.

View Replies!   View Related
Match Cells & Shift Rows Down If No Match
Attached is a spreadsheet of 2 tables from B:1 to V:5
I need to shift either rows down if cells from column B do not match column M and vice versa.

How would it be possible to acheive results as shown in B:10 to V:15 through VBA.

View Replies!   View Related
Find In VBA Code: Find Exact Match
I'm using the below VBA

Dim c, DataRow
With Data
Set c = . Range("A5:A350"). Find(KPI, LookIn:=xlValues)
If Not c Is Nothing Then
DataRow = c.Row
End If
End With

Now,If KPI is for example = "Favourite Hotel" and if i have data that looks like :

Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham

Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).

How do i make it search for an exact match?

View Replies!   View Related
How To Remove Ws Create/delete Residue From A Wb
My first VBA project continues, and what I thought would be a short-term diversion is becoming a career.

I have a couple of places where a worksheet needs to be built from scratch. I thought I was being a good programmer by deleting the sheet and creating a fresh new one each time (Worksheet.Delete and Worksheets.Add). Not.

I just happened to run across a post that referred to "residue" that's left in the workbook when you delete a worksheet. Thanks, mikerickson. I noticed the workbook size increasing, and it seemed to be increasing too much for the code and controls that I was adding as I developed this thing, but then that's Microsoft for ya.

My workbook is now at 400KB and I don't even have much data in it yet. If it exceeds 500KB, I have to upgrade a free Web hosting account to a paid account (max file size is 500KB for the free account).

Therefore, in addition to wanting to be a good programmer, and wanting to save disk space, bandwidth, and download time, I also have a financial interest in removing all that "residue". Emptying my recycle bin, you might say, except that I can't recycle this particular trash. That's now #1 on my to-do list.

View Replies!   View Related
Delete Or Remove The Unwanted Pages
I have a 'document' which shows 294 pages, I have only 10 actual pages. How
can I delete or remove the unwanted pages?

View Replies!   View Related
Remove Duplicates And Delete Cells...
I am using the following code to grab installed software on a remote computer through a macro in Excel 2007. I don't have the entire code I'm using as the majority of it works, this section here though is where I'm having problems.

View Replies!   View Related
Remove Autofill And Delete Data
I have used the following VBA quotes for my workbook to remove auotfills and data that I want to remove from my worksheet. The workbook has multiple worksheets and the worksheet that I want to use this VBA on is on worksheet 10 (ie. Sheet10). what's wrong with the VBA codes that I have below? Currently nothing happens whenever I try to click on the button that's linked to this code and there's no error message.

Sub ClearStuff()
Dim rng As Range
For Each rng In Sheets(10).Range("C18:BV" & Sheets(10).Range("B65533").End(xlUp).Row)
If rng.Value = Sheets(10).Range("D11").Value Then
rng.Interior.ColorIndex = xlNone
End If
Next rng
End Sub

View Replies!   View Related
Delete Button To Remove Cell Value From Listbox
I have a list box that shows a range of cells (called emailrange). I want to add a Delete button that enables a selected value in the Listbox to be deleted if necessary (just the cell value not the row).

The code I have thus far is below: ...

View Replies!   View Related
Cell Deletion (delete/remove All Non Use Cells)
is it possible to delete/remove all non use cells
for example if i only want the cells A1 THROUGH TO O31
all the rest removed??

View Replies!   View Related
Delete/Remove Picture From Image Control
I want to delete a picture from an image control in a worksheet when pressing a button. So my code is:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Image1.Picture = LoadPicture("")
End Sub

Well, the problem is that this code only works when I create it. If I save and close the excel file when I open it again and I press the CommandButton1 I get the following error: Run-time error '-2147417848(80010108)' Method "Picture of object "IImage" failed.

View Replies!   View Related
Delete & Merge Columns,Delete Rows With Filter, Etc
1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

respectively )

View Replies!   View Related
Is There A Method To Globally Delete/remove All Comments From A Worksheet?
I'm attempt to automatically validate QTP spreadsheets with an EXCEL VBA macro. All is going well but I now want to look smarter. Is there a method to globally remove all comments from a worksheet?: D What I actually want to do is add comments to cells which fail validation but some of these cells may already contain comments. If they do contain comments then I want to overwrite with my comments. I thought the easiest way would be to delete all comments at the start of macro but I am all ears to any other approach.

View Replies!   View Related
Delete Row If Two Corresponding Cells Match
I am trying to make a VBA code that will delete an entire row if two cells match. An example would be if F4 and G4 are equal then delete row 4.

View Replies!   View Related
Find And Replace (remove / Slash)
I have:

what I want is to basically remove the "/" after the .com but keep it on the second one.

So final result should be

when I do find and replace of "/" with a blank it makes it www.abce.comdef, which is not what I want.

View Replies!   View Related
Find And Remove Character In Variable
I am trying to remove the last 6 character from a variable. I worked out how to do it using formula's in XL, but want to move this in to VBA. Currently I have this

Mid(ActiveSheet. Name, 1, Find(" Intrdy", LookIn:=ActiveSheet.Name))

however it does not like the find function. Does it exist? am I using it correctly?

View Replies!   View Related
Copyright © 2005-08, All rights reserved