Identifying duplicate location

[markstro]

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.

 

[MickG]

Hi, Although not a Formula, this code will return all the Duplicate addresses in Column "A", in Column "C".

[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

 

[mikerickson]

Autofilter is a quick way to bring them all togther.

 

[Peter_SSs]

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
	A	B	C	D
1	Value	Duplicate?	First Duplicate	Duplicate Above
2	a	DUPLICATE	Match at row 9
3	b	DUPLICATE	Match at row 5
4	c
5	b	DUPLICATE	Match at row 13	See row 3
6	d	DUPLICATE	Match at row 14
7	e
8	f
9	a			See row 2
10	g
11	h
12	i
13	b			See row 3
14	d			See row 6
15	j
16
Duplicates

 

[markstro]

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.