Getting the top five incidents

[Danny Lewis]

Hi all

I wonder if you can help me, I've racked my brains, but cannot find a
solution.

I have a large table of data with e.g. the following headings

Incident Number
Date
Incident Description
Minutes 1
Minutes 2

Now in this table there are maybe 1500 records.

Is there any way I can, in another spreadsheet in another workbook, create a
table displaying, in descending order by Minutes 1, the top 5 Incidents?

I thought about using LARGE and INDEX&MATCH on the minutes but it won't work
because the minutes figure may not be unique.

Many many many thanks in anticipation

Danny

[Marcelo]

Hi Danny,

I think the pivot table is the best way here.

hth
regards from Brazil
Marcelo

"Danny Lewis" escreveu:

> Hi all
>
> I wonder if you can help me, I've racked my brains, but cannot find a
> solution.
>
> I have a large table of data with e.g. the following headings
>
> Incident Number
> Date
> Incident Description
> Minutes 1
> Minutes 2
>
> Now in this table there are maybe 1500 records.
>
> Is there any way I can, in another spreadsheet in another workbook, create a
> table displaying, in descending order by Minutes 1, the top 5 Incidents?
>
> I thought about using LARGE and INDEX&MATCH on the minutes but it won't work
> because the minutes figure may not be unique.
>
> Many many many thanks in anticipation
>
> Danny

[Danny Lewis]

Thanks Marcelo

That is the way I have been doing it but I'm trying to improve the process
efficiency, removing the need for them. You don't see any quicker way i.e.
just a formula?

Cheers

"Marcelo" wrote:

> Hi Danny,
>
> I think the pivot table is the best way here.
>
> hth
> regards from Brazil
> Marcelo
>
> "Danny Lewis" escreveu:
>
> > Hi all
> >
> > I wonder if you can help me, I've racked my brains, but cannot find a
> > solution.
> >
> > I have a large table of data with e.g. the following headings
> >
> > Incident Number
> > Date
> > Incident Description
> > Minutes 1
> > Minutes 2
> >
> > Now in this table there are maybe 1500 records.
> >
> > Is there any way I can, in another spreadsheet in another workbook, create a
> > table displaying, in descending order by Minutes 1, the top 5 Incidents?
> >
> > I thought about using LARGE and INDEX&MATCH on the minutes but it won't work
> > because the minutes figure may not be unique.
> >
> > Many many many thanks in anticipation
> >
> > Danny

[Marcelo]

Hi Danny,

maybe the rank function could help you take a look at Chip Person Web page at:

http://www.cpearson.com/excel/rank.htm

hth
regards from Brazil
Marcelo

"Danny Lewis" escreveu:

> Thanks Marcelo
>
> That is the way I have been doing it but I'm trying to improve the process
> efficiency, removing the need for them. You don't see any quicker way i.e.
> just a formula?
>
> Cheers
>
> "Marcelo" wrote:
>
> > Hi Danny,
> >
> > I think the pivot table is the best way here.
> >
> > hth
> > regards from Brazil
> > Marcelo
> >
> > "Danny Lewis" escreveu:
> >
> > > Hi all
> > >
> > > I wonder if you can help me, I've racked my brains, but cannot find a
> > > solution.
> > >
> > > I have a large table of data with e.g. the following headings
> > >
> > > Incident Number
> > > Date
> > > Incident Description
> > > Minutes 1
> > > Minutes 2
> > >
> > > Now in this table there are maybe 1500 records.
> > >
> > > Is there any way I can, in another spreadsheet in another workbook, create a
> > > table displaying, in descending order by Minutes 1, the top 5 Incidents?
> > >
> > > I thought about using LARGE and INDEX&MATCH on the minutes but it won't work
> > > because the minutes figure may not be unique.
> > >
> > > Many many many thanks in anticipation
> > >
> > > Danny

[Danny Lewis]

Yeah that crossed my mind earlier but I can't work out how

I guess there's no way of picking them out.

Thanks fot the help

"Marcelo" wrote:

