Help me, compare 2 sheet and extract the match data into the new sheet.

  • Thread starter Thread starter sweetnet
  • Start date Start date
S

sweetnet

Hai,

Im not a good excel user and i have a problem here, please any one.

i got sheet1 with 2 columns like this:

code | item
100 | book2
222 | paper1
101 | book1
333 | pen1

then i got sheet2 with 2 columns like this:

code | item
100 | book2
102 | book3
101 | book1

so i want to extract all possible item and code from sheet1 that matc
the code in sheet2. I need to extract the match code and item into ne
sheet3.

So for example after we extract the above data i will get sheet3 jus
like this:

code | item
100 | book2
101 | book1

Can anyone please help me in this matter? any suggestion with VB
programming or anything is accepted.

Please.

Thanks
 
Try this code
The Following VBA procedure should do the trick for you.

Objective: Compare Values in Old Sheet With New Sheet. When differences are
found record the Number ... and what sheet it is and isn't on ... in a sheet
called "Differences".

NOTE :
1) You must make sure you have the 3 sheets name properly
2) Start Code by calling "FindSheetDiff"



Public Sub FindSheetDiff()
Call FindAndRecDiff("Old", "New")
Call FindAndRecDiff("New", "Old")
End Sub


Private Sub FindAndRecDiff(SourceSh, TargetSh)
With ThisWorkbook.Sheets(SourceSh)
For Each C In .Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
With ThisWorkbook.Sheets(TargetSh).Range("A:A")
If .Find(C.Value, LookIn:=xlValues) Is Nothing Then

With ThisWorkbook.Sheets("Difference")
NxRw = .Cells(65536, 1).End(xlUp).Row + 1
..Cells(NxRw, 1).Value = C.Value
..Cells(NxRw, 2).Value = "In " & SourceSh & " But not in " & TargetSh
End With

End If
End With
Next C
End With
End Sub
 
Back
Top