Consulting

Results 1 to 18 of 18

Thread: Comparing Data on two sheets

  1. #1

    Post Comparing Data on two sheets

    Hi All,

    I have yet another big project on hand. This is the requirement. I have two worksheets (Sheet1) and (Sheet2). I have some data on sheet1 and some on sheet 2. I will need to compare column A with Column A, if they match Shade them, and then compare Column B with Column B if they match shade them and then Column C with Column C. Now if all the three columns match, for which some will and some won?t. If all the three columns match, then I will to Copy the entire record to a new workbook/worksheet.

    Looking forward to your help, thanks in advance.

    Regards,
    Richard.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    What happens if say

    Sheet1!A2=Smit
    Sheet1!B2=Richard
    Sheet1!C2=Planner

    and

    Sheet2!A2=Smith
    Sheet2!B2=Joe
    Sheet2!C2=Planner

    should it be highlighting Sheet2!B2 when it is clear that it is not different to a corresponding row in Sheet1, the rows are different elements altogether.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    It should actually delete if all the three colms match.

    Sheet1!A2=Smit
    Sheet1!B2=Richard
    Sheet1!C2=Planner

    and

    Sheet2!A2=Smith
    Sheet2!B2=Joe
    Sheet2!C2=Planner


    Col A - Only contains Serial Numbers
    Col B - Only contains Part Numbers
    Col C - Only contains AlfNum

    If Col A - Col A, Col B - Col B, Col C - Col C match, then the row can be deleted. Else the entire row needs to be transfered to another location or they can be as they are as long as the matching rows are deleted.

    Note : It is the same. If Col A has a serial number then Column B has a partnumber associated to IT and also the AlfaNum.

    I hope im not confusing you.

    Please let me know if you need more information.

    Regards,
    Richard.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Yeah next question.

    What if the 3 items in Sheet1 row don't match Sheet2 row 2, but they do with row 3? Is theier a key field to matc with?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Cols 1 in sheet 1 should only match with content in Cols 1 in sheet 2 and likewise.

    If Cols 1 in sheet 1 matches with Cols 1 in sheet 2 then check Cols 2 in sheet 1 with Cols 2 in sheet 2 then with Cols 3 in sheet 1 with Cols 3 in sheet 3. If all the three matches then delete the entire row. Even if One value does not match then do nothing.
    Example.

    Cols 1 in sheet 1 matches with Cols 1 in sheet 2 and
    Cols 2 in sheet 1 matches with Cols 2 in Sheet 2 but
    Cols 3 in sheet 1 does not match Cols 3 in sheet 2
    Then keep the entire row

    Cols 1 in sheet 1 matches with Cols 1 in sheet 2 But
    Cols 2 in sheet 1 does not match Cols 2 in Sheet 2 and
    Cols 3 in sheet 1 does not match Cols 3 in sheet 2
    then keep the entire row.

    This can be in combination, either rows.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Richard,

    That is just re-stating what you have already said, it doesn't answer my last question.

    Here is an example of what I mean

    Sheet1

    Serial Num Part Num Afl Num
    X123 7-2 123
    X123 9-2 345
    X123 10-2 789

    Sheet2

    Serial Num Part Num Afl Num
    X123 7-2 123
    X123 10-2 789

    Should row 3 on Sheet1 and row 2 on Sheet2 be deleted?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    My Apologies XLD, Yes, That is perfect.

    - Richard

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    What is perfect? I asked a question, to which there are (at least) 2 responses.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Sorry, i meant to say. yes, that is correct. It should be deleted.
    If you can delete only the rows on Sheet 1, it would be great.

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Well now I am really confused as to what you want. I don't know if it is you that has confused me, or whether I have confused myself <bg>.

    Let's try some code

    [vba]

    Public Sub ProcessData()
    Dim i As Long
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")
    With sh1

    LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With sh2

    LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For i = LastRow1 To 1 Step -1

    If 1 <= LastRow2 Then

    If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value And _
    sh1.Cells(i, "B").Value = sh2.Cells(i, "B").Value And _
    sh1.Cells(i, "C").Value = sh2.Cells(i, "C").Value Then

    sh1.Rows(i).Delete
    End If
    End If
    Next i

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Thanks XLD, however that did not work entirely. Only one row was deleted. not All matching rows. I noticed that is was looped, but not sure why it did not get through. please tell me if im missing something here.

    Thanks and Regards,
    Richard

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Impossible to tell without more info, or a sample workbook.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    Alrighty! I've attached a sample workbook, all the rows shaded should be deleted in sheet one.

    If you compare both the sheets you can see that I've shaded only the rows that match.

    Hope this helps.

    Regards,
    Richard

  14. #14
    I actually am trying to accomplish the same thing (sort of). The problem with the code below is that it's comparing the two columns Row by Row (ie. Details!A5 to Owners!A5). Is there a way that it'll search the entire column to see if there's a match, and not do it row by row? The value "Frank" might exist in A5 of the Details sheet, but on the Owners sheet "Frank" exists in A20..therefore it doesn't find the match.


    [VBA]Public Sub FindUnmatchedData()
    Dim i As Long
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    Set sh1 = Worksheets("Detail")
    Set sh2 = Worksheets("Owners")
    With sh1

    LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With sh2

    LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For i = LastRow1 To 5 Step -1

    If 1 <= LastRow2 Then

    If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then
    sh1.Cells(i, "A").Interior.ColorIndex = 2
    Else
    sh1.Cells(i, "A").Interior.ColorIndex = 6
    End If

    End If
    Next i

    End Sub[/VBA]

  15. #15
    I actually found the following Conditional Format to do what I need. But if you were to have a simple solution to the VBA approach, I would be interested to hear it. Below, "AppName" is the name of the list that I am comparing the cells to.

    =COUNTIF(AppName,A50)=0

  16. #16
    Hey,
    Ok, I tried to attach a workbook but I couldn't get it under 2.MB even with just 10 lines so Im going to try and make it look like a work book here

    So Imagine this is the workbook:
    Sheet1 Column A:
    123456
    123457
    123358

    Sheet2 Column A:
    123459
    123457
    123777
    123456

    Sheet3:
    Column A | Column B


    Now in sheet three column A, I want the numbers from sheet1 to display there. In column B, I want a Yes or a No
    Yes = Yes the number in sheet1, matches a number in sheet2
    No = If a No is displayed it means there is no number in sheet1 that matches sheet2

    Now the reason for having all the numbers listed in sheet1 in sheet3 is because:
    1) I need to know which numbers match and which ones dont
    2) Sheet2 is 30,000 lines, Sheet1 is only 2,000 lines...
    So there is going to be several numbers on sheet1 that wont be in sheet2 and vice versa.

    So I need a formula or VB script that will list all the numbers on sheet1 on sheet3, then have the script display a yes or a no beside each number telling me - "Yes" the number is there, or "No" the number is not there

    Hope that helps...

    Thanks again!
    Last edited by IcePirate; 09-05-2008 at 09:48 AM.

  17. #17

    RE:

    Ok, here is a sample workbook.
    View all three sheets inside the workbook.
    This is a scaled down version -
    My sheet1 is 2,000 lines, my sheet2 is 30,000 lines

    I made it real simple...If you look at sheet3...When you see a yes - that means there is a number in sheet1 that matches a number somewhere in sheet2.
    When you see a `no` on sheet3 - that means there is no number on sheet 1, that matches sheet 2

  18. #18

    Whops

    Forgot to post workbook

Posting Permissions

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