> Hi Danny,
>
> maybe the rank function could help you take a look at Chip Person Web page at:
>
> http://www.cpearson.com/excel/rank.htm
>
> hth
> regards from Brazil
> Marcelo
>
> "Danny Lewis" escreveu:
>
> > Thanks Marcelo
> >
> > That is the way I have been doing it but I'm trying to improve the process
> > efficiency, removing the need for them. You don't see any quicker way i.e.
> > just a formula?
> >
> > Cheers
> >
> > "Marcelo" wrote:
> >
> > > Hi Danny,
> > >
> > > I think the pivot table is the best way here.
> > >
> > > hth
> > > regards from Brazil
> > > Marcelo
> > >
> > > "Danny Lewis" escreveu:
> > >
> > > > Hi all
> > > >
> > > > I wonder if you can help me, I've racked my brains, but cannot find a
> > > > solution.
> > > >
> > > > I have a large table of data with e.g. the following headings
> > > >
> > > > Incident Number
> > > > Date
> > > > Incident Description
> > > > Minutes 1
> > > > Minutes 2
> > > >
> > > > Now in this table there are maybe 1500 records.
> > > >
> > > > Is there any way I can, in another spreadsheet in another workbook, create a
> > > > table displaying, in descending order by Minutes 1, the top 5 Incidents?
> > > >
> > > > I thought about using LARGE and INDEX&MATCH on the minutes but it won't work
> > > > because the minutes figure may not be unique.
> > > >
> > > > Many many many thanks in anticipation
> > > >
> > > > Danny

[starguy]

suppose your data is in book1 sheet1 having data in following columns
col C Incident Number
col D Date
col E Incident Description
col F Minutes 1
col G Minutes 2
and your data is in the range of $C$6:$G$15 in book 1

use following formuals in book2 (i suppose it your another book)

in col C =VLOOKUP(LARGE([Book1.xls]Sheet1!$C$6:$C$15,1),[Book1.xls]Sheet1!$C$6:$G$15,2,FALSE) copy it down to five rows but change bold value in LARGE function as 2, 3, 4, 5 in following rows respectively.
in col D =VLOOKUP(C6,[Book1.xls]Sheet1!$D$6:$G$15,2,FALSE) and copy down to five rows
in col E =VLOOKUP(C6,[Book1.xls]Sheet1!$D$6:$G$15,3,FALSE) and copy down to five rows
in col F =VLOOKUP(C6,[Book1.xls]Sheet1!$D$6:$G$15,4,FALSE) and copy down to five rows
now you can sort by minutes 1
I have attached the sample files for this process.

hope this would help you.

Thanks Marcelo

That is the way I have been doing it but I'm trying to improve the process
efficiency, removing the need for them. You don't see any quicker way i.e.
just a formula?

Cheers

"Marcelo" wrote:

> Hi Danny,
>
> I think the pivot table is the best way here.
>
> hth
> regards from Brazil
> Marcelo
>
> "Danny Lewis" escreveu:
>
> > Hi all
> >
> > I wonder if you can help me, I've racked my brains, but cannot find a
> > solution.
> >
> > I have a large table of data with e.g. the following headings
> >
> > Incident Number
> > Date
> > Incident Description
> > Minutes 1
> > Minutes 2
> >
> > Now in this table there are maybe 1500 records.
> >
> > Is there any way I can, in another spreadsheet in another workbook, create a
> > table displaying, in descending order by Minutes 1, the top 5 Incidents?
> >
> > I thought about using LARGE and INDEX&MATCH on the minutes but it won't work
> > because the minutes figure may not be unique.
> >
> > Many many many thanks in anticipation
> >
> > Danny

[Leo Heuser]

"Danny Lewis" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Hi all
>
> I wonder if you can help me, I've racked my brains, but cannot find a
> solution.
>
> I have a large table of data with e.g. the following headings
>
> Incident Number
> Date
> Incident Description
> Minutes 1
> Minutes 2
>
> Now in this table there are maybe 1500 records.
>
> Is there any way I can, in another spreadsheet in another workbook, create
> a
> table displaying, in descending order by Minutes 1, the top 5 Incidents?
>
> I thought about using LARGE and INDEX&MATCH on the minutes but it won't
> work
> because the minutes figure may not be unique.
>
> Many many many thanks in anticipation
>
> Danny


Hi Danny

One way to do it, assuming "Minutes 1" in C2:C24

