Find Duplicate Texts among rows using macro

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
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.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Ron99

Active Member
Joined
Feb 10, 2010
Messages
340
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Dec 2, 2015
Messages
168
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

Active Member
Joined
Feb 10, 2010
Messages
340
Office Version
  1. 2016
Platform
  1. Windows
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

NAME
PARENT/HUSBAND
NAME1
PARENT/HUSBAND 1
COUNT
PAUL
ROBERT
=LEFT(A12,3)
=LEFT(B12,3)
=COUNTIF($C$12:C12,C12)
SHAWN
SIMPSON
SHA
SIM
1
PAULERS
ROB
PAU
ROB
1
SHAWNY
SIMP
SHA
SIM
2

<tbody>
</tbody>
 
Last edited:

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
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

NAMEPARENT/HUSBANDNAME1PARENT/HUSBAND 1COUNT
PAULROBERT =LEFT(A12,3) =LEFT(B12,3) =COUNTIF($C$12:C12,C12)
SHAWNSIMPSONSHASIM1
PAULERSROBPAUROB1
SHAWNYSIMPSHASIM2

<tbody>
</tbody>

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

Board Regular
Joined
Dec 2, 2015
Messages
168

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
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)


ABC
1PAULROBERTPercent
2SHAWNSIMPSON1.61%
3PAULERSROB44.44%
4SHAWNYSIMP3.70%
5PooLROBaRT37.04%
6PAULROBERT100.00%
7PAULERSROB44.44%
8SHAWNYSIMP3.70%

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,884
Members
414,413
Latest member
Sinbin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top