VBa code to select from worksheet
 

I have some columns like this below except that there are 700 rows.

Date Status
3/7/2006 CLOSED
3/7/2006 CLOSED
3/7/2006 CANCEL
3/7/2006 OPEN
3/7/2006 OPEN
3/7/2006 OPEN

I need to be able to produce output like this:

Date Status Count
3/7/2006 CLOSED 2
3/7/2006 CANCEL 1
3/7/2006 OPEN 3

I was wondering if excel can do something like:

select a, b, count(a) as c
from worksheet1
group by a, b

Any chance?

Mike

VBa code to select from worksheet
 

I don't understand your pseudo code but I think your first list pretty much
exposes a reasonable path to creating the second list. Your first list is
almost sorted by date as a primary sort key and status as a secondary sort
key. If it were really sorted the CANCEL transaction would be first. Once
sorted it is a simple matter to iterate through the list, count consecutive
identical entries, and output items in the 2nd list. Unfortunately Excel
VBA doesn't have a sort function (or maybe I never found it) so I typically
use a fairly simple bubble sort that is implemented in about 10 lines of
code. Your sort would be a little more complex because you have two keys
but you can easily form a single pseudo key out of the two keys and sort on
the pseudo key.

Note that you might be able to invoke a spreadsheet function to do the
sorting but I never thought that writing my own sort was difficult enough to
motivate me to look at that option.

Luke

"mike" wrote in message
ups.com...
I have some columns like this below except that there are 700 rows.

Date Status
3/7/2006 CLOSED
3/7/2006 CLOSED
3/7/2006 CANCEL
3/7/2006 OPEN
3/7/2006 OPEN
3/7/2006 OPEN

I need to be able to produce output like this:

Date Status Count
3/7/2006 CLOSED 2
3/7/2006 CANCEL 1
3/7/2006 OPEN 3

I was wondering if excel can do something like:

select a, b, count(a) as c
from worksheet1
group by a, b

Any chance?

Mike

VBa code to select from worksheet
 

You can use a pivot table. Place that activecell in the data set and select
Data - Pivot Table - Finish

Drage the dates to the far left and place the status next to it. finally
drop the date field into the center of the report. It will come up wit a
count for you...
--
HTH...

Jim Thomlinson


"mike" wrote:

I have some columns like this below except that there are 700 rows.

Date Status
3/7/2006 CLOSED
3/7/2006 CLOSED
3/7/2006 CANCEL
3/7/2006 OPEN
3/7/2006 OPEN
3/7/2006 OPEN

I need to be able to produce output like this:

Date Status Count
3/7/2006 CLOSED 2
3/7/2006 CANCEL 1
3/7/2006 OPEN 3

I was wondering if excel can do something like:

select a, b, count(a) as c
from worksheet1
group by a, b

Any chance?

Mike