"How to:" Scan array and produce summary

[Her_Kitty_Daddy]

I’m sure I’m missing the obvious if so, please accept my apology.

In calc, I have a page that looks like this:

(Yes, this is the simplified version.)

I’d like to be able to automate the process of producing a summary of these cells.

I’m thinking of trying to produce something like:

(Constraint: Contents of cells not predefined but X/Y geography is.)

I had started down the road of reading about arrays and realized I was way over my head.
I started searching here and couldn’t sort out what keywords I should be searching for.

I’m sure I’m not the only one that’s headed down this road so I’m asking for pointers of what I should be looking for (keywords) or advice of just where to start.

However, if anyone thinks it’ll make a good addition to some FAQ some place, by all means, dive in.

Thanks in advance for any pointers.

[JohnSUN]

Probably, this task can be solved using formulas and a pivot table. But I decided that writing a macro would be faster:

Function showStatistic(aData As Variant) As Variant
Dim i As Long, j As Long 
Dim aTemp As Variant
Dim aResult As Variant
	aTemp = Array()
	For i = LBound(aData) To UBound(aData)
		For j = LBound(aData, 2) To UBound(aData, 2)
			If Trim(aData(i, j)) <> "" Then Call countUnique(aData(i, j), aTemp)
		Next j
	Next i
	aResult = DimArray(UBound(aTemp),1)
	For i = LBound(aTemp) To UBound(aTemp)
		aResult(i,0) = aTemp(i)(0)
		aResult(i,1) = aTemp(i)(1)
	Next i
	showStatistic = aResult
End Function

Sub countUnique(key As Variant, aData As Variant)
Dim l&, r&, m&, N&, i&
    l = LBound(aData)
    r = UBound(aData) + 1
    N = r
    While (l < r)
        m = l + Int((r - l) / 2)
        If aData(m)(0) < key Then
            l = m + 1
        Else
            r = m
        End If
    Wend
    If r = N Then
        ReDim Preserve aData(0 To N)
        aData(N) = Array(key, 1)
    ElseIf aData(r)(0) = key Then
        aData(r)(1) = aData(r)(1) + 1
    Else
        ReDim Preserve aData(0 To N)
        For i = N - 1 To r Step -1
            aData(i + 1) = aData(i)
        Next i
        aData(r) = Array(key, 1)
    End If
End Sub

Just write in a cell something like =SHOWSTATISTIC(A1:K23) and press Ctrl+Shift+Enter

[Her_Kitty_Daddy]

Holy crap1 That’s amazing!
Thank you!

I’ll give it a shot right now.

Hey, put your name on it!

[Her_Kitty_Daddy]

Works great! Thank you!