+- MacResource (https://forums.macresource.com)
+-- Forum: My Category (https://forums.macresource.com/forumdisplay.php?fid=1)
+--- Forum: Tips and Deals (https://forums.macresource.com/forumdisplay.php?fid=3)
+--- Thread: Collapsing data from a single column (Excel or Access or whatever) (/showthread.php?tid=7518)
Collapsing data from a single column (Excel or Access or whatever) - Greg the dogsitter - 03-20-2006
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
Re: Collapsing data from a single column (Excel or Access or whatever) - Don C - 03-21-2006
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
Re: Collapsing data from a single column (Excel or Access or whatever) - Greg the dogsitter - 03-21-2006
Much thanks, Don!