Duplicate Names - Yet it will not show as Duplicate in Excel

[Shreesurya]

Hi,I am struck with Below issue, and I have Data around 60,000 and unable to find a way to remove or identify the duplicates in the below Table,I would Greatly appreciate any kind of help on this.Excel 2012

	A	B	C	D	E	F
1	CONCATENATE - Company Info	Company	Address	City	State	ZIP+4
2	Mass General Hospital, 55 Fruit Street Mailcode LI 044, Boston, E2, 21142696	Mass General Hospital	55 Fruit Street Mailcode LI 044	Boston	MA	02114-2696
3	Mass General Hospital, 55 Fruit St Mailcode LI 044, Boston, E2, 21142696	Mass General Hospital	55 Fruit St Mailcode LI 044	Boston	MA	02114-2696
4	Intelisent, 880 Marshall Phelps Road, Windsor, E2, 60952107	Intelisent	880 Marshall Phelps Road	Windsor	CT	06095-2107
5	Intelisent, , LLC., 880 Marshall Phelps Rd, Windsor, E2, 60952107	Intelisent, , LLC.	880 Marshall Phelps Rd	Windsor	CT	06095-2107
6	Starts Sampling, 195 East Elk Trail, Carol Stream, E2, 601889300	Starts Sampling	195 East Elk Trail	Carol Stream	IL	60188-9300
7	Starts Sampling, 195 E. Elk Trail, Carol Stream, E2, 601889300	Starts Sampling	195 E. Elk Trail	Carol Stream	IL	60188-9300
8	Grayhair Software, 124 Gaither Drive Ste 160, Mt Laurel, E2, 08054-1719	Grayhair Software	124 Gaither Drive Ste 160	Mt Laurel	NJ	08054-1719
9	Grayhair Software Inc, 124 Gaither Dr Ste 160, Mt Laurel, E2, 08054-1719	Grayhair Software Inc	124 Gaither Dr Ste 160	Mt Laurel	NJ	08054-1719
10	Newkirk, LLC, 15 Corporate Circle, Albany, E2, 12203-5177	Newkirk, LLC	15 Corporate Circle	Albany	NY	12203-5177
11	Newkirk, 15 Corporate Cir, Albany, E2, 12203-5177	Newkirk	15 Corporate Cir	Albany	NY	12203-5177

<colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[JLGWhiz]

This would give you about a 95% probability of eliminating duplicates. Might require minimal editing.

Sub delstuff()
Dim sh As Worksheet, lr As Long, i As Long, spAry As Variant, spArr As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
            spAry = Split(.Cells(i, 3).Value, " ")
            spArr = Split(.Cells(i - 1, 3).Value, " ")
            If spAry(LBound(spAry)) = spArr(LBound(spArr)) And Left(spAry(LBound(spAry) + 1), 1) = _
                Left(spArr(LBound(spArr) + 1), 1) And .Cells(i, 6) = .Cells(i - 1, 6) Then
                    Rows(i).Delete xlUp
            End If
        End With
    Next
End Sub

The only ones that I calculate would be missed would be if somebody is dislexic and reverses the sequence of the street address or if two comapanys share the same address.

 

[Shreesurya]

Thank you So Much For your Code JLGWhiz, Truly Awesome,

However I don't want to delete the duplicate, It should be highlighted, Request you to help to modify the Code...

 

[JLGWhiz]

Thank you So Much For your Code JLGWhiz, Truly Awesome,

However I don't want to delete the duplicate, It should be highlighted, Request you to help to modify the Code...

This should highlight the duplicate rows.

Sub delstuff()
Dim sh As Worksheet, lr As Long, i As Long, spAry As Variant, spArr As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
            spAry = Split(.Cells(i, 3).Value, " ")
            spArr = Split(.Cells(i - 1, 3).Value, " ")
            If spAry(LBound(spAry)) = spArr(LBound(spArr)) And Left(spAry(LBound(spAry) + 1), 1) = _
                Left(spArr(LBound(spArr) + 1), 1) And .Cells(i, 6) = .Cells(i - 1, 6) Then
                    .Cells(i - 1, 1).Resize(2, 1). EntireRow.Interior.ColorIndex = 6
            End If
        End With
    Next
End Sub

 

[Shreesurya]

Thank you So much this code works for me….

 

[JLGWhiz]

You're welcome,
Regards, JLG