Counting of Duplicate Values in a column

[tahir]

Hi Friends,

I m new 2 VBA, i have a sheet that has got a many columns on it, but
the column that is of my interest is, is column A, This column contains
report numbers, I need a way out through some VBA code that could help
me to count how many times a report number has been repeated in that
column. The report numbers are infinite, and keep on growing. at the
moment we r on 5400 reprot no and every day it increases. tomorrow it
might move to 6000 depending on frequency of reprots generated. So my
query is to count that how many times a report number is repeated in
column A.

Regards,


Darno

 

[broro183]

Hi Darno,

The link below doesn't give a VBA solution but does suggest ways of
identifying duplicates etc using a helper column, to count # of
appearances try:
=COUNTIF(Range1,Range1)

This is a shortened version of a function shown on Chip's page:
http://www.cpearson.com/excel/duplicat.htm

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[baj]

First you write the Sub (or Function) you need to Count (I make the
supposal that all the reportnumbers are on sheet "Reportnumbers" in
Column A from cell 1 till 50000) :

Sub Search(reportnr as integer)

With Sheets("Reportnumbers")
List = .Range(.Cells(1, 1), .Cells(50000,1))
End With

i=1
for i=1 to UBound(List)

if List(i,1) = reportnr then
counter = counter + 1
endif

next

End Sub


Then you call this Sub in your main program...

Sub Main ()
(...)

nr = InputBox("Give the reportnumber please : ")
Search (nr)

(...)

End Sub



Try to make it work on your situation..

Greetings

Baj

 

[baj]

SORRY FORGOT SOMETHING
CORRECTED TEXT :


Sub Search(reportnr as integer)

counter =0

With Sheets("Reportnumbers")
List = .Range(.Cells(1, 1), .Cells(50000,1))
End With


i=1
for i=1 to UBound(List)


if List(i,1) = reportnr then
counter = counter + 1
endif

next

Msgbox("Reportnr. appears : " & counter)

End Sub

 

[Ron Rosenfeld]

Hi Friends,

I m new 2 VBA, i have a sheet that has got a many columns on it, but
the column that is of my interest is, is column A, This column contains
report numbers, I need a way out through some VBA code that could help
me to count how many times a report number has been repeated in that
column. The report numbers are infinite, and keep on growing. at the
moment we r on 5400 reprot no and every day it increases. tomorrow it
might move to 6000 depending on frequency of reprots generated. So my
query is to count that how many times a report number is repeated in
column A.

Regards,


Darno

What, exactly, do you want for output and where do you want to see it?

To get a list of duplicated report numbers and how many times they have been
duplicated, in sorted order, you could use something like the code below. The
output, in this case, is in sorted order and printed in the "immediate window".
But you could output it anyplace.

You also might be able to use the SubTotals or Pivot Table wizards to generate
a report.

==============================
Option Explicit

Sub Dups()
Dim c As Range, a As Range
Dim ReportNum() As Long
Dim ReportCount As Long
Dim i As Long


Set a = [A:A]
Set a = a.Resize(Application.WorksheetFunction.Count(a))
ReDim ReportNum(1 To a.Rows.Count)
For i = 1 To UBound(ReportNum)
ReportNum(i) = a(i).Value
Next i

SingleBubbleSort ReportNum

For i = 1 To UBound(ReportNum)
ReportCount = Application.WorksheetFunction.CountIf(a, ReportNum(i))
If ReportCount > 1 Then
Debug.Print "Report Number: " & ReportNum(i) & _
" Count: " & ReportCount
End If
i = i + ReportCount - 1
Next i
End Sub

Sub SingleBubbleSort(TempArray As Variant)
'copied directly from support.microsoft.com
Dim temp As Variant
Dim i As Integer
Dim NoExchanges As Integer

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 1 To UBound(TempArray) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) > TempArray(i + 1) Then
NoExchanges = False
temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = temp
End If
Next i
Loop While Not (NoExchanges)
End Sub
==============================
--ron

 

[Gerencsér Gábor]

The following macro sorts the Column A ascending and then marks the
duplicate entries yellow.
You then have to flip through the list and check for yellow cells to see
which ones are using the same number.
(I guess you do not only want to know how many running numbers are used more
than once, but you also want to do something with them, right?)
The macro assumes your first row is a header and your second row is the
first data row.
If it is not the case you have to change parameters.
You could assign the macro to a little rectangle object at the top of the
sheet, so you can run it comfortably.

Gabor


Sub MarkDoubles() 'GG, 10/12/2002

Dim Dbls As Integer
Dim Msg
Dbls = 0
Range("A12").Select

Selection.Sort Key1:=Range("A12"), Order1:=xlAscending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Do Until ActiveCell = Empty

If ActiveCell = ActiveCell.Offset(1, 0) Or ActiveCell Like
ActiveCell.Offset(1, 0) Then
Selection.Interior.ColorIndex = 6
ActiveCell.Offset(1, 0).Interior.ColorIndex = 6
Dbls = Dbls + 1
End If

ActiveCell.Offset(1, 0).Select

Loop

Range("A1").Select
Msg = MsgBox("There are about " & Dbls & " reports that used occupied
numbers", vbOKOnly, "Double-Counter")

End Sub

"tahir" <[email protected]> az alábbiakat
írta a következõ hírüzenetben:
(e-mail address removed)...

 

[Guest]

Tahir,

You can do this without resorting to code, check out the 'Subtotals'
function in the data menu, this will be able to do exactly what you want.

HTH

Neil
www.nwarwick.co.uk

 

[Guest]

Worksheetfuction method:
=COUNTIF(A:A, 5400)

VBA method:
Sub Test()
MsgBox Application.CountIf(Columns(1), 5400)
End Sub

Regards,
Greg