If cell match in one column then check for match among 2 other different columns

[jr1977]

Hi,
I have a problem that I'm having difficulty solving. Please help.

[TABLE="width: 667"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]Code Name[/TD]
[TD]FirstnameProfile[/TD]
[TD]LastnameProfile[/TD]
[TD]FirstnameonBill[/TD]
[TD]LastnameonBill[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] max[/TD]
[TD]Bob[/TD]
[TD]Johnson[/TD]
[TD]Robert[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] max[/TD]
[TD]Bob[/TD]
[TD]Johnson[/TD]
[TD]Bob[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD]max[/TD]
[TD]Betty[/TD]
[TD]Johnson[/TD]
[TD]Bob&Betty[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="align: right"]5 [/TD]
[TD]royal[/TD]
[TD]Bobby[/TD]
[TD]Piper[/TD]
[TD]Bob[/TD]
[TD]Piper[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] parrot123[/TD]
[TD]Jimmy[/TD]
[TD]Bear[/TD]
[TD]Jimmy[/TD]
[TD]Bear[/TD]
[/TR]
[TR]
[TD="align: right"]7 [/TD]
[TD]boots[/TD]
[TD]Paulie[/TD]
[TD]Roland[/TD]
[TD]Paul[/TD]
[TD]Roland[/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD]boots[/TD]
[TD]Jenny[/TD]
[TD]Lester[/TD]
[TD]Paul[/TD]
[TD]Roland[/TD]
[/TR]
</tbody>[/TABLE]

What I need to do is look at the Code Names column. If any of these match, they are the subject of further investigation. For example, rows 2,3,& 4 are all code name "max". Next I need to see if the FirstnameProfile matches the FirstnameonBill for any of the "max" group. If they match it can print a YES in column F. If no match then No in column F.

For row 2 it would return a Yes because Bob (B2) matches Bob (D3).
For row 3 it would return a Yes because Bob (B3) matches Bob (D3).
For row 4 it would return a No because Betty (B4) does not match any of the First names from D2, D3 or D4.
For row 5 it would return a No because Bobby (B5) does not match D5
For row 6 it would return a Yes because Jimmy matches D6
For row 7 it would return a No because Paulie does not match D7 or D8
For row 8 it would return a No because Jenny does not match D7 or D8

thanks!

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim c As Range
    Dim FNP As Range
    Dim foundFNP As Range
    Sheets("Sheet1").Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("A1:A" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    For Each c In rngUniques
        ActiveSheet.Range("$A$1:$E$" & LastRow).AutoFilter Field:=1, Criteria1:=c
        For Each FNP In Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
            Set foundFNP = Range("D:D").Find(FNP, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundFNP Is Nothing Then
                Range("F" & FNP.Row) = "Yes"
            Else
                Range("F" & FNP.Row) = "NO"
            End If
        Next FNP
    Next c
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    Application.ScreenUpdating = True
End Sub

 

[Trouttrap2]

You can try this too:
=IFERROR(IF((A2=$A$2:$A$8)*(MATCH(B2,$D$2:$D$8,0))>0,"YES"),"NO")

 

[jr1977]

This line seems to be an issue when I try to run it
Sheets("Sheet1").Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("A1:A" & LastRow), Unique:=True

 

[jr1977]

Thanks. However this one only seems to search the row and not the grouping. Also it needs to be applicable for a wider range of cells than the ones in the example.

 

[Trouttrap2]

Thanks. However this one only seems to search the row and not the grouping. Also it needs to be applicable for a wider range of cells than the ones in the example.

Try this:
=IFERROR(IF(MATCH(B2,INDIRECT("D"&MIN(IF(A2=$A$2:$A$8,ROW($A$2:$A$8)))&":D"&MAX(IF(A2=$A$2:$A$8,ROW($A$2:$A$8)))),0),"YES"),"NO")
You have to Ctrl+SHFT+Enter because its an aray formula. You will need to change the ranges for "A" to fit your data ranges, A2:A1000 or whatever.

 

[jr1977]

Great. I'll give it a try. Thanks for your patience.

 

[jr1977]

I think its working. I have a wrinkle that may add some complexity. I hope you can help out with it too.
If I wanted to search from partial matches between B&D how would I add that to your formula? I did partial matches straight across a row using =MATCH("*"&LEFT(B2,3)&"*",D2,0). I'd like to add this. thanks!

 

[Trouttrap2]

Be aware a partial match won't work with "bob" and "Robert". Bob and Robert are synonyms. Here is a better formula that won't require Ctrl+Shft+Enter. It includes the partial match you asked for.
=IFERROR(IF(MATCH("*"&LEFT(B2,3)&"*",OFFSET(D2,-(COUNTIF($A$2:$A2,A2)-1),,COUNTIF($A$2:$A$8,A2)),0),"YES"),"NO")

If you want to continue using the other formula, replace the B2 in the formula with "*"&LEFT(B2,3)&"*"

 

[jr1977]

Understood. I was looking to add in matches to things Paulie and Paul. thanks again. Where in the formula is it looking at column D? Is that the "D" and ":D"? You're a lifesaver thanks!