two identical names in different columns in Excel

[Pauls123]

Hi all,

I have a simple spreadsheet where in two of the colums have names in them. Column E and column L. A certain name may appear just the once in both of the columns, no more than once.

I want to highlight/find the names that appear in both columns?

Any suggestions please

Paul

 

[JonXL]

Did you already try conditional formatting?

https://support.office.com/en-us/ar...in-excel-34402f91-c7e7-4060-944c-65d913033d18

Maybe I don't fully understand what you're wanting to do, but it seems that would do the trick...

 

[Pauls123]

Hi Jon,

Thanks for your reply. There may be heaps of similar names, not just the one name. But a name would only appear the once in both columns. Not sure if that would work in conditional formatting?

Paul

 

[JonXL]

Conditional formatting will work very well in this case. Just select both columns (use Ctrl+click if necessary) and follow the instructions in the link above under the first heading - "Highlight duplicate values with color".

 

[jolivanes]

Sub Maybe()
Dim c As Range
    For Each c In Range("E2:E" & Cells(Rows.Count, 5).End(xlUp).Row)
        If Application.WorksheetFunction.CountIf(Range("L:L"), c.Value) > 0 Then c.Interior.Color = vbRed
    Next c
End Sub

 

[Pauls123]

Hey Jon, That is brilliant, I didnt realise you could do a conditional formatting for two separate columns. Excellent. And also thanks Jolivanes. Your formula is a bit too advanced for me.

Thanks again, Paul