Delete Matching Cells

Aug 1, 2007

I have four columns of info. Two are check #s and amounts from the bank and two are check #s and amounts from a database. How can I delete check #s(along with their amounts) that match? Is there any way to detect check numbers that match but amounts that don't?

ADVERTISEMENT

Delete Matching Rows

Jun 17, 2009

Situation: I would like to compare the information between two worksheets and delete the rows that contain the same data in multiple columns, on a row by row comparison.

IE: I have two worksheets, each have identical row headers, with 5 columns each.

Company Load Date Load Time Load Description Amount ?Report?
Store#44 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
Store#44 5/14/2009 02:34:21 AM SLATOUR $20.00 WS1
Store#45 5/14/2009 01:55:41 AM GCHANDLER $100.00 WS1
Store#46 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1

If column A(Company), B(Load Date), and E(Amount) for record 35 in worksheet one, match the same columns for a record in worksheet two, both records are deleted/highlighted/marked with an x in an additional column/anything.

Alternately, I can combine the data in both worksheets into one large worksheet, if that would make the solution easier. And or adding a column that idenifies which record came from which report.

Basically I have two similar reports; each contain a few rows of transactions that the other does not, I need to separate the matching transactions from the unique transactions, in order to balance the two.

I have tried using the Remove Duplicates function but it saves one of the matching records (they should add an opiton to delete matching records aswell keeping only truly unique records), I dont understand how to work Conditional Formatting to get it to do what I want, I dont know macros, or vlookups.

View 9 Replies View Related

Macro To Delete Matching Values

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

Delete Rows Not Matching Criteria

May 11, 2007

I have been using the code found here

Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria

On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................

to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.

View 3 Replies View Related

Find Matching Value In Range On Different Sheet And Delete?

Jan 28, 2014

I am an inventory specialist for a dish network company and as such I track inventory in and out of technicians vans, both serialized and not. I've done a great deal of work updating a broken excel sheet they use so that it functions again but I didn't build it. I've learned a lot but I'm only self taught with Excel and had never even heard of VBA code until I dived into this project. It's a huge puzzle and is now my "baby".

Anyway, basically I have one sheet that has a list of all the items I need to keep track of. One section of this Sheet1 I've designed to have cells with dependent drop down lists that are Named Ranges on Sheet2. The tech can choose item A B or C in the first dropdown box and then the next cell shows only the serial numbers from the named range on Sheet2 of A B or C. (Was that english?)

Since the receiver comes out of the techs van once its used I want to figure out a way to delete the serial number that the tech has chosen without deleting the row or cell, just the value in it so that it can then have another serial number typed in. How can I do that?

Also, since I'm here, my 2nd drop down list seems to always start scrolled down and I have to scroll up to see my serial numbers. Why is that? The receiver list starts at the top but the dependent one doesn't...

View 2 Replies View Related

Search For Matching Text, Then Delete Rows Above It

Jul 8, 2009

Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:

ExampleCell1
ExampleCell2
ExampleCell3
UID = example

ExampleCell1
ExampleCell2
ExampleCell3

ExampleCell1
ExampleCell2
ExampleCell3
UID = example


That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".

Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.

Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.

View 7 Replies View Related

Find Cell With Matching Text And Delete?

Dec 28, 2013

I'm looking for code to find a copy of the text in cell AH3 in a range AH7:AH100 then delete all copies of the text in that range AH7:AH100 leaving other cells in that range which contain different text intact.

View 3 Replies View Related

Find Matching Value In A Range On Different Sheet And Delete

Jan 18, 2014

I am an inventory specialist for a dish network company and as such I track inventory in and out of technicians vans, both serialized and not. I've done a great deal of work updating a broken excel sheet they use so that it functions again but I didn't build it. I've learned a lot but I'm only self taught with Excel and had never even heard of VBA code until I dived into this project.

Anyway, basically I have one sheet that has a list of all the items I need to keep track of. One section of this Sheet1 I've designed to have cells with dependent drop down lists that are Named Ranges on Sheet2. The tech can choose item A B or C in the first dropdown box and then the next cell shows only the serial numbers from the named range on Sheet2 of A B or C. (Was that english?)

