matching names in same row

[gilnic]

Gwenda Johnstone Beverley Johnstone
Robert Hellicar Robert Hasterr
Mark Kavanagh Tony Kavanagh
Wayne Nichols Wayne Nortin
Steven Lake Steven Lake


Hello, Hoping someone can help me. I have two columns with names. What I would like to do is to highlight in a third column any row that has a matching last name. The matching of the first name does not matter .So I would like Johnstone, Kavanagh and Lake to be highlighted in the third column. I did see somewhere where matching of names can be affected by differing spaces, this is likely to happen in my case as there could be different number of spaces after each name.

 

[RickOlson]

It wasn't clear to me whether you were looking for a macro, or just a formula. Here's a formula. It's a little ugly because I assumed that extra spaces could be at the beginning, middle or end. I also assumed that it's possible for one person's first name to be the other person's last name, but that you don't want to flag that situation (e.g. Brent Musberger and George Brent).

This compares the names in cells A7 and B7 and reports "TRUE" in C7.

=IF(RIGHT(TRIM(B7),LEN(TRIM(B7))-FIND(" ",TRIM(B7)))=RIGHT(TRIM(A7),LEN(TRIM(A7))-FIND(" ",TRIM(A7))),TRUE,"")

If you really want to highlight C7 by, for example, shading C7 solid yellow, you can use conditional formatting in C7 to make the background yellow and text yellow when it's true.

I hope this helps.

 

[sethu_bala1]

Use the below formula in conditional formating.(use formula to determine which cells to format)

=IF(RIGHT(B1,LEN(B1)-(FIND(" ",B1)))=C1,TRUE,FALSE)

 

[gilnic]

Hello, Thanks for reply.I am not sure what is meant by conditional formatting.The names I have are in column A and Column B.I would like in column C to say true when Col a and Col B last names are matched.

 

[Arul.rajesh]

Hello, Thanks for reply.I am not sure what is meant by conditional formatting.The names I have are in column A and Column B.I would like in column C to say true when Col a and Col B last names are matched.

Conditional formatting allows you to format a cell based on values in other cells.



If you are using Excel2010,
1.Select the range you are trying to apply conditional formatting to.
2.Hit Alt,H,L Then N.
3.In the dialog box "New formatting Rule".
4.Select the Option Use a formula to determine which cells to format.
5.Click the format button and select the format you want applied.
5.In the box labeled "Format values where this formula is true:"
Write your criteria which must be exactly like the logical_test part of the "IF" function. If the test evaluates to a "TRUE" the selected formatting would be applied.
Also the important thing to remember is that when you are writing the formula for the criteria, keep in mind that you are writing the formula with reference to the active cell in the selected range, So you will have to use a combination of absolute and relative references to acheive the desired result.


Absolute reference $A$1 - when you copy the formula to any other cell. This reference always points to A1

Relative reference - Suppose you have a formula in cell "B1" which has a reference to $A1. When you copy and paste the formula to cell "C2" the reference would change to $A2, because by using the $ before the A you fix it to always look in column A. Similarly you can use A$2 to fix the row to always refer to row 2.

 

[gilnic]

Thank you to all who have replied. @Rick Olsen your formula seems to suit my needs. This worked well when I used it in the sheet that I have typed the names in. The data that I download is in K2 attached, and I have used text to columns to obtain the set up as in A& B. Now the formula doesn’t work for those yet when I type in a name at the bottom in A31 it works? I do not understand why? Any thoughts?
Picture available at
http://s1097.photobucket.com/albums/g350/gilnic/

 

[RickOlson]

Without seeing the actual formulas it's hard to see exactly what's going on. Are cols A and B the names, or are they formulas that yield the names. If they have formulas, then Try selecting all the data in columns A and B and using copy, paste-special values. maybe the problem is an artifact of the formula and that will convert the formula result into a string.

 

[gilnic]

Hello,
The formula works well if I type the names in so I know that is successful.What I do is import the names by copying from the site and pasting then clicking match destination formatting,then autofit for the columns,this gives me the result in col L and M in the previous picture.I then use data -text to columns and ( and then )as the delimiter to separate the names and end up with the results in col A and B.So there is no formula used.There is something stopping this formula from working as I can add a name in the row following and it works.

 

[RickOlson]

Looking closely at your picture, it looks like col B has a space at the beginning of the name. I was using trim to get rid of the spaces and I'd have expected it to get rid of that one, too. If I type " Craig Robertson" in cell B7 (without the quotes, but with the leading space) I get something that looks like your B7. Then if I enter =trim(b7) into d7 the leading space goes away as I expected it to. So I can't recreate the problem. I wonder whether the thing that looks like a space at the beginning of Col B isn't really a space. Try putting CLEAN() inside the TRIMs:
=IF(RIGHT(TRIM(CLEAN(B7)),LEN(TRIM(CLEAN(B7)))-FIND(" ",TRIM(CLEAN(B7))))=RIGHT(TRIM(CLEAN(A7)),LEN(TRIM(CLEAN(A7)))-FIND(" ",TRIM(CLEAN(A7)))),TRUE,"")

or
In cell C7 enter =trim(clean(A7))
In D7 enter =trim(clean(B7))
Now you don't need the trim function in the duplicate finder.
In E7 enter =IF(RIGHT(D7,LEN(D7)-FIND(" ",D7)) =RIGHT(C7,LEN(C7)-FIND(" ",C7)),TRUE,"")


As a point of reference:
FIND(" ",C7)) should be giving the character # where the space appears in C7
FIND(" ",D7)) should be giving the character # where the space appears in D7
Can you check those values to see whether the formula gives the same result you get by entering the cell and counting?