Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Duplicates

  1. #11
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicates

    I think you are looking for only duplicates number in one column eg. 1,2,4 etc. right?? or do you need the count also??

  2. #12
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicates

    This might not be the cleanliest code but try:

    Code:
    Sub Duplicatevalues()Dim lr As Long, r As Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    For r = 1 To lr
          
        If Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & r)) > 1 Then Range("A" & r).Copy Destination:=Range("C" & ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1)
    Next r
    ActiveSheet.Range("C:C").RemoveDuplicates Columns:=1, Header:=xlNo
    
    End Sub

  3. #13
    Board Regular proficient's Avatar
    Join Date
    Apr 2012
    Location
    Karachi, Pakistan.
    Posts
    699
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Duplicates

    Quote Originally Posted by norulen View Post
    I think you are looking for only duplicates number in one column eg. 1,2,4 etc. right?? or do you need the count also??

    Yes! I am looking for only duplicate numbers...

  4. #14
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicates

    Did the macro work for you??

  5. #15
    Board Regular proficient's Avatar
    Join Date
    Apr 2012
    Location
    Karachi, Pakistan.
    Posts
    699
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Duplicates

    No...

  6. #16
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicates

    It seems to working totally fine for the sample you gave. I rechecked. what is the error that you are getting?

  7. #17
    Board Regular
    Join Date
    Nov 2012
    Posts
    389
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicates

    Try this: I have made some small changes. This uses Helper column as AA which will automatically get deleted in the end. But make sure you do not have any data there. If you do change the coloumn reference

    Code:
    Sub Duplicatevalues()
    Dim lr As Long, r As Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    For r = 1 To lr
          Range("AA" & r).Value = Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & r))
        If Range("AA" & r).Value > 1 Then Range("A" & r).Copy Destination:=Range("C" & ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1)
        
    Next r
    ActiveSheet.Range("C:C").RemoveDuplicates Columns:=1, Header:=xlNo
    Columns("AA:AA").ClearContents
    End Sub

  8. #18
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,902
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Duplicates

    Quote Originally Posted by proficient View Post
    I want to find duplicates numbers in a range



    Duplicates Value

    A B C D
    1 1 1 4
    2 2 2 6
    3 3 4 3
    4 4
    5 6
    6 1
    7 2
    8 2
    9 4
    10 65
    11 1
    12 2
    13 2
    14 4
    15 25
    16 1
    17 2

    Spreadsheet Formulas
    Cell Formula
    D1 =COUNTIF($A$1:$A$17,C1)
    D2 =COUNTIF($A$1:$A$17,C2)
    D3 =COUNTIF($A$1:$A$17,C3)


    Excel tables to the web >> Excel Jeanie HTML 4
    This seems to be what you are looking for:
    Extract a list of duplicates from a column using array formula in excel | Get Digital Help - Microsoft Excel resource
    [code]your code[/code]

  9. #19
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,682
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Duplicates

    A possible solution using formulas

    A B C D E
    Numbers DuplicatesList Count Duplicates
    1 1 4 3
    2 2 6
    3 4 3
    4
    6
    1
    2
    2
    4
    65
    1
    2
    2
    4
    25
    1
    2


    Formula in E2 to get the count of duplicates
    =SUMPRODUCT(--(COUNTIF($A$2:$A$18,$A$2:$A$18)>1),--(MATCH($A$2:$A$18,$A$2:A18,0)=ROW($A$2:$A$18)-ROW($A$2)+1))

    Array formula in C2
    =IF($E$2>=ROWS($C$2:C2),INDEX(A:A,MIN(IF(COUNTIF($A$2:$A$18,$A$2:$A$18)>1,IF(ISNA(MATCH($A$2:$A$18,$C$1:C1,0)),ROW($A$2:$A$18))))),"")

    confirmed with Ctrl+Shift+Enter
    (hold down both Ctrl and Shift keys and hit Enter)

    copy down

    Formula in D2
    =IF(C2<>"",COUNTIF(A:A,C2),"")

    copy down

    M.






  10. #20
    Banned user
    Join Date
    Nov 2010
    Posts
    2,075
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicates

    Proficient,

    Try this code
    Code:
    Sub dups()
    Dim c
    With CreateObject("scripting.dictionary")
    For Each c In Range("A1", Cells(Rows.Count, "a").End(3)).Value
        .Item(c) = .Item(c) + 1
    Next
    For Each c In .keys
        If .Item(c) = 1 Then .Remove c
    Next
    Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    End With
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •