Forum Discussion
Question related to finding duplicates
Let's say the first/middle/last names are in A2:A50 and the last/middle/first names in B2:B50.
Select A2:A50.
I will assume that A2 is the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))&" "&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),255,255))&" "&TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",255)),255)),$B$2:$B$50,0))
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Next, select B2:B50.
Do the same as above, but with the formula
=ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),255))&" "&TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",255)),255,255))&" "&TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",255)),255)),$A$2:$A$50,0))
Sir it helps. Thank you so much.
Sir it is difficult to understand this formula. Can you please explain this formula?