Solved: Matching & Deleting rows

[MRichmond]

I have a workbook in which I need to compare a list of records (this can vary in length up to about 2ooo rows) against existing records that are kept on approx 50 different worksheets.

All worksheets are named after column A in the data, and I have found a macro that I can use to transfer the rows onto the correct tab, without the need to manually copy and paste.

For the purpose of the post the existing records on the individual tabs are list one, and the new data is list two.

What i am looking for is a macro to check and do the following: If the item is on list one, but not on list two delete from list one. If the item is on list one & on list two delete from list two. Result should be items that appeared in both lists (keeping the record from list one) and items that only appear in list two. All these matches are made against column G (Item No) only. List two can be either on the same tab or a combined tab (whichever would be easier it, makes no difference to me as I can move that data once the matching/ deletion has occurred), list one will always be on a tab marked with the location number, and I have 50 of these tabs

I am attaching a sample worksheet to show what I am looking for.

Any help would be gratefully received

[MRichmond]

Ok, I've had a number of views but no suggestions as yet, so I will see if I can make it easier.

I get new data every week (could be up to 2000 records) that goes onto one tab. I then have 40-50 existing data tabs, so I need to check to see if the new record exists on one of the tabs (using the number in column G (which will only ever be on one tab)), if so it is deleted from the new data list. If existing record is not on new data then that is also deleted.

I can perform the checks by using vlookups, and then deleting unwanted records, but i thought there may be a VBA method to stop me having to enter vlookups on 50 worksheets.

I have attached what i think is a simplified workbook in the hopes that it will be easier for you.

thanks in advance for the help

[CharlesH]

MRichmond,

Will you be adding a worksheet for the NewData that was received?
Will the NewData be added too the next empty row of the "NewData" worksheet?
You can Programmatically add the vlookup formula to the worksheets and delete.

[Simon Lloyd]

This should do it although it would be much faster using FIND
[vba]Sub Delete_If_Found()
Dim sh As Worksheet
Dim Rng As Range
Dim MyCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'assuming data to check is in column A sheet1
Set Rng = Sheets("Sheet1").Range("A1:" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Address)
For Each MyCell In Rng
For Each sh In Sheets
'check all sheets except sheet1
If sh.Name = "Sheet1" Then GoTo Nxt
If Application.WorksheetFunction.CountIf(sh.UsedRange, MyCell) = 1 Then
MyCell.EntireRow.Delete
GoTo Nxt2
End If
Nxt:
Next sh
Nxt2:
Next MyCell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
[/vba]
EDIT: just to let you know i haven't had time to look at your workbook but you can easily adapt this

[CharlesH]

Simon,

I think the data is in column "G", and too this is the project I was looking at for the "Vlookup" problem ( That you helped me with) I was having. I'll still look at my approach. As mentioned the "Old Brain" is still learning.

[CharlesH]

Hi,

This code can be modified. This is only an example.
It will only check the "New Data" sheet to Sheet "619".
I modified the workbook for this test.

You may need to turn Auto Calculate back on before testing.

[Simon Lloyd]

Why go to the trouble of entering the formula on the worksheet when it's only used for the test, you'd be better off Evaluating it in the VBA then check against the evaluated value.

[CharlesH]

Hi,

I update the attachment to remove a piece of code that would not allow it to work properly.
The code should place a vlookup formula in each worksheet and then delete the rows per the users request. With out further detail from the user this is what I came up with.
There may be a faster and simpler solution but!!!!

Simon, thanks for your input.

[MRichmond]

Hi CharlesH,

Sorry I havent replied sooner.

As for you questions the worksheet New Data exists and will not be a new sheet.

It will always be empty when we start, data will be pasted onto it, and then once the checks have been done any remaining rows will be added to the relevant worksheet based on the number in column A. In other words "New Data" is only a holding worksheet until the checks are made, and then the data is moved.

I will test the various solutions given so far and report back.

Thanks very much for your time & trouble Charles & Simon.

[Simon Lloyd]

Charles, what i mean is rather than do this[vba]Sheets(ws1).Cells(2, 9).Formula = "=VLookup(G2,'" & Sheets(ws1).Range("A2") & "'!G$2:G$" & s2lrow & ", 1," & "False" & ")"[/vba]then this[vba]For j = s2lrow To 2 Step -1
If Sheets(ws2).Cells(j, 9).Text = "#N/A" Then
Sheets(ws2).Range("A" & j & ":I" & j).Delete Shift:=xlUp
End If
Next[/vba]and all the formula fill down in between you could do this[vba]Dim eva
For j = s2lrow To 2 Step -1
On Error Resume Next
eva = Application.WorksheetFunction.VLookup(Sheets("New Data").Range("G2"), Sheets("New Data").Range("G2:G" & j), 1, 0)
If Not IsEmpty(eva) Then
Sheets(ws1).Range("A" & j & ":I" & j).Delete Shift:=xlUp
End If
Next[/vba]

[CharlesH]

Simon

Thanks for the information. I'll look at it when I return.

[MRichmond]

MRichmond,

Will you be adding a worksheet for the NewData that was received?

No, the "NewData" worksheet will always be there, and the new data will be apsted onto it.

Will the NewData be added too the next empty row of the "NewData" worksheet?

The "NewData" worksheet will be empty, as any remaining records will be moved at the end of the checking process onto the relevate worksheet based on column A of the "NewData" worksheet.

You can Programmatically add the vlookup formula to the worksheets and delete.

Would I have to have VBA enter the vlookup 40-50 times or is there a way VBA can check each worksheet (again worksheets are named after column A of "NewData" worksheet.

I tried your example "Delete_If_Found" Simon and keep getting an error 'Run-time error '9':
Subscript out of range
Debug takes me to this line of code

Set Rng = Sheets("Sheet1").Range("A1:" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Address)

Not sure whats going wrong, so not sure how to fix it.

Thanks for the help, and pointers so far.

[Simon Lloyd]

Subscript error 9 means it couldnt find the worksheet, either you dont have a Sheet1 or you have a typo in the sheet name or maybe a leading or trailing space.

[CharlesH]

Hi,

I changed this from the "Vlookup" approach to the 'Find" as suggested by Simon.
In the workbook I left some formula for the "Vlookup" in order to validate what the user wanted.

[MRichmond]

CharlesH you are a magician, a very big thank you.

I do need one more tweak though, I have header rows on all the data tabs, apart from New Data. Rows 1 - 4 need to be kept as they are.

Can the code be modified to stop it from deleting rows 2,3 & 4?

[MRichmond]

CharlesH & Simon thank you very much for your help.

I worked out what I need to change to keep my header rows, and am now a happy bunny.

thanks again guys

[CharlesH]

MRichmond,

Thanks for letting us know.