7 Replies Latest reply on Jul 11, 2019 1:31 PM by Joe Cooper

    Case When or If Then Logic Help

    Joe Cooper

      I am trying to figure how to write this - maybe I am just overthinking it. I have one worksheet with lets say Flag filtered to "Yes" - another worksheet that could either be yes or no -


      How do I combine both into singe worksheet leaving the list of people in first worksheet as yes - and 2nd worksheet selected people with either yes or no flag.


      Attached is a sample workbook. Any help would be great. Thank you.

        • 1. Re: Case When or If Then Logic Help
          Jim Dehner

          good morning

          see the attached

          when you use filters you are limiting the data available in the worksheet - by using sets you can categorize data into in and out of the set but all the data are available


          i create a set on the name field (you may want it on a different field but the concept is the same)  and set the General to Use all and the condition  as shown




          then on the T worksheet I put the set on the filter shelf and set to IN

          on the F worksheet I set the flag to In and Out

          you get this




          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Case When or If Then Logic Help
            Kelly McGrady

            I'm not sure what you are looking for but I replaced your case statement with IF [Name] ='Kora Cantu' and [Flag]  THEN 1 else 0 end and this works

            • 3. Re: Case When or If Then Logic Help
              Joe Cooper

              Hi Jim - I am following up until the part of adding Set as filter with T worksheet set to In and F set to In & Out ---- the next part (missing part) is how do I go about combining those 2 sheets into single worksheet. Ideally I am looking for an output where its combined into single worksheet.

              • 4. Re: Case When or If Then Logic Help
                John Sarantos

                Hi Joe,


                Building on what Jim already did: I think you're just looking for the placement of the pills, right?

                Here's one way (I added in totals and subtotals out of habit.)  I changed the alias for In and Out to Yes and No.


                Does that get you to where you need to be?


                • 5. Re: Case When or If Then Logic Help
                  Joe Cooper

                  Hi John- not quite


                  Below list lets just say is of Managers across Category with Flag Yes


                  2nd list - lets say are sub-support person under the manager with Flag of either Yes or No


                  What I am looking for is of a final list and can be placed at the end or by grouped by Category should have below result:

                  • 6. Re: Case When or If Then Logic Help
                    John Sarantos

                    Hi Joe,


                    The way you just described the issue, it sound like the names are not actually in the same category, and that you have one group 'Managers', and another group, 'Employees.'  If you have two different categories of people, then your calcs can be built on a conditional statement.


                    So, first you create a group for your names so that there are unique values called Manager and Employee.


                    The create a filter like:


                    [Name (group)]='Manager' and [Flag]


                    [Name (group)]='Employee'



                    Please let me know if this was helpful!  If not, I might still not understand the ask/question.


                    Thank you,


                    2 of 2 people found this helpful
                    • 7. Re: Case When or If Then Logic Help
                      Joe Cooper

                      WOAH! it worked John - I had them in same Category but by creating a 2 seperate Group and using the calc I am able to list them under the same sheet. Thanks a lot in providing the solution!