Help with a Multi-Row Formula

[mallieteal21]

Hi there - I am working on a career progression analysis and hoping someone can help me with a multi-row formula using the attached data. I essentially want to write a formula that looks at each row in the attached document and tags each row accordingly. Specifically I'm trying to group the data into two buckets, the first are people who have a 0-3 in their job code (column D) at any point. The other group is anyone with rows that only contains 4-8 in the job code. Basically looking for some way to  identify and separate the people with rows for P0-P3 job codes, from the people who only have rows containing P4-P8 job codes. 

[rzdodson]

@mallieteal21 do you have a final output you have previously produced for this project that we can use as reference? Having some trouble understanding the grouping logic you are trying to arrive at/replicate.

Also, you have 820 other job codes that are in the data set. Any preference on what happens with these (e.g. B7162VP, PM2010, N5015 etc.)?

[FrederikE]

Hi @mallieteal21, 

This workflow tags all strings that contain P0-P3 and P4-P8 with "group" 1 or 2 - they can of course be both. All others are Nulls (can be filtered or kept).

 

[rzdodson]

Here is one approach.

If [Job Code] IN('P0','P1','P2','P3') then "Group 1"
elseif [Job Code] IN('P4','P5','P6','P7','P8') then "Group 2"
else null()
endif