Since the receiver comes out of the techs van once its used I want to figure out a way to delete the serial number that the tech has chosen without deleting the row or cell, just the value in it so that it can then have another serial number typed in. How can I do that?

Also, since I'm here, my 2nd drop down list seems to always start scrolled down and I have to scroll up to see my serial numbers. Why is that? The receiver list starts at the top but the dependent one doesn't...

View 4 Replies View Related

Delete Rows Not Matching 3 Static Criteria

Sep 12, 2008

I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.

Sub TestDeleteRows()

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

View 5 Replies View Related

Delete Rows Matching Cell, Across All Worksheets

Aug 20, 2006

in VBA, I would like to do the following:

Delete the entire row where any cell in that row is equal to cell "b15" in worksheet "Metal Type".
I would like it to run across all the worksheets hidden or unhidden, in the workbook, (except for worksheet "Metal Type").

If possible I would also like it to prompt the user to accept the contents of cell "b15" in worksheet "Metal Type" or enter a new string.

Also can it alert the user when "no matches were found"

View 9 Replies View Related

Delete Rows Not Matching Date Variable

Mar 10, 2008

I have a need to call a sheet from another sheet, copy two pages from the newly opened, existing sheet to a new workbook, search one sheet of the new workbook for any entries that don't contain a variable I specify, which is the first four digits of the field, and delete them. The sheets represent a daily view of jobs (identified by the "RptDate" variable) and an entire monthly list of jobs, which I want to delete all of them not from the date specified in the variable "RptDate". The fields in column A contain the date in "mmdd" format as the first four digits, and I have that specified in the "B1" celll of the originating workbook.

I found some code in this link Search Column Delete Row If Value Found that looked good. I had to modify it since I needed to search for a variable (RptDate) and needed to search column A and start on row 8 of that column. My current code looks like this:

Sub Macro1()

Dim RptDate As String
Dim RptMonth As String
Dim iLastRow As Long
Dim i As Long
RptDate = Range("B1").Text
RptMonth = Range("B2").Text
Workbooks.Open Filename:=(RptMonth)
Sheets( Array(RptDate, "Total Database")).Copy
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 8 Step -1
If Left(Cells(i, "A").Value, 4) <> RptDate Then
Rows(i).Delete
End If
Next i
End Sub

It works fine right up to the "Rows(i).Delete" line. It errors out with a "run-time error 1004, delete method of range class failed" and highlights the above line. I know the fields I'm pulling the variables from are working right since it opens the correct workbook off of one of them.

View 6 Replies View Related

Adding Search Function To Delete Matching Records..

Jun 1, 2009

I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.

View 5 Replies View Related

Compare 2 Columns Delete Rows With Matching Values

Feb 5, 2009

I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.

View 3 Replies View Related

Delete Individual Matching Values From A Column - Error

Mar 15, 2012

I am using this code below to delete individual matching values (the value in "A2") from a column.

Code:
Set Found = Columns("AH").Find(what:=Range("A2").Value, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Delete Shift:=xlShiftUp

I am receiving error "Delete Method of Range Class Failed" in the highlighted line.

View 3 Replies View Related

Delete Matching Debits & Credits (reversal Entries)

Jul 24, 2007

Q:How to delete reversal entries?

I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.

Example:
A B
Name Amount
1)Mr. A 2000
2)Mr. B 6000
3)Mr. A -2000
4)Mr. D 4000
5)Mr. A 2000

Now i want to matching amount of Mr. A of row 1 & 3 as these two entries are reversing each other. I am poor in english but hope that i have clarified the problem

View 7 Replies View Related

Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

View 2 Replies View Related

Fill Cells Based On Corresponding Cells Matching 3 Criteria

Jul 1, 2008

I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View 8 Replies View Related

Variable # Of Cells That Need Formula Matching # Of Cells

May 6, 2009

I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.

I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.

View 5 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows?

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.

My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.

So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5

[Code]....

My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.

View 4 Replies View Related

Delete Rows Matching Criteria & Move Rows

