Question related to finding duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-2131727%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20finding%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131727%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%3CSPAN%3EHow%20to%20find%20the%20duplicate%20values%20When%20the%20data%20of%20First%20column%20with%20First%20Name%20%2CMiddle%20Name%2C%20Last%20Name%20and%20the%20other%20column%20with%20Last%20Name%2C%20Middle%20Name%2C%20First%20Name%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ELike%20-%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Web%20capture_13-2-2021_201537_www.udemy.com.jpeg%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254715i7B6C88A5DB33A360%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Web%20capture_13-2-2021_201537_www.udemy.com.jpeg%22%20alt%3D%22Web%20capture_13-2-2021_201537_www.udemy.com.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help..%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2131727%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131752%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20finding%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20first%2Fmiddle%2Flast%20names%20are%20in%20A2%3AA50%20and%20the%20last%2Fmiddle%2Ffirst%20names%20in%20B2%3AB50.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20A2%3AA50.%3C%2FP%3E%0A%3CP%3EI%20will%20assume%20that%20A2%20is%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(A2%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C255))%26amp%3B%22%20%22%26amp%3BTRIM(MID(SUBSTITUTE(A2%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C255%2C255))%26amp%3B%22%20%22%26amp%3BTRIM(LEFT(SUBSTITUTE(A2%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C255))%2C%24B%242%3A%24B%2450%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20highlight%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20select%20B2%3AB50.%3C%2FP%3E%0A%3CP%3EDo%20the%20same%20as%20above%2C%20but%20with%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(B2%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C255))%26amp%3B%22%20%22%26amp%3BTRIM(MID(SUBSTITUTE(B2%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C255%2C255))%26amp%3B%22%20%22%26amp%3BTRIM(LEFT(SUBSTITUTE(B2%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C255))%2C%24A%242%3A%24A%2450%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131767%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20finding%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20mention%20attached%20file%20and%20picture%20to%20this%20message%20box.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESplit%20the%20second%20table%20into%20columns%20based%20upon%20delimetr%20and%20then%20using%20Textjoin%20function%20concatenate%20them.%3C%2FP%3E%3CP%3ECopy%20newly%20formed%20column%20values%20to%20the%20first%20table%20(column).%3C%2FP%3E%3CP%3EFrom%20Data%20menu%2C%20select%20Remove%20Duplciates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20can%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ilgar_Zarbaliyev_0-1613228981740.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254716i98178F3A0BCA04A6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Ilgar_Zarbaliyev_0-1613228981740.png%22%20alt%3D%22Ilgar_Zarbaliyev_0-1613228981740.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20solution%20as%20a%20best%20one%2C%20please%20mention.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

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 - 

Web capture_13-2-2021_201537_www.udemy.com.jpeg

 

Please help...???

6 Replies

@Zan_Hanifee 

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))

@Zan_Hanifee 

 

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.

 

Ilgar_Zarbaliyev_0-1613228981740.png

 

 

If you find this solution as a best one, please mention.

 

 

@Hans Vogelaar 

Sir it helps. Thank you so much. 

 

Sir it is difficult to understand this formula. Can you please explain this formula?

@Ilgar_Zarbaliyev 

Thank you so much sir. It helps me lot

@Hans Vogelaar 

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.

@Hans Vogelaar 

Now i fully understand. Thank you so much sir