Identifying duplicate location

markstro

Board Regular
Joined
Sep 8, 2008
Messages
106
Using this formula from another post, how can I identify the location of the duplicate entry??
=IF(COUNTIF(A2:$A$521,A2)>1,"DUPLICATE","")

I have identified a lot of duplicates, finding them is another problem.

I cannot simply sort and delete as each row has data I have to incorporate into one row before I delete the other.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Although not a Formula, this code will return all the Duplicate addresses in Column "A", in Column "C".
Code:
[COLOR="Navy"]Sub[/COLOR] MG03Dec34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
.Add Dn.Value, Array(Dn.Address, "@")
[COLOR="Navy"]Else[/COLOR]
Q = .Item(Dn.Value)
    [COLOR="Navy"]If[/COLOR] Q(1) = "@" [COLOR="Navy"]Then[/COLOR]
        c = c + 1: Q(1) = c
        ray(Q(1)) = Dn.Value & " // " & Q(0) & "," & Dn.Address
    [COLOR="Navy"]Else[/COLOR]
        ray(Q(1)) = ray(Q(1)) & "," & Dn.Address
    [COLOR="Navy"]End[/COLOR] If
.Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(c) = Application.Transpose(ray)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
All formulas copied down. Perhaps column C or D may be useful to you. Note, though that if there are multiple duplications of the same value, this will only identify one of the matches.

Excel Workbook
ABCD
1ValueDuplicate?First DuplicateDuplicate Above
2aDUPLICATEMatch at row 9 
3bDUPLICATEMatch at row 5
4c
5bDUPLICATEMatch at row 13See row 3
6dDUPLICATEMatch at row 14
7e
8f
9aSee row 2
10g
11h
12i
13bSee row 3
14dSee row 6
15j
16
Duplicates
 

markstro

Board Regular
Joined
Sep 8, 2008
Messages
106
Great solutions, before I received the answers I thought of just sorting by the column with all the duplicates, then conditional format each cell to change color if it matches the cell below. That way all the duplicates are together and I can then resort back to the original format.

Either way, I have to type and copy some formula or other. Thanks for all the help Mick, Mike & Peter. I will have to try them all and choose the easiest one to pass on to fellow employees.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,517
Members
413,995
Latest member
waealu

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