This might sound complicated but that is because I am not really up on the lingo - sorry!!
I have workseets - the first has the following data:
A B C
1 Area Error type Total
2 Bradford Food 4
3 Bradford Cleaning 7
4 Bradford Timing 2
5 Bradford Shift 3
6 Dover Food 1
7 Dover Cleaning 4
8 Dover Timing 2
9 Dover Shift 0
10 Leyland Food 22
11 Leyland Cleaning 2
12 Leyland Timing 3
13 Leyland Shift 1
The actual table is much bigger than this but you get the idea. What I want to do is a separate table that Identifies the top 2 issues for an area and puts them in a table ie:
A B C D E
26 Area Highest Error type Highest error value 2nd Highest Error Type 2nd Highest Error Value
27 Leyland Food 22 Timing 3
28 Bradford Cleaning 7 Food 4
If i use the following formula the correct values appear under Highest error value and second highest
=LARGE(IF($A$2:$A$13=Report!$A27, $c$2:$c$13),1) ****IN CELL C27)
=LARGE(IF($A$2:$A$13=Report!$A27, $c$2:$c$13),2) ****IN CELL E27
the problem comes when I want to automatically input the associated error type. I have used:
=INDEX($A$2:$A$13,MATCH(C27,$C$2:$C$13,0),1)
Initially it looked like this had worked, but then I noticed that is searches for the correct number and returns the error type next to the first occasion of that number in C2:C13 - For the example above - Under the Leyland 2nd highest error type it actually returns "Shift" as in the list C2:C13 that is the first time the number 3 occurs .... It should return 'Timing' as this is the second highest number for Leyland.
Can anyone at all help - this is the last rung on a spreadsheet that will create its own reports for the charity I work for but I just cant get it to only look at the part of the array that relates to the Area given (eg Cell A27 - Leyland)
I have workseets - the first has the following data:
A B C
1 Area Error type Total
2 Bradford Food 4
3 Bradford Cleaning 7
4 Bradford Timing 2
5 Bradford Shift 3
6 Dover Food 1
7 Dover Cleaning 4
8 Dover Timing 2
9 Dover Shift 0
10 Leyland Food 22
11 Leyland Cleaning 2
12 Leyland Timing 3
13 Leyland Shift 1
The actual table is much bigger than this but you get the idea. What I want to do is a separate table that Identifies the top 2 issues for an area and puts them in a table ie:
A B C D E
26 Area Highest Error type Highest error value 2nd Highest Error Type 2nd Highest Error Value
27 Leyland Food 22 Timing 3
28 Bradford Cleaning 7 Food 4
If i use the following formula the correct values appear under Highest error value and second highest
=LARGE(IF($A$2:$A$13=Report!$A27, $c$2:$c$13),1) ****IN CELL C27)
=LARGE(IF($A$2:$A$13=Report!$A27, $c$2:$c$13),2) ****IN CELL E27
the problem comes when I want to automatically input the associated error type. I have used:
=INDEX($A$2:$A$13,MATCH(C27,$C$2:$C$13,0),1)
Initially it looked like this had worked, but then I noticed that is searches for the correct number and returns the error type next to the first occasion of that number in C2:C13 - For the example above - Under the Leyland 2nd highest error type it actually returns "Shift" as in the list C2:C13 that is the first time the number 3 occurs .... It should return 'Timing' as this is the second highest number for Leyland.
Can anyone at all help - this is the last rung on a spreadsheet that will create its own reports for the charity I work for but I just cant get it to only look at the part of the array that relates to the Area given (eg Cell A27 - Leyland)
