Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=526310 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=526310 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
On Sat, 25 Mar 2006 21:29:33 -0600, tahir
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
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" az alábbiakat írta a következő hírüzenetben: ... 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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=526310 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
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 "tahir" wrote: 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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=526310 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting of Duplicate Values in a column
Worksheetfuction method:
=COUNTIF(A:A, 5400) VBA method: Sub Test() MsgBox Application.CountIf(Columns(1), 5400) End Sub Regards, Greg "tahir" wrote: 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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=526310 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells with duplicate values | Excel Discussion (Misc queries) | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Highlighting duplicate values in a column | Excel Discussion (Misc queries) | |||
can i not allow duplicate values in the same column? | New Users to Excel | |||
Sum of duplicate values in a column | Excel Programming |