Collapsing data from a single column (Excel or Access or whatever)

[Greg the dogsitter]

I have data...something like this:

1 A
1 B
2 A
2 B
3 C
3 D
4 A
4 B
5 C
5 D
6 E
6 F

I want to show the total count of each pair of letters, like so:
A B 3
C D 2
E F 1

I can't think of a way to pull this off besides using a script to turn the pairs into two columns, and then sorting and grouping. Is there a better way?

GtDS

[Don C]

So let's say that the first letter is cell A2.

In B2 type the formula, =if(b1="",a1&a2,"")
This will give you a blank in one row and a combination in the next row.

If you have just a few combinations in column B, a series of =countif(b1:Bn,"AB") would get you your tallies. If there are quite a few, then a pivot table on column B will get you the counts.

There are, of course, other solutions!
Don

[Greg the dogsitter]

Much thanks, Don!