+ Reply to Thread
Results 1 to 9 of 9

How can I use Vlookup to check multiple task fulfilment?

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    Henfield
    MS-Off Ver
    Excel 2013
    Posts
    4

    How can I use Vlookup to check multiple task fulfilment?

    Hello,

    I'm a new boy. I assume that there will be some sort of initiation/hazing so, bring it on!

    Right here's the problem, for which I would be grateful for a solution suitable for a remedial class member such as myself:

    I have a number of separate manufacturing tasks which require several parts to complete. I need to establish whether there are sufficient parts to complete all of the sub-tasks and therefore the whole task. The first bit is easy, but what I am struggling with is counting the number of sub-tasks and comparing that to the number of the sub-tasks which can be completed e.g. there are five sub-tasks to complete the task, if all the sub-tasks are fulfilled, then return a value "yes" if not, return a value "no."

    Specimen Spreadsheet publisher.gif

    I look forward to getting to being humbled.
    Last edited by Baloo; 06-10-2013 at 07:30 AM. Reason: Schoolboy error/bollocking by moderator

  2. #2
    Registered User
    Join Date
    06-10-2013
    Location
    Henfield
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: How can I use Vlookup to check multiple task fulfilment?

    I should have added that the attached spreadsheet reflects what I would like to happen in the last column i.e. if all sub-tasks are do-able, then the whole task is do-able. So, if there are five subtasks and they are all fulfilled then the all of the boxes in the last column associated with that task will say "yes."

  3. #3
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: How can I use Vlookup to check multiple task fulfilment?

    can you upload the workbook itself? (follow the same process used to upload that gif file, but select a shareable version of the excel file).

    One way to do this would be to count the number of "yes" entries and compare the to total number of entries with 2 sumproduct functions.

    Example:

    =CHOOSE((SUMPRODUCT(($A$5:$A$20=A5)*($A$5:$A$20=A5))=SUMPRODUCT(($A$5:$A$20=A5)*($E$5:$E$20="yes")))*1+1,"no","yes")

    Try that adjusted for your ranges
    Last edited by GeneralDisarray; 06-10-2013 at 10:28 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  4. #4
    Registered User
    Join Date
    06-10-2013
    Location
    Henfield
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: How can I use Vlookup to check multiple task fulfilment?

    Certainly can. Thanks for your help.

    Specimen Spreadsheet.xlsx

  5. #5
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: How can I use Vlookup to check multiple task fulfilment?

    Yep. That works (note, you had the wrong label for the Task2 B column (you didn't have Task2/...)


    Anyway, is the attached file what you are after? I changed some numbers in the task 2 area to test if the formula would evaluate properly
    Attached Files Attached Files

  6. #6
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: How can I use Vlookup to check multiple task fulfilment?

    @Baloo, you will suffer minimal hazing if you review the Forum Rules you already agreed to follow when you registered. Hehe. Link is above in the menu bar.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  7. #7
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: How can I use Vlookup to check multiple task fulfilment?

    @ General - I've never heard of the CHOOSE() function before. It looks interesting and I'll have to play around with it.

    Another way would be to use =IF(SUMPRODUCT(--($A:$A=A5)*($E:$E="Yes"))=SUMPRODUCT(--($A:$A=A5)),"Yes","No"). I'm just providing options.
    Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.

    Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
    If you're happy with an answer given, please click the * under the person's name to boost their reputation.

  8. #8
    Registered User
    Join Date
    06-10-2013
    Location
    Henfield
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: How can I use Vlookup to check multiple task fulfilment?

    GeneralDisarray,

    Thank you very much for your help. It was bang on. And thanks to everyone else for their input.

  9. #9
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: How can I use Vlookup to check multiple task fulfilment?

    Yeah, the CHOOSE function is handy to know. Usually, you can use it anytime you feel the need for an IF() statement. It's usually just easier to read through.

    Granted, in a case like this the IF statement is just fine - but when you start nesting the IF statements using CHOOSE really starts to simplify things. Like anything in excel, more than one way to skin a cat but after you get used to CHOOSE in tough cases you'll find yourself using it for simple ones also.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1