Counting sequences of values

[MetricsGuy]

I want to count a mixed sequence of values contained in a single column and show how many of each value occur, in order. Here's some sample data and desired output:

Sample data:
A
A
A
B
A
B
B
A
A
B

Desired output:
A 3
B 1
A 1
B 2
A 2
B 1

How can this be done (preferably not using VBA, but if that's how it needs to be done, so be it)?

Thanks.

 

[rob_sheeds]

hmm I am thinking....

 

[AdamL]

If your column of letters is A1:A100, then maybe in C1:

C1: =A1

and in D1 (confirmed with Ctrl+Shift+Enter):

D1: =MATCH(TRUE,A1:A100<>C1,0)-1

and then in C2:

=OFFSET(A$1,SUM(D$1:D1),0)

and in D2 (confirmed with Ctrl+Shift+Enter):

=MATCH(TRUE,OFFSET(A$1,SUM(D$1:D1),0,100)<>C2,0)-1

and then fill C2 and D2 as far down as needed.

Pretty quick and dirty solution to be honest, but maybe a starting point.

edit...

you could tidy up the C2 and D2 formulae a bit with:

C2: =OFFSET(A$1,SUM(D$1:D1),0)&""
D2: =IF(C2="","",MATCH(TRUE,OFFSET(A$1,SUM(D$1:D1),0,100)<>C2,0)-1)

 

[MetricsGuy]

Yup, quick and dirty worked great. Many thanks!

Any recommended reading for me to understand how the "confirmed" functionality works? I'm not accustomed to array handling but am interested in learning. Can't quite get my brain wrapped around embedding ranges in functions (except those like SUM()).