How to check multiple strings in 2 cells and generate another strings based on conditions?

Question

I'm trying to make a "Role Description generator" for my employee based on a Google Sheet in which i have multiple sheets :

1. 1st Sheet : "Positions and Responsabilities" In this sheet I Have all the descriptions of the roles. In the first column I have all the positions associate with each Role. The positions are strings of text separate by a ",".
2. 2nd Sheet : People and Positions In this sheet I have all the Employee with each position associated
3. 3rd Sheet : "People Responsabilites" In this Sheet I am trying to generate a list of roles based on a people. I've tried a workaround which seems be working based on queries. But the formula does not work every time (blank position...)

What I am trying to to do : In the Sheet "Positions and Responsabilities" I would like to fill in the last Column "People" with a list of people associated with each position, separated with "," So I can simplify the process of generating the last sheet...

Here is a link of an example : https://docs.google.com/spreadsheets/d/1-jolPlw72FpFdViRm6J2jDiiGDTLMuurmpo7hq06J6Y/edit?usp=sharing

Thank You for your Help ! Stephane

Answer 1

If you are looking for a list of "Positions" by Person", enter this formula on sheet="People and Positions", cell=H1

=arrayformula({"Name"\"Positions";A2:A\regexreplace(regexreplace(trim(flatten(query(transpose(B2:F)&",";"";9^9)));"[\ ,]+$";);",\ ";",")})

---

Sample

---

Other Notes

• the "blank" cells on sheet: "People and Positions" in the range B2:F5 are NOT blank. Use len on any blank cell and the value = 2. This would explain the commas appearing after the positions in Column G despite using "True" to "ignore empty cells".