Have a data table that contains the following information. This table
will eventually contain thousands of records, so I've just included a
sample below.

ID Title Type Value Elapsed
PR583089 PL10 - rec6 Goods £2,640.00 5d0h1m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583091 PL151 req2 Service £3,900.00 3d0h3m
PR583091 PL151 req2 Service £3,900.00 3d0h3m

TABLE 1:
Type SLA
Goods 3d
Service 5d

I need to be able to analyse how many 'unique' IDs have an elapsed time
of greater than the entries within Table 1. In the example extract
above PR583090 appears 3 times and PR583091 2 times so these only need
to be counted twice. So in total there would be 3 entries. The table
for the above would then show:

Goods Services
In SLA 1 1
Out SLA 1

1 goods entry in SLA (PR583090), 1 goods out of SLA (PR583089) and one
service entry in SLA (PR583091).

Is this possible @ all?

Thanks in advance as always and Happy New Year.

Cheers, Al.

Didn't get how to differentiate between Goods In and Out, but this counts
Goods

=COUNT(1/IF($C$2:$C$20="Goods",MATCH($A$2:$A$20,$A$2:$A$20, 0)=ROW($A$2:$A$10)-ROW($A$2)+1))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Have a data table that contains the following information. This table
will eventually contain thousands of records, so I've just included a
sample below.

ID Title Type Value Elapsed
PR583089 PL10 - rec6 Goods £2,640.00 5d0h1m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583091 PL151 req2 Service £3,900.00 3d0h3m
PR583091 PL151 req2 Service £3,900.00 3d0h3m

TABLE 1:
Type SLA
Goods 3d
Service 5d

I need to be able to analyse how many 'unique' IDs have an elapsed time
of greater than the entries within Table 1. In the example extract
above PR583090 appears 3 times and PR583091 2 times so these only need
to be counted twice. So in total there would be 3 entries. The table
for the above would then show:

Goods Services
In SLA 1 1
Out SLA 1

1 goods entry in SLA (PR583090), 1 goods out of SLA (PR583089) and one
service entry in SLA (PR583091).

Is this possible @ all?

Thanks in advance as always and Happy New Year.

Cheers, Al.

Hi Bob,

Thanks for your quick response on this. In SLA/Out basically means if
it was a goods and within 3 days (elapsed column) then this is within
the SLA set. For Services this is set @ 5 days.

Sorry to be a pain, is it possible to explain how the formula works as
well? Just helps me to understand the use of these which is useful for
future reference!.

Thanks in advance, Al.

Bob Phillips wrote:
Didn't get how to differentiate between Goods In and Out, but this counts
Goods

=COUNT(1/IF($C$2:$C$20="Goods",MATCH($A$2:$A$20,$A$2:$A$20, 0)=ROW($A$2:$A$10)-ROW($A$2)+1))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Have a data table that contains the following information. This table
will eventually contain thousands of records, so I've just included a
sample below.

ID Title Type Value Elapsed
PR583089 PL10 - rec6 Goods £2,640.00 5d0h1m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583091 PL151 req2 Service £3,900.00 3d0h3m
PR583091 PL151 req2 Service £3,900.00 3d0h3m

TABLE 1:
Type SLA
Goods 3d
Service 5d

I need to be able to analyse how many 'unique' IDs have an elapsed time
of greater than the entries within Table 1. In the example extract
above PR583090 appears 3 times and PR583091 2 times so these only need
to be counted twice. So in total there would be 3 entries. The table
for the above would then show:

Goods Services
In SLA 1 1
Out SLA 1

1 goods entry in SLA (PR583090), 1 goods out of SLA (PR583089) and one
service entry in SLA (PR583091).

Is this possible @ all?

Thanks in advance as always and Happy New Year.

Cheers, Al.

OK, I will take a stab

First, the simple but. This part of the formula
ROW($A$2:$A$10)-ROW($A$2)+1
gets an array of index numbers. This part of the formula (index_nums)
returns
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
in our example.

Secondly, we get an array of first positions (first_positions) of the ids to
count for all types
MATCH($A$2:$A$20,$A$2:$A$20,0)
which evaluates to
{1;2;2;4;4;4;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}
(that is PR583080 is position 1, the first PR583090 is index 2, etc.

first_positions is checked against the index_nums,
MATCH($A$2:$A$20,$A$2:$A$20,0)=ROW($A$2:$A$10)-ROW($A$2)+1
which returns an array (first_instances) of Ids of TRUE/FALSE, evaluating to
{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}
which returns a TRUE for the first instance of each ID.

As we are only looking for a Type of Goods, this array of first_instances
is limited by checking for Goods in the Type
IF($C$2:$C$20="Goods",MATCH($A$2:$A$20,$A$2:$A$20, 0)=ROW($A$2:$A$20)-ROW($A$2)+1)
which returns an array (first_goods_instances) of Ids of TREU/FALSE,
evaluatin to
{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FAL SE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE ;FALSE;FALSE}

The first_goods_instances array is used to divide into 1 to get an array of
1 (for the TRUEs) and #DIV/0 (for the FALSEs). This array aligns to the
first instance of each Id where the Type Goods. The COUNT is simply used to
COUNT them, COUNT only counts numbers.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Hi Bob,

Thanks for your quick response on this. In SLA/Out basically means if
it was a goods and within 3 days (elapsed column) then this is within
the SLA set. For Services this is set @ 5 days.

Sorry to be a pain, is it possible to explain how the formula works as
well? Just helps me to understand the use of these which is useful for
future reference!.

Thanks in advance, Al.

Bob Phillips wrote:
Didn't get how to differentiate between Goods In and Out, but this counts
Goods

=COUNT(1/IF($C$2:$C$20="Goods",MATCH($A$2:$A$20,$A$2:$A$20, 0)=ROW($A$2:$A$10)-ROW($A$2)+1))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Have a data table that contains the following information. This table
will eventually contain thousands of records, so I've just included a
sample below.

ID Title Type Value Elapsed
PR583089 PL10 - rec6 Goods £2,640.00 5d0h1m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583090 PL151 req1 Goods £3,900.00 0d0h7m
PR583091 PL151 req2 Service £3,900.00 3d0h3m
PR583091 PL151 req2 Service £3,900.00 3d0h3m

TABLE 1:
Type SLA
Goods 3d
Service 5d

I need to be able to analyse how many 'unique' IDs have an elapsed time
of greater than the entries within Table 1. In the example extract
above PR583090 appears 3 times and PR583091 2 times so these only need
to be counted twice. So in total there would be 3 entries. The table
for the above would then show:

Goods Services
In SLA 1 1
Out SLA 1

1 goods entry in SLA (PR583090), 1 goods out of SLA (PR583089) and one
service entry in SLA (PR583091).

Is this possible @ all?

Thanks in advance as always and Happy New Year.

Cheers, Al.