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.

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" <quartz@discussions.microsoft.com> wrote in message
news:9090C79C-E40A-4AF8-A151-CDDBB2130EC5@microsoft.com...
> 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.

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" <quartz@discussions.microsoft.com> wrote in message
> news:9090C79C-E40A-4AF8-A151-CDDBB2130EC5@microsoft.com...
> > 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 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" <quartz@discussions.microsoft.com> wrote in message
news:1C11EB20-F462-46C9-A29F-9E3E61DB9DEC@microsoft.com...
> 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" <quartz@discussions.microsoft.com> wrote in message
> > news:9090C79C-E40A-4AF8-A151-CDDBB2130EC5@microsoft.com...
> > > 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 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.

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" <quartz@discussions.microsoft.com> wrote in message
news:9090C79C-E40A-4AF8-A151-CDDBB2130EC5@microsoft.com...
>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.

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.