Count of Unique Values in Table of data

M

mralmackay

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.
 
B

Bob Phillips

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)


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.
 
M

mralmackay

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.
 
B

Bob Phillips

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;FALSE;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)


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top