Forum Discussion
Question related to finding duplicates
Hello Everyone,
How to find the duplicate values When the data of First column with First Name ,Middle Name, Last Name and the other column with Last Name, Middle Name, First Name
Like -
Please help...???
6 Replies
Please mention attached file and picture to this message box.
Split the second table into columns based upon delimetr and then using Textjoin function concatenate them.
Copy newly formed column values to the first table (column).
From Data menu, select Remove Duplciates.
I think this can help you.
If you find this solution as a best one, please mention.
Thank you so much sir. It helps me lot😊
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?
