Find duplicates to show in MsgBox

[MagnusGrey]

Hi All,

I have this problem for a while now, I'm trying to count the duplicates by the last 2 digits of data in 1 column using this formula "=0+(COUNTIF(R2C[-6]:R10000C[-6],RIGHT(RC[-6],2)&""*"")>1)" but it doesn't make the count.

What I'm trying to do is a macro that shows in a MsgBox the 2 digits number with their respective duplicate count for example
40 = 4
54 = 2

8546
9540
4540
4543
8054
5444
6254
9540
5440
5490
5435
5458

<colgroup><col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[igold]

@Marcelo-

You are right. The way I had extended the data there were no numbers that had single occurrences. I just added a few at the bottom and Fazza's code returned the number of occurrences for the newly added data as 1.

igold

 

[Fazza]

whoops - i missed the requirement that only duplicates were to be returned.
Amended code below, also changed for situation of no duplicates
and format as "00"

Sub assumes_numeric_data()

  Const lCOLUMN_TO_ANALYSE As Long = 1 'column with the data


  Dim i As Long, k As Long
  Dim v As Long
  Dim ar() As Variant
  Dim input_data As Variant
  
  input_data = ActiveSheet.UsedRange.Columns(lCOLUMN_TO_ANALYSE).Value2
  ReDim ar(0 To 99)
  
  For i = LBound(input_data) To UBound(input_data)
    If Len(input_data(i, 1)) > 0 Then
      v = CLng(Right$(input_data(i, 1), 2))
      ar(v) = ar(v) + 1
    End If
  Next i
  
  For i = LBound(ar) To UBound(ar)
    If ar(i) > 1 Then 'CHANGED HERE SO ONLY COUNTS > 1 RETURNED
      ar(k) = Format$(i,"00") & " = " & ar(i)
      k = k + 1
    End If
  Next i
  
  'And changed below here to handle no duplicates
  If k > 0 Then
    ReDim Preserve ar(0 To k - 1)
    MsgBox Join$(ar, vbCr)
  Else
    MsgBox "no duplicates"
  End If


End Sub

 

[MagnusGrey]

Wow thank you all!! I just review all codes and it worked like a charm, really appreciate the different ways to approach the goal!!

 

[MagnusGrey]

Would be possible to display the results in a userform msgbox?