Help with a Multi-Row Formula

Question

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.

JobHistory Example 2024-02-28T18_43_28.983Z.xlsx

rzdodson · Answer

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

FrederikE · Answer

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).

New Workflow7.yxzp

rzdodson · Answer

@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.)?