R

#### RJB

Some orders have just one row of data, some have two rows of data,

some have three, some have four, etc.

Each row represents either a delay or an activity. (So, obviously, an

order can have more than one of either.)

I need to aggregate into one row:

Order | Type of Order | Num. of Delays | Total Length of Delays

HERE'S THE MANUAL WAY I'M DOING THIS NOW:

======================================

My initial columns are:

Order Number (A) | Type of Order (B) | Action Code (C) | Action Length

(D)

I added a "marker column" - Delay Length (E):

=IF(C= a delay, D, "Activity")

In other words, if the Action Code indicates a delay, put the length

of delay in cell E. Otherwise, put in text.

Then I click the subtotal button, At Each Change in Column 'Order

Number (A)', Use Function.... SUM to Delay Length (E). Then I click

it again, NOT replacing existing, and Use Function... COUNT NUMBER to

Delay Length (E).

So that gives me a table of

* First Work Order

- Number of delays

- Sum of delay time

* SecondWork Order

- Number of delays

- Sum of delay time

* Nth Work Order

- Number of delays

- Sum of delay time

THEN I copy Visible Cells and post into another worksheet and then

start manipulating.

So.... Is there a spiffy way to do this with formulas? I could crack

this if every order had the same number of rows, but it does NOT.

-----

EXAMPLE DATA TABLE

Order Number (A) | Type of Order (B) | Action Code (C) | Action Length

(D) | Delay Length (E)

111 | Rebuild | Delay | 0:10 | 0:10

111 | Rebuild | Shipping | 0:15 | Activity

112 | New | Manuf | 0:50 | Activity

112 | New | Packing | 0:40 | Activity

112 | New | Delay | 0:12 | 0:12

113 | Scrub | Prep | 0:20 | Activity

113 | Scrub | Delay | 0:10 | 0:10

113 | Scrub | Delay | 0:16 | 0:16

113 | Scrub | Packing | 0:05 | Activity

113 | Scrub | Billing | 0:07 | Activity

EXAMPLE SUBTOTAL

Order Number (A) | Type of Order (B) | Action Code (C) | Action Length

(D) | Delay Length (E)

111 Count 1

111 Total 0:10

112 Count 1

112 Total 0:12

113 Count 2

113 Total 0:26

What I'd like?

A worksheet (can I pivot this?) where it automagically takes my data

table and presents/reads:

111 | Rebuild | 1 | 0:10

112 | New | 1 | 0:12

113 | Scrub | 2 | 0:26