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))
SUBSTITUTE(B2," ",REPT(" ",255)) replaces each space in the name with 255 spaces.
So for instance
"Murthy Krishna Murali"
will be changed to
"Murthy (255 spaces) Krishna (255 spaces) Murali".
RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255) takes the last 255 characters of this string. It will look like
" (lots of spaces) Murali"
TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255)) removes the leading spaces, so that you end up with
"Murali"
By placing this at the beginning of the formula, we move the last part to the front.
Similarly, TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),255,255)) extracts the middle name, and TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",255)),255)) extracts the first part.
Now i fully understand. Thank you so much sir😊
Sir it helps. Thank you so much.
Sir it is difficult to understand this formula. Can you please explain this formula?
