Return a result from an overall array but limit by another value
Results 1 to 8 of 8

Thread: Return a result from an overall array but limit by another value

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return a result from an overall array but limit by another value

    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)

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    Ive just noticed that is has ditched my spacing, The ABC at the top of the example tables relate to column A B and C; the 1 - 13 and 26-28 relate to the row numbers!

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    Hi & welcome to MrExcel.
    How about
    =INDEX($B$2:$B$13,MATCH(C27&"|"&$A27,$C$2:$C$13&"|"&$A$2:$A$13,0),1)

    Array entered
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    sorry - what are the characters for the second part - it look like:
    MATCH(C27&"|"&$A27 ....

    Is that correct or is something wrong in my display settings?

    Many thanks for your speedy reply

    Paul

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    No, those are correct, they just there for safety.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    Hi - Sorry to be a pest - this has returned #Value
    Can I send the actual spreadsheet to you? it is spread over 2 worksheets (ie table 1 is on one worksheet and table 2 on another - but I referenced these correctly)

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    Quote Originally Posted by PaulUR1 View Post
    Hi - Sorry to be a pest - this has returned #Value
    Can I send the actual spreadsheet to you? it is spread over 2 worksheets (ie table 1 is on one worksheet and table 2 on another - but I referenced these correctly)
    Sorry - Guess what I forgot ... CTRL+Shift+Enter .... it works ... I had no idea you could match multiple lines so easily - Thank you

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Return a result from an overall array but limit by another value

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •