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 match
the code in sheet2. I need to extract the match code and item into new
sheet3.

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

code | item
100 | book2
101 | book1

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

Please.

Thanks.


--
sweetnet
------------------------------------------------------------------------
sweetnet's Profile: http://www.excelforum.com/member.php...o&userid=31809
View this thread: http://www.excelforum.com/showthread...hreadid=515318

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

"sweetnet" wrote:


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 match
the code in sheet2. I need to extract the match code and item into new
sheet3.

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

code | item
100 | book2
101 | book1

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

Please.

Thanks.


--
sweetnet
------------------------------------------------------------------------
sweetnet's Profile: http://www.excelforum.com/member.php...o&userid=31809
View this thread: http://www.excelforum.com/showthread...hreadid=515318