In F2 (F1 must be empty or not hold data found in C2:C24)

=MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24))

This is an array formula, and it must be entered with
<Shift><Ctrl><Enter> instead of <Enter>, also if
edited later.

Copy F2 to F3:F6 with the fill handle (the little square in
the lower right corner of the cell).


--
Best regards
Leo Heuser

Followup to newsgroup only please.

[Danny Lewis]

Thanks Leo I tried yours but was a bit confused...any chance of being a bit
clearer? Would be hugely appreciated - I did manage to write the formula so
it brought up unique values only (is that what it's meant to do?)

Starguy that file didnt work - any chance of trying again???

Thanks chaps

Danny

"Leo Heuser" wrote:

> "Danny Lewis" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > Hi all
> >
> > I wonder if you can help me, I've racked my brains, but cannot find a
> > solution.
> >
> > I have a large table of data with e.g. the following headings
> >
> > Incident Number
> > Date
> > Incident Description
> > Minutes 1
> > Minutes 2
> >
> > Now in this table there are maybe 1500 records.
> >
> > Is there any way I can, in another spreadsheet in another workbook, create
> > a
> > table displaying, in descending order by Minutes 1, the top 5 Incidents?
> >
> > I thought about using LARGE and INDEX&MATCH on the minutes but it won't
> > work
> > because the minutes figure may not be unique.
> >
> > Many many many thanks in anticipation
> >
> > Danny
>
>
> Hi Danny
>
> One way to do it, assuming "Minutes 1" in C2:C24
>
> In F2 (F1 must be empty or not hold data found in C2:C24)
>
> =MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24))
>
> This is an array formula, and it must be entered with
> <Shift><Ctrl><Enter> instead of <Enter>, also if
> edited later.
>
> Copy F2 to F3:F6 with the fill handle (the little square in
> the lower right corner of the cell).
>
>
> --
> Best regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
>
>

[Leo Heuser]

"Danny Lewis" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Thanks Leo I tried yours but was a bit confused...any chance of being a
> bit
> clearer? Would be hugely appreciated - I did manage to write the formula
> so
> it brought up unique values only (is that what it's meant to do?)

I got the impression, that you had duplicates in "Minute 1" and that you
didn't
want them in your list, so yes my formula brings up unique values only.
How do you enter the minutes in "Minute 1"? As numbers 3, 6, 12, 7, 6 etc or
as time 0:3, 0:6 etc.
If you enter them as time, then select F2:F6 and give it the user-
defined format [mm] to get a list of the top 5 minutes.

Leo Heuser

[Danny Lewis]

It is actually a little more complicated than I said.

There are other columns in the table, but I didnt want it to seem more
complicated. What I need is to pick up records with variables in two other
columns that equal certain things.

For example...

Column
A: Incident No
B: Date
C: Description
D: Minutes 1
E: Minutes 2
F: Function (eg Engineering)
G: Area

I need to find the top 5 incidents in terms of minutes 1, where Function is
x, and Area is y...

There's a pickle for ya lol...

"Leo Heuser" wrote:

> "Danny Lewis" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > Thanks Leo I tried yours but was a bit confused...any chance of being a
> > bit
> > clearer? Would be hugely appreciated - I did manage to write the formula
> > so
> > it brought up unique values only (is that what it's meant to do?)
>
> I got the impression, that you had duplicates in "Minute 1" and that you
> didn't
> want them in your list, so yes my formula brings up unique values only.
> How do you enter the minutes in "Minute 1"? As numbers 3, 6, 12, 7, 6 etc or
> as time 0:3, 0:6 etc.
> If you enter them as time, then select F2:F6 and give it the user-
> defined format [mm] to get a list of the top 5 minutes.
>
> Leo Heuser
>
>
>

[Leo Heuser]

"Danny Lewis" <[email protected]> skrev i en meddelelse
news:[email protected]...
> It is actually a little more complicated than I said.
>
> There are other columns in the table, but I didnt want it to seem more
> complicated. What I need is to pick up records with variables in two other
> columns that equal certain things.
>
> For example...
>
> Column
> A: Incident No
> B: Date
> C: Description
> D: Minutes 1
> E: Minutes 2
> F: Function (eg Engineering)
> G: Area
>
> I need to find the top 5 incidents in terms of minutes 1, where Function
> is
> x, and Area is y...
>
> There's a pickle for ya lol...

Hi Danny

Don't expect a solution, if you do not disclose all facts from the
beginning :-)

Try this array formula instead with Function in B2:B24 and
Area in D2:D24

If x and y are text:

=MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24)*($B$2:$B$24="x")*($D$2:$D$24="y"))

