List the duplicate cells by row number

[kingleviathan]

Hi Guys

I appreciate your help with this..

What I wish to do is...

Look in Column V for duplicates....

and then list the row numbers the duplicates are in in column AA

So if cell V2, V3, V6, V18, V33 are duplicates then in AA1 list 2, AA2 list 3, AA3 list 6, AA4 list 18 and AA5 list 33

Some Notes:
1) it is possible there may be no duplicates
2) it only needs to list the duplicates - it does not need to list the row with the first instance (ie..if Apple is in V1, V3, V5 - then it only needs 3 and 5 listed
3) It needs to list all instances of duplicates regardless of the source for example v1 might be cat, v2 might be dog, v3 might be cat and v4 might be dog so AA1 should be 3 and AA2 should be 4

Hope this is an easy one for you to help me with - as i have no idea how to code such

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG08May11
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
    [COLOR=navy]Set[/COLOR] Rng = Range(Range("V1"), Range("V" & rows.Count).End(xlUp))
        [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, ""
    [COLOR=navy]Else[/COLOR]
         n = n + 1
         Range("AA" & n) = Dn.row
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[kingleviathan]

Thanks MickG

It works a treat - much appreciate your help

 

[Peter_SSs]

Here is a simpler approach to try.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DupeRows()<br>    <SPAN style="color:#00007F">With</SPAN> Range("AA1:AA" & Range("V" & Rows.Count).End(xlUp).Row)<br>        .Formula = "=IF(COUNTIF(V$1:V1,V1)>1,ROW(),"""")"<br>        .Value = .Value<br>        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[kingleviathan]

Hi PeterSS

Your code would be great except in this case delete by shift up wont work because im wanting to delete the row within a linked range to sharepoint which is why i need to use my delete method...

I hope this makes sense...

 

[Peter_SSs]

Hi PeterSS

Your code would be great except in this case delete by shift up wont work because im wanting to delete the row within a linked range to sharepoint which is why i need to use my delete method...

I hope this makes sense...

Can you explain in more detail?

 

[Peter_SSs]

Would this be any better?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DupeRows()<br>    <SPAN style="color:#00007F">With</SPAN> Range("AA1:AA" & Range("V" & Rows.Count).End(xlUp).Row)<br>        .Formula = "=IF(COUNTIF(V$1:V1,V1)>1,ROW(),"""")"<br>        .Value = .Value<br>        .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[kingleviathan]

Hi Peter SS

Sorry, I made a mistake MickG code works great. I will try your code tomorrow as it may still be useful (when i have access to the file)

I thought you were suggesting a solution for another forum I have going so got a bit mixed up. Sorry for taking up your time but i appreciate your efforts

 

[Peter_SSs]

MickG code works great.

I agree. I was just trying for a simpler, more direct way of doing it.