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?
View 5 Replies
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.
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
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.
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...
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.
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 RelatedI 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...
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
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"
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.
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 RelatedI'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 RelatedI 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.
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
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.
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.
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.
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.
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).
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:.......................
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 RelatedI 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")
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?
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?
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.
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?
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.
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".
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