If x and y are numbers:

=MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24)*($B$2:$B$24=x)*($D$2:$D$24=y))

again to be entered with <Shift><Ctrl><Enter>

I'm beginning to wonder about your remark on not wanting duplicates
in "Minute 1".

If for instance you have the numbers 12,10,8,8,8,7,6
will your top 5 picks be
12,10,8,8,8
or
12,10,8,7,6
?

Leo Heuser


>
> "Leo Heuser" wrote:
>
>> "Danny Lewis" <[email protected]> skrev i en
>> meddelelse
>> news:[email protected]...
>> > Thanks Leo I tried yours but was a bit confused...any chance of being a
>> > bit
>> > clearer? Would be hugely appreciated - I did manage to write the
>> > formula
>> > so
>> > it brought up unique values only (is that what it's meant to do?)
>>
>> I got the impression, that you had duplicates in "Minute 1" and that you
>> didn't
>> want them in your list, so yes my formula brings up unique values only.
>> How do you enter the minutes in "Minute 1"? As numbers 3, 6, 12, 7, 6 etc
>> or
>> as time 0:3, 0:6 etc.
>> If you enter them as time, then select F2:F6 and give it the user-
>> defined format [mm] to get a list of the top 5 minutes.
>>
>> Leo Heuser
>>
>>
>>

[Danny Lewis]

Sorry Leo!

I'm looking for 12,10,8,8,8

"Leo Heuser" wrote:

> "Danny Lewis" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > It is actually a little more complicated than I said.
> >
> > There are other columns in the table, but I didnt want it to seem more
> > complicated. What I need is to pick up records with variables in two other
> > columns that equal certain things.
> >
> > For example...
> >
> > Column
> > A: Incident No
> > B: Date
> > C: Description
> > D: Minutes 1
> > E: Minutes 2
> > F: Function (eg Engineering)
> > G: Area
> >
> > I need to find the top 5 incidents in terms of minutes 1, where Function
> > is
> > x, and Area is y...
> >
> > There's a pickle for ya lol...
>
> Hi Danny
>
> Don't expect a solution, if you do not disclose all facts from the
> beginning :-)
>
> Try this array formula instead with Function in B2:B24 and
> Area in D2:D24
>
> If x and y are text:
>
> =MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24)*($B$2:$B$24="x")*($D$2:$D$24="y"))
>
> If x and y are numbers:
>
> =MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24)*($B$2:$B$24=x)*($D$2:$D$24=y))
>
> again to be entered with <Shift><Ctrl><Enter>
>
> I'm beginning to wonder about your remark on not wanting duplicates
> in "Minute 1".
>
> If for instance you have the numbers 12,10,8,8,8,7,6
> will your top 5 picks be
> 12,10,8,8,8
> or
> 12,10,8,7,6
> ?
>
> Leo Heuser
>
>
> >
> > "Leo Heuser" wrote:
> >
> >> "Danny Lewis" <[email protected]> skrev i en
> >> meddelelse
> >> news:[email protected]...
> >> > Thanks Leo I tried yours but was a bit confused...any chance of being a
> >> > bit
> >> > clearer? Would be hugely appreciated - I did manage to write the
> >> > formula
> >> > so
> >> > it brought up unique values only (is that what it's meant to do?)
> >>
> >> I got the impression, that you had duplicates in "Minute 1" and that you
> >> didn't
> >> want them in your list, so yes my formula brings up unique values only.
> >> How do you enter the minutes in "Minute 1"? As numbers 3, 6, 12, 7, 6 etc
> >> or
> >> as time 0:3, 0:6 etc.
> >> If you enter them as time, then select F2:F6 and give it the user-
> >> defined format [mm] to get a list of the top 5 minutes.
> >>
> >> Leo Heuser
> >>
> >>
> >>
>
>
>
>