Counting values in arrays

[upstate_steve]

How do I get from a vertical single-field array to an array of the sam
size, each original value converted to a count of that value in th
original array?

E.g.,

1 -> 3
0 -> 2
1 -> 3
0 -> 2
1 -> 3
2 -> 1


I start with a calculated array, not a cell range, so (I'm assuming
{=COUNTIF(Array,Array)} won't work.

Steve Przyborski
Boston, Mass

 

[Peter Atherton]

Two methods using an array formula and countif

Assuming that the data starts in A13. Type the following
and drag down - the range will expand as you do
=COUNTIF(A$13:A13,A13)

Alternatively use this formula
=COUNT(IF($A13=A$13:A13,A$13:A13))

enter as array (Ctl + Shift + Enter) and drag down

Regards
Peter

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you could
consider the ArrayCountIf function; e.g.:

Sub test3010()
Dim arr1(), arr2(0 To 5)
arr1 = Array(1, 0, 1, 0, 1, 2)
For i = 0 To 5
arr2(i) = ArrayCountIf(arr1, arr1(i))
Next
End Sub

Alan Beban

 

[Alan Beban]

My previous post did not deal with vertical arrays as you specified
(though I'm not sure what you meant by a "single-field" vertical array);
the following does:

Sub test3010()
Dim arr1(), arr2(1 To 6, 1 To 1)
ReDim arr1(1 To 6, 1 To 1)
arr1 = ArrayTranspose(Array(1, 0, 1, 0, 1, 2))
For i = 1 To 6: For j = 1 To 1
arr2(i, 1) = ArrayCountIf(arr1, arr1(i, 1))
Next: Next
End Sub

Alan Beban

 

[Alan Beban]

I had the Option Base 1 Statement in effect.

Alan Beban

 

[Alan Beban]

The following is the general form, independent of the Option Base Statement:

Sub test3010()
Dim arr1(), arr2()
arr1 = ArrayTranspose(Array(1, 0, 1, 0, 1, 2))
ReDim arr2(LBound(arr1) To UBound(arr1), _
LBound(arr1, 2) To UBound(arr1, 2))
For i = LBound(arr1) To UBound(arr1)
arr2(i, LBound(arr1, 2)) = _
ArrayCountIf(arr1, arr1(i, LBound(arr1, 2)))
Next
End Sub

Alan Beban

 

[Leo Heuser]

Steve

One way:

=MMULT((Array=TRANSPOSE(Array))+0,(Array=Array)+0)

The formula is an array formula and must be entered
with <Shift><Ctrl><Enter>, also if edited later. If done
correctly, Excel will display the formula in the formula bar
enclosed in curly brackets { } Don't enter these brackets
yourself.

For a horizontal single-field array use:

=MMULT((Array=Array)+0,(Array=TRANSPOSE(Array))+0)

 

[upstate_steve]

Dear Leo Heuser:

I am blown away and completely intimidated by this simple, elegan
solution.

Holy living Christ, it worked.

Steve Przyborski
Boston, Mass

 

[Leo Heuser]

Glad you could use it, Steve.
Thanks for the heartwarming words