Getting the top five incidents
 

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

Getting the top five incidents
 

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

Getting the top five incidents
 

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

Getting the top five incidents
 

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

Getting the top five incidents
 

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

Getting the top five incidents
 

"Danny Lewis" skrev i en meddelelse
...
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.

Getting the top five incidents
 

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.


Danny Lewis Wrote:
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


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

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=564285

Getting the top five incidents
 

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" skrev i en meddelelse
...
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.

Getting the top five incidents
 

"Danny Lewis" 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

Getting the top five incidents
 

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

Getting the top five incidents
 

"Danny Lewis" skrev i en meddelelse
...
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" 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

Getting the top five incidents
 

Sorry Leo!

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

"Leo Heuser" wrote:

"Danny Lewis" skrev i en meddelelse
...
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" 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