Board index » Excel » Counting values in arrays

Excel20
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



--

Message posted from http://www.ExcelForum.com


-

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

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



upstate_steve < wrote:

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



upstate_steve < wrote:

I had the Option Base 1 Statement in effect.



Alan Beban



Alan Beban wrote:

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



upstate_steve < wrote:

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)



--

Best Regards

Leo Heuser



Followup to newsgroup only please.



"upstate_steve>" <<upstate_steve.14ivn3@excelforum-nospam.com>skrev i en

meddelelse news:upstate_steve.14ivn3@excelforum-nospam.com...





-

Dear Leo Heuser:



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

solution.



Holy living Christ, it worked.



Steve Przyborski

Boston, Mass



--

Message posted from http://www.ExcelForum.com



-

Glad you could use it, Steve.

Thanks for the heartwarming words :-)



--

Best Regards

Leo Heuser





"upstate_steve>" <<upstate_steve.14k1ql@excelforum-nospam.com>skrev i en

meddelelse news:upstate_steve.14k1ql@excelforum-nospam.com...



-