Formula to identify status of requisition when there are multiple possible scenerios
Results 1 to 6 of 6

Thread: Formula to identify status of requisition when there are multiple possible scenerios

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Formula to identify status of requisition when there are multiple possible scenerios

    I am hoping to find assistance with a formula that will help me identify which requisitions have been fully or partially awarded.

    Each requisition may have multiple lines and none, some, or all of the lines might be awarded. In the example below Requisition 111111 has three line items, but only line three has been awarded so the requisition is "partially awarded." Requisition 444444 has three line items and no lines have been awarded so it is "not awarded."

    The difficulty I am having is that I need each instance of the requisition to reflect this information. For instance with requisition 111111, line items 1, 2 and 3 must all state the same status (in this case partially awarded) even though only one line has an award number associated with it.

    As a note, there can be more than one award number per requisition as shown in example 66666 below.

    I am looking for a formula to complete the "Requisition Status" column in the image below.

    Thank you.
    REQUISTION LINE AWARD NUMBER REQUISITION STATUS
    111111 1 - PARTIAL AWARD
    111111 2 - PARTIAL AWARD
    111111 3 123111 PARTIAL AWARD
    444444 1 - NOT AWARDED
    444444 2 - NOT AWARDED
    444444 3 - NOT AWARDED
    666666 1 223111 AWARDED
    666666 2 223111 AWARDED
    666666 3 333222 AWARDED

  2. #2
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula to identify status of requisition when there are multiple possible scenerios

    Try this:

    =IF(COUNTIF($A$2:$A$10,A2)=COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,">0"),"",
    IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,">0"),"PARTIAL ","NOT "))&"AWARDED"


    Markmzz

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to identify status of requisition when there are multiple possible scenerios

    Markmzz,

    I failed to mention that the award numbers contain text which cause your formula (which otherwise does exactly what I need) to not work. A sample award number is 123C7818F0087. If I substitute the award number with a number your formula works, otherwise it doesn't recognize the field. I apologize, I should have mentioned this in my original post. Any ideas? Thanks!

  4. #4
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula to identify status of requisition when there are multiple possible scenerios

    Quote Originally Posted by u4carson View Post
    Markmzz,
    I failed to mention that the award numbers contain text which cause your formula (which otherwise does exactly what I need) to not work. A sample award number is 123C7818F0087. If I substitute the award number with a number your formula works, otherwise it doesn't recognize the field. I apologize, I should have mentioned this in my original post. Any ideas? Thanks!
    Try this is this case:

    =IF(COUNTIF($A$2:$A$10,A2)=COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"<>-"),"",
    IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"<>-"),"PARTIAL ","NOT "))&"AWARDED"


    Markmzz

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to identify status of requisition when there are multiple possible scenerios

    Quote Originally Posted by markmzz View Post
    Try this is this case:

    =IF(COUNTIF($A$2:$A$10,A2)=COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"<>-"),"",
    IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"<>-"),"PARTIAL ","NOT "))&"AWARDED"


    Markmzz
    MArkmzz,

    This worked! Thank you! I learned something today!!!

  6. #6
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula to identify status of requisition when there are multiple possible scenerios

    Quote Originally Posted by u4carson View Post
    MArkmzz,

    This worked! Thank you! I learned something today!!!
    You are welcome and thank you for the feedback.

    Markmzz

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
  •