Comparing Columns inserting message based off of difference

[HTMLGhozt]

Hello again!

This community has been so helpful with my previous problems I've returned with another. This could be something simple; however, I can't seem to figure it out.

I stole this code (off this site?) and I'm trying to re-work it to compare two columns based off of the position of names in other columns.

Names1	Other stuff	Other stuff	Date1	other stuff	Date 2	other stuff	Names2	Output
John Smith			1/1/1991		1/2/1991		James Smith	Date Mismatch
Jr. Smith			1/13/2001		1/13/2001		Jr. Smith
James Smith			1/2/1991		4/1/1991		John Smith

<tbody>
</tbody>

The output needs to be based off the names in "Names1". I was trying to base the rows off of string members... but in the end (after troubleshooting)... Sometimes it's just better to do it simply.

The code as it is simply spits out Date mismatch on each row.

Also, if at all possible the code needs to be somewhat ambiguous there'll be a few processes that I'll be adjusting it too.

Sub Compare_Eff_Date()


Dim ws2 As Worksheet
Dim MbrName_Console As String 'Member Names (Console)
Dim MbrName_Carrier As String 'Member Names (Carrier)
Dim Console_Date As String 'Console Effective Date
Dim Carrier_Date As String 'Carrier Effective Date
Dim Carrier_Tg As String
Dim Console_Tg As String
Dim Eff_Date_Mismatch As String 'Effective Date Mismatch
Dim iListStart As Integer 'Row where List Begins
Dim Eff_Date_Mismatch_Msg As String
Dim i As Integer, j As Integer
Dim iLastRow1 As Integer, iLastRow2 As Integer


Set ws2 = ThisWorkbook.Sheets("Master spreadsheet")
'---Edit these variables---'
MbrName_Console = "S" 'Member Names (Console)
MbrName_Carrier = "Y" 'Member Names (Carrier)
Console_Date = "P" 'Console Effective Date
Carrier_Date = "H" 'Effective Date
Carrier_Tg = (MbrName_Carrier & Carrier_Date & i)
Console_Tg = (MbrName_Console & Console_Date & j)
Eff_Date_Mismatch = "V" 'In Console not Portal
iListStart = 4 'Row start
'--------------------------'


iLastRow1 = ws2.Range(MbrName_Console & Rows.Count).End(xlUp).Row
iLastRow2 = ws2.Range(MbrName_Carrier & Rows.Count).End(xlUp).Row


With ws2


Eff_Date_Mismatch_Msg = "Effective Date Mismatch"


'Identify in Console not Portal
For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
  For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
    If (Range("S" & j) & ("P" & j)) = (Range("Y" & i) & ("H" & i)) Then
    Exit For
    ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then
        Range(Eff_Date_Mismatch & i) = Eff_Date_Mismatch_Msg
    End If
  Next j
Next i
End With
End Sub

 

[HTMLGhozt]

Any Ideas?

 

[HTMLGhozt]

I've edited down the code a little. Maybe my previous statement was too vague? This macro is attempting to compare the dates (column "P" and "H") based on the row value of the names (Column "Y" and "S") and then paste a message in the correct column ("V") based on the i integer.

So find the dates which belong to the correct names and if there's a mismatch of the dates only to produce a message.

Sub Compare_Eff_Date(ws2)


Dim MbrName_Console As String 'Member Names (Console)
Dim MbrName_Carrier As String 'Member Names (Carrier)
Dim Console_Date As String 'Console Effective Date
Dim Carrier_Date As String 'Carrier Effective Date
Dim Eff_Date_Mismatch As String 'Effective Date Mismatch
Dim iListStart As Integer 'Row where List Begins
Dim Eff_Date_Mismatch_Msg As String 'Effective Date Message
Dim i As Integer, j As Integer
Dim iLastRow1 As Integer, iLastRow2 As Integer


'---Edit these variables---'
MbrName_Console = "S" 'Member Names (Console)
MbrName_Carrier = "Y" 'Member Names (Carrier)
Console_Date = "P" 'Console Effective Date
Carrier_Date = "H" 'Effective Date
Eff_Date_Mismatch = "V" 'In Console not Portal
iListStart = 4 'Row start
'--------------------------'


iLastRow1 = ws2.Range(MbrName_Console & Rows.Count).End(xlUp).Row
iLastRow2 = ws2.Range(MbrName_Carrier & Rows.Count).End(xlUp).Row


With ws2


Eff_Date_Mismatch_Msg = "Effective Date Mismatch"


For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
  For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
    If (Range(MbrName_Carrier & i)) = (Range(MbrName_Console & j)) Then
    If (Range(Carrier_Date & i)) = (Range(Console_Date & j)) Then
    Range(Eff_Date_Mismatch & i) = Eff_Date_Mismatch_Msg
    Exit For
    ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then
    End If
    End If
  Next j
Next i
End With
End Sub

 

[HTMLGhozt]

Can't seem to do it would it be possible to use a .find to reference the integer cell to find the name and then an offset from the referenced cell to compare dates?

 

[HTMLGhozt]

I Found something new! But it's backwards

So! What this handy piece of thing does: It finds people and dates which match and pastes them into output... I need it to do the opposite! Suggestions?

Sub MATCHING()
Dim My_Rows As Integer, My_New_Rows As Integer
Dim My_Text As String, my_text_2 As String
Dim My_Found_A As Integer
Dim Not_Found
Dim Ws2 As Worksheet
Dim Eff_Date_Mismatch_Msg As String


Eff_Date_Mismatch_Msg = "Effective Date Mismatch"
Set Ws2 = ThisWorkbook.Sheets("Master Spreadsheet")


    Application.ScreenUpdating = False
    For My_Rows = 4 To Range("S" & Rows.Count).End(xlUp).Row
        My_Text = Range("S" & My_Rows).Value & Range("P" & My_Rows).Value
        For My_New_Rows = 4 To Range("Y" & Rows.Count).End(xlUp).Row
            my_text_2 = Range("Y" & My_New_Rows).Value & Range("H" & My_New_Rows).Value
            If My_Text = my_text_2 Then
                My_Found_A = My_Found_A + 1
                If My_Found_A = 1 Then
                Range("V" & My_Rows) = Eff_Date_Mismatch_Msg
                Else
                Range("V" & My_Rows) = Eff_Date_Mismatch_Msg
                End If
            End If
        Next My_New_Rows
    Next My_Rows




    Application.ScreenUpdating = True
End Sub

 

[HTMLGhozt]

Bump

 

[HTMLGhozt]

Bump

 

[HTMLGhozt]

Bump