Find Duplicate Texts among rows using macro

[Jyotirmaya]

In My sheet there are 2000 names & their Father or husbands names are there in Column A & B.
Column A is the name & Column B is thier father's name or husbands name.
I want to find the duplicate values among the 2000 names. But I cant use the Conditional Formatting - Highlight cell rules - Duplicate values in case of the names because the data entry of the names are not having unique values.
For example Ram father name is Peter, in another row its shown as Rama Father name is Pitter. Is there any solution to Find that If any of the particular row's column A & B's 50% text matches with another Row's Text of Column A & B then it will highlight with a colour then my problem can be solved.

 

[Ron99]

Hi,

Apart from the name is there any other unique number or ID ? example staff ID, etc. I am just going by the example which you have provided. It looks more like a typo error which can be rectified though another unique ID / NUMBER.

 

[Jyotirmaya]

Hi,Apart from the name is there any other unique number or ID ? example staff ID, etc. I am just going by the example which you have provided. It looks more like a typo error which can be rectified though another unique ID / NUMBER.

No there are no unique number is associated with the name. The database is created by data entry by different persons hence if someone wrote Peter then another person wrote Pitter. Now that's difficult to find the duplicate rows.

 

[Ron99]

Then you can try this, you will have to add additional columns, like below. After you drag the formula then filter the header and sort by "NAME1". Should make it easy to identify. If this works probably same logic can be applied for macro

[TABLE="width: 439"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: #44546A"]NAME
[/TD]
[TD="class: xl66, width: 125, bgcolor: #44546A"]PARENT/HUSBAND
[/TD]
[TD="class: xl66, width: 88, bgcolor: #44546A"]NAME1
[/TD]
[TD="class: xl66, width: 135, bgcolor: #44546A"]PARENT/HUSBAND 1
[/TD]
[TD="class: xl66, width: 173, bgcolor: #44546A"]COUNT
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]PAUL
[/TD]
[TD="class: xl65, bgcolor: transparent"]ROBERT
[/TD]
[TD="class: xl67, bgcolor: transparent"] =LEFT(A12,3)
[/TD]
[TD="class: xl65, bgcolor: transparent"] =LEFT(B12,3)
[/TD]
[TD="class: xl65, bgcolor: transparent"] =COUNTIF($C$12:C12,C12)
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SHAWN
[/TD]
[TD="class: xl65, bgcolor: transparent"]SIMPSON
[/TD]
[TD="class: xl65, bgcolor: transparent"]SHA
[/TD]
[TD="class: xl65, bgcolor: transparent"]SIM
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]PAULERS
[/TD]
[TD="class: xl65, bgcolor: transparent"]ROB
[/TD]
[TD="class: xl65, bgcolor: transparent"]PAU
[/TD]
[TD="class: xl65, bgcolor: transparent"]ROB
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SHAWNY
[/TD]
[TD="class: xl65, bgcolor: transparent"]SIMP
[/TD]
[TD="class: xl65, bgcolor: transparent"]SHA
[/TD]
[TD="class: xl65, bgcolor: transparent"]SIM
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

I recommend that you review the following link: (See post # 2)


https://www.mrexcel.com/forum/excel...zy-matching-new-version-plus-explanation.html

And this is the syntax =fuzzyvlookup(A1,B1:B5,1,,,1)

 

[Jyotirmaya]

Then you can try this, you will have to add additional columns, like below. After you drag the formula then filter the header and sort by "NAME1". Should make it easy to identify. If this works probably same logic can be applied for macro

[TABLE="width: 439"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: #44546A"]NAME[/TD]
[TD="class: xl66, width: 125, bgcolor: #44546A"]PARENT/HUSBAND[/TD]
[TD="class: xl66, width: 88, bgcolor: #44546A"]NAME1[/TD]
[TD="class: xl66, width: 135, bgcolor: #44546A"]PARENT/HUSBAND 1[/TD]
[TD="class: xl66, width: 173, bgcolor: #44546A"]COUNT[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]PAUL[/TD]
[TD="class: xl65, bgcolor: transparent"]ROBERT[/TD]
[TD="class: xl67, bgcolor: transparent"] =LEFT(A12,3)[/TD]
[TD="class: xl65, bgcolor: transparent"] =LEFT(B12,3)[/TD]
[TD="class: xl65, bgcolor: transparent"] =COUNTIF($C$12:C12,C12)[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SHAWN[/TD]
[TD="class: xl65, bgcolor: transparent"]SIMPSON[/TD]
[TD="class: xl65, bgcolor: transparent"]SHA[/TD]
[TD="class: xl65, bgcolor: transparent"]SIM[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]PAULERS[/TD]
[TD="class: xl65, bgcolor: transparent"]ROB[/TD]
[TD="class: xl65, bgcolor: transparent"]PAU[/TD]
[TD="class: xl65, bgcolor: transparent"]ROB[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SHAWNY[/TD]
[TD="class: xl65, bgcolor: transparent"]SIMP[/TD]
[TD="class: xl65, bgcolor: transparent"]SHA[/TD]
[TD="class: xl65, bgcolor: transparent"]SIM[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the idea, but sorry this will not work for me because the first 3 letters of name also doesn't match in many of my database my database is full of errors such as names are Robert Rubert Rubbertt Ruubert Robort, hence this will not work.

 

[Jyotirmaya]

Hi,

I recommend that you review the following link: (See post # 2)


https://www.mrexcel.com/forum/excel...zy-matching-new-version-plus-explanation.html

And this is the syntax =fuzzyvlookup(A1,B1:B5,1,,,1)

The above formula only shows the Vlookup value, but when I am trying to find the Percentage among the Duplicate values of B column I am getting Error.
here A6 is 1
=fuzzypercent($B6,B6:B4000,A$6)

I am getting Error #Value !

 

[admiral100]

Hi,

The formula (fuzzypercent) is not an array formula, so you need to compare A1&B1 (PAUL ROBERTS) to the rest of the lines...

C2 =fuzzypercent($A$1&$B$1,A2&B2,3)


[TABLE="width: 296"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAUL[/TD]
[TD]ROBERT[/TD]
[TD]Percent[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SHAWN[/TD]
[TD]SIMPSON[/TD]
[TD]1.61%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PAULERS[/TD]
[TD]ROB[/TD]
[TD]44.44%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SHAWNY[/TD]
[TD]SIMP[/TD]
[TD]3.70%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PooL[/TD]
[TD]ROBaRT[/TD]
[TD]37.04%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PAUL[/TD]
[TD]ROBERT[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PAULERS[/TD]
[TD]ROB[/TD]
[TD]44.44%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]SHAWNY[/TD]
[TD]SIMP[/TD]
[TD]3.70%[/TD]
[/TR]
</tbody>[/TABLE]