Matching Problem

[austenr]

Could someone please look at the attached zip file and give me an idea how to solve this problem. I have two sheets that compare each other and produce a discrepancy worksheet. The problem is when a new item is found on Sheet1 and not on Sheet2 it writes the row like it is supposed to do but when it processes the next row, on Sheet1 and Sheet2 for a match it also writes a row to Sheet3 even though they match. I tried several solutions but have not come up with anything. Could asomeone please help? Thank you

[Ken Puls]

Hi Austen,

I didn't really have enought time to dig through your sub to really understand it in full, but I think I have a different version that does what you want. (more efficiently too!) DRJ's Delete Dups KB Entry, has a really cool method using CountIf that can be adapted to do this kind of thing. I changed your sub a bit to look at each cell in Sheet 1, Column B to see if there was at least 1 entry in Sheet 2, Column B that matched. If not, I just added the entire row to the next available line of sheet 3.

If you need to check sheet 2 to get any missing from sheet 1, that could also be done with another loop.

Sub OneOnOne()
     Dim rngCel As Range, rngChk As Range
Set rngChk = Sheets("Sheet1").Range("B1", Sheets("Sheet1"). _
                     Range("B65536").End(xlUp))
     For Each rngCel In rngChk
        If Application.WorksheetFunction.CountIf(Sheets("Sheet2"). _
 Range("B1:B" & Sheets("Sheet2").Range("B65536").End(xlUp).Row), _
 rngCel.Text) = 0 Then
            rngCel.EntireRow.Copy
            With Sheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0)
                .EntireRow.PasteSpecial Paste:=xlValues
                .Font.ColorIndex = 18
            End With
        End If
     Next rngCel
End Sub

Let me know if you want me to troubleshoot your original code, but in the mean time, I hope this helps.

Cheers,

[austenr]

I think I have found the problem. The problem seems to be that there are people with the same last name within the Sheet I am matching (Sheet1). Someone suggested that I take the column with the last name and concantonate it in another column and do the match there or keep the same routine and do a FindNext until there are no more found. I suppose that both would work, but at this point I think I would rather give the FindNext procedure a go. Could someone look at the code and tell me how to approach this as I am not sure how to go about either procedure? Thanks in advance for your expert help and suggestions.

[austenr]

What I really need to do is do the main check on colunn A. Then check each cell in the row. If the cell A1 on Sheet1 is not found, then write that row and go to the next row in Sheet1 checking A1. If A1 on Sheet1 and Sheet2 match, then check each cell in that row on Sheet1 to see if it matches every cell in Sheet2. If it does, do nothing, if not write the row in Sheet1 and proceed with the match. Matching the last name is probably not a good idea since there will be duplicates of that name. Matching the SS# is the best way I think. Could I change what I have now to do that fairy easily? Thanks

[Zack Barresse]

Hi Austen,

For your HelpInfo sheet, I'd sugget you just get rid of the hidden sheet and hardcode your comments. This will add some additional flexibility rather than pointing to a specific cell. This looks better and is more decipherable (imho):

Option Explicit

Public Sub Setup_Sheets()
    MsgBox "To use this program correctly there are a few things to know:" & _
    vbCrLf & "(1)  You have to have both sheets sorted." & vbCrLf & _
    "(2)  The raw data sheet is Sheet1.  The master sheet is Sheet2.  " & _
    "The discrepancy sheet is Sheet3.  All columns on Sheet1 and " & _
    "Sheet2 must be the same and in the same place." & vbCrLf & _
    "(3)  When the macro runs if there are any discrepancies found " & _
    "in ANY cell the entire line of Sheet1 is written to Sheet3.  " & _
    "If the data in Sheet1 is new the whole line is written to Sheet3.", _
    vbInformation, "Help: Setup Sheets"
End Sub

Public Sub Running_Macro()
    MsgBox "To run the macro do the following:" & vbCrLf & vbCrLf & _
    "     Go to Tools," & vbCrLf & "     then to Macro," & vbCrLf & _
    "     then to Macros." & vbCrLf & vbCrLf & "A pop up box will appear.  " & _
    "Select " & Chr$(34) & "One on One" & Chr$(34) & " and click run.  " & _
    "The program will match the two files." & vbCrLf & _
    "Depending on how large the file is it may take a couple of minutes.", _
    vbInformation, "Help: Running Macro"
End Sub

Public Sub Disc_Sheet_Info()
    MsgBox "Once the program has run if there are any discrepancies, " & _
    vbCrLf & "the Discrepancy Report will be written on Sheet3.  Any totally" & _
    vbCrLf & "new line will be highlighted in light red and any row that has" & _
    vbCrLf & "ANY change is highlighted in green.", _
    vbInformation, "Help: Discrepancy Sheet Info"
End Sub

And where are the SSN?

[Ken Puls]

What I really need to do is do the main check on colunn A. Then check each cell in the row. If the cell A1 on Sheet1 is not found, then write that row and go to the next row in Sheet1 checking A1. If A1 on Sheet1 and Sheet2 match, then check each cell in that row on Sheet1 to see if it matches every cell in Sheet2. If it does, do nothing, if not write the row in Sheet1 and proceed with the match. Matching the last name is probably not a good idea since there will be duplicates of that name. Matching the SS# is the best way I think. Could I change what I have now to do that fairy easily? Thanks

Hi Austen,

I hear what you're saying about the last name, but where is the SSN? If you run on column A, right now, you'l probably get all the names, since sheet1.row1 doesn't match sheet2.row1. (And none of the rest will either...) If that is the SSN, I would assume that the number should be the same for one person on both sheets...