Count duplicates in an array?
I am using Office 2003 on Windows XP.
I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
Count duplicates in an array?
If we are talking all worksheet cells here, and say the array is in M1:M8,
then use =COUNTIF($M$1:$M$8,A1) -- HTH RP (remove nothere from the email address if mailing direct) "quartz" wrote in message ... I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
Count duplicates in an array?
Thanks Bob, but no, this array is already in code and contains dynamic
data collected from multiple sheets based on user's input - on the fly. Any further help appreciated... "Bob Phillips" wrote: If we are talking all worksheet cells here, and say the array is in M1:M8, then use =COUNTIF($M$1:$M$8,A1) -- HTH RP (remove nothere from the email address if mailing direct) "quartz" wrote in message ... I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
Count duplicates in an array?
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook (watch for word wrap) Sub testIt3a() Dim arrColor, arrUniq, arrNum, arrOutput Dim iRows As Long, i As Long Dim rng As Range arrColor = Array("Red", "Blue", "Red", "Orange", "Orange", "Red", "Green", "Blue") arrUniq = ArrayUniques(arrColor) iRows = UBound(arrUniq, 1) ReDim arrNum(1 To iRows, 1 To 1) For i = 1 To iRows arrNum(i, 1) = ArrayCountIf(arrColor, arrUniq(i, 1)) Next ReDim arrOutput(1 To iRows, 1 To 2) For i = 1 To iRows arrOutput(i, 1) = arrUniq(i, 1) arrOutput(i, 2) = arrNum(i, 1) Next Set rng = Range("A1").Resize(iRows, 2) rng.Value = arrOutput End Sub Alan Beban quartz wrote: I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
Count duplicates in an array?
quartz wrote: I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. If you include a reference to the Scripting Runtime Library (which is included in XP) you can use the dictionary object: Sub CountDuplicates(A As Variant) 'needs to include a reference to Microsoft Scripting Runtime 'go to Tools/Refrences Dim D As Dictionary Dim i As Long, key As Variant Set D = New Dictionary For i = LBound(A) To UBound(A) key = A(i) If D.Exists(key) Then D.Item(key) = D.Item(key) + 1 Else D.Add key, 1 End If Next i i = 0 For Each key In D.Keys Range("A1").Offset(i).Value = key Range("B1").Offset(i).Value = D.Item(key) i = i + 1 Next key End Sub Hope that helps -John Coleman |
Count duplicates in an array?
If it is just a single dimension, zero based array, the only way I can see
is to loop through each element and check it. -- HTH RP (remove nothere from the email address if mailing direct) "quartz" wrote in message ... Thanks Bob, but no, this array is already in code and contains dynamic data collected from multiple sheets based on user's input - on the fly. Any further help appreciated... "Bob Phillips" wrote: If we are talking all worksheet cells here, and say the array is in M1:M8, then use =COUNTIF($M$1:$M$8,A1) -- HTH RP (remove nothere from the email address if mailing direct) "quartz" wrote in message ... I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
Count duplicates in an array?
I am not sure now if it is faster (definitely not if the number of items is
small), but an elegant way to do this is with SQL. This is how that would go: Dump the array in a sheet and give the range a name, say colours. Have a field name in the very first row, say colour. So for example your colours will be in A2 to A9 and in A1 you have the text colour. Then the range A1:A9 will be named colours. Then in a normal module you have the following code: Sub RunSheetSQL() Dim rs As ADODB.Recordset Dim objCommand As ADODB.Command Dim strSheetConn As String Dim objField As ADODB.Field Dim strQuery As String Dim strWBPath As String Dim i As Long On Error GoTo ERROROUT strWBPath = ActiveWorkbook.FullName Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:\TempSave.xls" If InStr(1, strWBPath, ":\", vbBinaryCompare) 0 Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs strWBPath Else ActiveWorkbook.SaveAs "C:\TempSave2.xls" End If Application.DisplayAlerts = True strSheetConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\TempSave.xls;" & _ "Extended Properties=Excel 8.0;" Set rs = New ADODB.Recordset strQuery = "select " & _ "colour as colour, " & _ "count(colour) as colour_count " & _ "from " & _ "colours " & _ "group by colour " & _ "order by 2 desc" rs.Open Source:=strQuery, _ ActiveConnection:=strSheetConn, _ CursorType:=adOpenForwardOnly, _ LockType:=adLockReadOnly, _ Options:=adCmdText If Not rs.EOF Then Cells(2, 5).CopyFromRecordset rs i = 5 For Each objField In rs.Fields Cells(i) = objField.Name i = i + 1 Next End If If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close End If Set rs = Nothing End If Exit Sub ERROROUT: End Sub You will need a reference to the Microsoft ActiveX Data Objects library. This is under Tools, References in the Visual Basic editor. Then just run the above code. It may look complicated, but once you understand it you can adapt it and use it for all kind of sheet range manipulations. This SQL query is a select query, but you can do an update type of query as well, so then you can alter the data in the table you are running it on. You could run the same code without saving and closing the workbook first, but there is a memory leak bug in this and you will see that the amount of memory taken up by Excel in the Windows task manager will go up and up with every run of this code. So you have to run it on a closed workbook, not an open workbook. If for some reason you don't want to run it on a worksheet range you could run it on a text file, but that would involve more code. Another option would be to use Access, but that would involve even more code. RBS "quartz" wrote in message ... I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
Count duplicates in an array?
Many thanks to all who responded.
"quartz" wrote: I am using Office 2003 on Windows XP. I need to be able to count the number of each item in a single element array, then write out the counts. For example, if the array contains: Red, Blue, Red, Orange, Orange, Red, Green, Blue My result would be: A1: Red B1: 3 A2: Blue B2: 2 A3: Orange B3: 2 A4: Green B4: 1 If anyone has a function to which I could pass my array to output the counts or could share some code I could adapt it would be most appreciated. Thanks much in advance. |
All times are GMT +1. The time now is 04:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com