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:

 

[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]

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:

 

[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

 

[Leo Heuser]

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

 

[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
=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 cop
down to five rows
in col E =VLOOKUP(C6,[Book1.xls]Sheet1!$D$6:$G$15,3,FALSE) and cop
down to five rows
in col F =VLOOKUP(C6,[Book1.xls]Sheet1!$D$6:$G$15,4,FALSE) and cop
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 th
process
efficiency, removing the need for them. You don't see any quicker wa
i.e.
just a formula?

Cheers

+-------------------------------------------------------------------
|Filename: Sample Files.zip
|Download: http://www.excelforum.com/attachment.php?postid=5080
+-------------------------------------------------------------------

 

[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]

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

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]

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 D224

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

"Danny Lewis" <[email protected]> skrev i en
meddelelse

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

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 D224

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

"Danny Lewis" <[email protected]> skrev i en
meddelelse
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