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




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

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.




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".

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question