+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    02-22-2006
    Posts
    1

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

    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.

  2. #2
    Dsuperc
    Guest

    RE: Help me, compare 2 sheet and extract the match data into the new s

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1