Hi,

Please can anyone help with a series of array formulas, I receive data
from a scheduled incident report, which looks a bit like the following:

Group Sub GP Duration < 1hr Duration 1-2hrs Duration 2hrs
A 0 0 2
5
A 1 0 3
0
A 1a 5 0
12
A 2 9 0
15
B 0 1 1
11
B 1 0 0
0
B 1a 2 1
0
B 2 0 2
1

The report I receive is normally contains about 2000 groups and approx
25 more duration columns so I need something set up where i just paste
the report into a spreadsheet and it calculates:

All Group totals ie Group A, including all sub groups and all
durations, so for the above data the result would be: Group A = 51
incidents Group B = 19

All sub group totals, so again for the above data the result would be:
GroupA Sub Group 0 = 7
Sub Group 1 = 3
Sub Group 1a = 17 etc

Any help would be greatly appreciated

Thanks

flick.

Can you just clarify how you want the data to be presented? Do you want
a table which shows the Groups and a separate table showing the
sub-groups? Something like:

Total
A x
B x
C x

and

Total
A 0 x
A 1 x
A 1a x
A 2 x
B 0 x
B 1 x
B 1a x
B 2 x


If so, how many main groups do you have (and are these really letters),
and what sub-groups do you have? Will it be okay to have these summary
tables in a separate sheet, so that you can paste your data into Sheet1
each time? If you have 25 more duration columns, does this mean your
main data sheet goes out to column AD ?

Pete

Hi,

I have upto 2000 main groups which are all Teams within a department ie
CST, TAGS1, MNG1 etc. Incidents raised for each team lie in 1 of 7 sub
groups which are 0, 1, 1a, 2, 2a, 3 and 4. And then they sit in 1 of 15
depending on the duration to date of the incident. Durations are <1 hr,
1-2 hrs, 3-7hrs, 7-10hrs etc

Tables you have shown would be a great way to display I just cant get
my head around it. No problem with pasting data into one sheet and
displaying table results into another.

Thanks and much appreciated

Flick. x

It sounds as though a Pivot Table would do what you want, and do so very
easily.

Debra Dalgleish has a ton of tips about pivot tables on her site

http://www.contextures.com/tiptech.html


" wrote:

Hi,

Please can anyone help with a series of array formulas, I receive data
from a scheduled incident report, which looks a bit like the following:

Group Sub GP Duration < 1hr Duration 1-2hrs Duration 2hrs
A 0 0 2
5
A 1 0 3
0
A 1a 5 0
12
A 2 9 0
15
B 0 1 1
11
B 1 0 0
0
B 1a 2 1
0
B 2 0 2
1

The report I receive is normally contains about 2000 groups and approx
25 more duration columns so I need something set up where i just paste
the report into a spreadsheet and it calculates:

All Group totals ie Group A, including all sub groups and all
durations, so for the above data the result would be: Group A = 51
incidents Group B = 19

All sub group totals, so again for the above data the result would be:
GroupA Sub Group 0 = 7
Sub Group 1 = 3
Sub Group 1a = 17 etc

Any help would be greatly appreciated

Thanks

flick.