Matching report automatically

Dear Master,

I need your help.
I'm only know how to record using macro but not write macro script.
Therefore, hope master here able to assist me so i have save my time in matching this report.

I attached the sample here for easy reference.

I have 2 sheet YZ & EPA.
EPA is the master list. And i need to match YZ with EPA.
In YZ, there are alot of repeated customer and also including vendor which i dont need it.

Can i request macro to do following :-
1. Delete all repeated customer code in YZ list.
2. Filter the code that start with 3000 in YZ list. (i only need the code that start with 3000)
3. Highlight in YZ those customer that unable to match & mark "X" in last column.

Really appreciate your help as i need to screen this everymonth manually.
It took too much of my time

TIA

I found this in others website. Possible to modify to suite mine?

//quote
hi
i am using the following code to compare two worksheets. The code is working fine. the differences are highlighted. I need a code that if I rectify the diferences in one of the sheets, the previously highlighted cells should return to normal. can somebody help me on this please?

the code is:
unquote//

Sub All_Diffs_Highlighted()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Cell As Range
Dim sBook As String
'
If Workbooks.Count < 2 Then
MsgBox "Error: Only one Workbook is open" & vbCr & _
"Open a 2nd Workbook and run this macro again."
Exit Sub
End If
'
Set wb1 = ThisWorkbook
For Each wb2 In Workbooks
If wb2.Name <> wb1.Name Then Exit For
Next
'
On Error Resume Next
ReDo1:
Application.DisplayAlerts = False
sBook = Application.InputBox(Prompt:= _
"Compare this workbook (" & wb1.Name & _
") to...?", _
Title:="Compare to what workbook?", _
Default:=wb2.Name, _
Type:=2)
If sBook = "False" Then Exit Sub
If Workbooks(sBook) Is Nothing Then
MsgBox "Workbook: " & sBook & " is not open."
GoTo ReDo1
Else
Set wb2 = Workbooks(sBook)
End If
'
Application.ScreenUpdating = False
For Each ws1 In wb1.Sheets
If Not wb2.Sheets(ws1.Name) Is Nothing Then
Set ws2 = wb2.Sheets(ws1.Name)
For Each Cell In ws1.UsedRange
If Cell.Formula <> ws2.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws2.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
If ws1.UsedRange.Rows.Count <> _
ws2.UsedRange.Rows.Count Or _
ws1.UsedRange.Columns.Count <> _
ws2.UsedRange.Columns.Count Then
For Each Cell In ws2.UsedRange
If Cell.Formula <> ws1.Range(Cell.Address).Formula Then
Cell.Interior.ColorIndex = 35
ws1.Range(Cell.Address). _
Interior.ColorIndex = 35
End If
Next Cell
End If
End If
Next ws1
'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub Clear_Highlights_this_Sheet()
ActiveSheet.UsedRange. _
Interior.ColorIndex = xlNone
End Sub

Sub Clear_Highlights_All_Sheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.UsedRange.Interior.ColorIndex = xlNone
Next
End Sub

Please Login or Register  to view this content.

Hi Jindon,

Thank for your code.
How should I copy it to my workbook?

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Thank you Jindon. It work perfectly on my worksheet.
However, i have some amendment as i gave the wrong information in the first place.
Hope you are able to help to modify.

1. Filter YZ column M to 3000 series (delete all non 3000 series)
2. Match YZ column N to EPA column S
3. If cant match, "X" will indicate in column P

I reattached the sample here.

Thank you in advance.

Change to

Please Login or Register  to view this content.

Hi Jindon,

I facing a run-time error showing "application-defined or object-difined error"

.Offset(1, 2).Resize(.Rows.Count - 1).Formula = _
"=if(trim(n3)="""","""",if(iserror(match(n3,'epa'!s:s,0)),""X"",""""))"

Try change to

Please Login or Register  to view this content.

Hi Jindon,

Still cant. The result in YZ are empty.
Is it because of i inserted column?
This is the new worksheet with i inserted column which is Column N.

You are not using the last code I posted.

Please Login or Register  to view this content.

opps... my fault.
I got it!!
However, how to assign macro delete all the non 3000 series in column M?

What do you mean by 3000 series?

The code is deleting the rows that are not begin with 3.

I would like it to delete the rows that are not begin with 3000.
Possible?

Then just change to

Please Login or Register  to view this content.

Perfectly done!!!
Really appreciate your help!!