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

# Case When or If Then Logic Help

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

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

Jim

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

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

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

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

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

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]

OR

[Name (group)]='Employee'

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

Thank you,

John

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

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!