Aug 28, 2007

I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:

1- all rows with the value "TOM" in column C will have to be deleted.

2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).

View 2 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5

Should be processed and come out like this:.......................

View 2 Replies View Related

Highlighting Matching Cells

Apr 11, 2008

im looking for a formula that will find matching dates in column a and then i want to see if column b has a matching time in it

View 11 Replies View Related

Matching Cells Query

May 19, 2009

I have 2 worksheets - main and match. In the main I have something like the following:.....

There are 200 entries in the main work sheet. In the match work sheet I have something like:......

I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.
I am using the following array formula: {=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N. If I just do a straight if statement then I get a yes: =IF(A2='[main.xls]Main File'!$A$29,"y","n")

View 4 Replies View Related

Matching 8 Cells To Their Counterparts

Jun 13, 2009

I have a table which is 8 columns wide by approximately 1000 (+) rows. I am struggling with the following;

if I type in seven numbers somewhere I need to get returns on a full 8 cell match? (and possible partial, but exact matches to 4+out of 8 numbers) All the numbers in individual cells are no larger than 99 and (in theory) are arranged from left to right in ascending order.

Can anyone point me in the right direction?

I don't want to use auto filter as I am just looking for a reference that I am searching the correct number. Does that make sense?

View 10 Replies View Related

VLookup - Matching Two Cells?

Dec 8, 2011

Is it possible to do the following?

Worksheet1!A2 holds a text value
Worksheet1!B2 holds a text value

Worksheet2!D2 holds a text value
Worksheet2!C2 holds a text value

Is it possible to use vlookup to find a match for Worksheet1!B2 in Worksheet2!C2 where the combination of Worksheet1!A2, Worksheet1!B2 is the same as the combination of Worksheet2!D2, Worksheet2!C2?

View 9 Replies View Related

Macro For Matching Up Cells

Mar 14, 2007

I am struggling with a macro that I’m trying to create. Basically what it would do is it would start at cell K1 on a sheet called “Database” and look for the value contained in cell K1 in the entire sheet.

It would paste all matches until no more are found into a sheet called “Matches”. Cell K1 would also be moved to this sheet if matches were found against it. It would then move on to the next non-empty cell in column K.

If no matches were found it would move on to the next non-empty cell.

View 9 Replies View Related

Matching Cells (2003)

Mar 20, 2008

I have a list of unique numbers on sheet 'List'. On another sheet, 'Data', I have a columns of data and column E contains a number which will match a number in the list on sheet 'List'.

What I want to do but don't know how to is loop (i think) through the numbers in 'List' and every row in 'Data' that contains a number is moved so that the data is grouped according to the number. i.e all rows with 78878 will appear beside each other and so on.

Neec code that i could use that will automate this so the user can just press a button and the data is reorganized?

View 9 Replies View Related

Countif For Non Matching Cells

Aug 13, 2006

How do I write a nested COUNTIF formula ( assigning a value of 1 for each cell) for a range of cells that does NOT contain "", "OFF", or "REQ OFF" ?

For example:

A row of 20 cells (A1:A20), 5 are blank (""), 3 contain the text "OFF", 4 contain the text "REQ OFF", and the rest contain anything other than the three values just mentioned.

I need a sum number for all the cells in that row that contain any other value. In this example, the output value should be 8. Out of 20 cells in the row, a total of 12 cells had either "", OFF, REQ OFF, and the remaining 8 had some other value that did not equal those.

Some sort of COUNTIF(AND(... Can't seem to figure this one out.

View 9 Replies View Related

Column Formula For Matching Cells?

Apr 1, 2014

I'm currently having a hard time creating a formula to verify if the contents of 2 cells match.

Example from Spreadsheet -

Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG

When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".

View 14 Replies View Related

Matching Data 2 Cells In 2 Sheets

Jul 10, 2014

is it possible to provide a formula to match as per the uploaded file. Data in Sheet one columns A and B, compared to what is in Sheet two column A I have a lot of data, and only need a Y for yes, and N for No as a result.

View 3 Replies View Related

Matching Two Cells Having Almost Same Number But In Different Style?

Dec 22, 2013

I want to know if there is any formula to match two numbers which have same digits but are in different style. I have attached a spreadsheet with numbers in column A and Column B. Both columns have the same number just the style is different, is it possible that i get a match based on the digits match and then return the output as correct or wrong?

If all digits match from column A with column B it should show as correct else wrong.

View 2 Replies View Related

Matching Cells And Replacing Data

Apr 14, 2009

We have a report with 2 different sheets. On sheet one there are 13,000 rows of data and columns A-H are filled with different data. column A is our item id and column G has a date that states when the item was sold last. Well, the date in column G is wrong for about 7800 rows and needs changed.

The second sheet in the file is the 7800 rows with just the item id in column A and the correct date in column B. We are looking for an easy way to replace the rows in the first sheet with the correct information from the rows in the second sheet without manually replacing it line by line.

I almost forgot to add in, that in both sheets, column A with the item ids are the same item ids on both those sheets. So column A on sheet one has 13000 rows and included in those 13000 are the 7800 item ids on sheet 2.

We tried using the VLookup function that is incorporated into excel but had absolutly no luck, mostly because we have never used this function before. Sheet one already has all the lines of sheet 2, its just that sheet one has the wrong dates for the item ids and sheet 2 has the correct dates. im trying to replace the dates on sheet one from the dates on sheet 2 matching on the item ids from both sheets......

View 2 Replies View Related

Matching Two Pairs Of Cells Between Two Lists

Sep 13, 2013

I have a long master list of registered members, column C has last name, column D has join date.

Now I have a short list of last names with join dates.

I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.

View 8 Replies View Related

Fetching Cells Per Row Matching Two Criteria

Jan 8, 2014

I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.

What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.

For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)

Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.

View 2 Replies View Related

Finding Matching Values In Cells

Oct 26, 2008

I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?

For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9

something to return the value 9

View 2 Replies View Related

Find Group Of Matching Cells VBA Or Otherwise?

Sep 9, 2013

i have a worksheet that is about 100rows x 100columns.

i would like to create some code to find a certain record. for example..

a1 a2 a3 a4
the record might be " tony tony alex tony"

basically, this group of cells could be any where in the group and i need some code to search the worksheet and then outline it. i just want to be able to find a certain block of cells and outline it.

View 2 Replies View Related

Finding Matching Cells From Two Different Columns

Jul 7, 2009

I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.

View 9 Replies View Related

Sorting Based On Matching Cells

Jun 29, 2006

I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue

View 2 Replies View Related

Copy Corresponding Data Based On Matching Cells?

Jul 15, 2014

I am trying to copy a row based on the value of a cell.

I have two sheets in my workbook and on sheet 1, I have a part number and a description. On sheet 2, I have part numbers again, but this time I the description is broken up into the format I need.

What I am trying to do is have excel search on sheet 2 for the part numbers, then copy the information that corresponds to the part number into the correct column.

I have tried using Vlookup. But if the part number in row 2 on sheet 1 match the one in row 8 on sheet 2, this will copy over the data from row 2 whereas I need row 8.

If this would be more doable using VBA, that is fine by me. I haven't been able to figure out anything in VBA or in excel formulas up to this point.

View 7 Replies View Related

Matching Combination Of Two Cells With A Table And Return Value

May 8, 2014

As shown in the attachment, I'm trying to return value when matching cell B7 + D5 with cell G5:I7. Is there any way to do so?

Attached File: File Type: xlsx Book1.xlsx‎

View 3 Replies View Related

Row Matching Macro. (match Duplicating Cells)

May 4, 2009

I m writing a macro that will match duplicating cells in an excel spreadsheet. Here is an example.

BEFORE MACRO

id1 | example 1 | example 2 |
id2 | example 3 | example 4 |
id1 | example 5 | example 6 |
id2 | example 7 | |

AFTER MACRO

id1 | example 1 | example 2 | example 5 | example 6 |
id2 | example 3 | example 4 | example 7 | |

View 3 Replies View Related

ADVERTISEMENT