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

  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jul 28 at 16:46

1 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)));"[\ ,]+$";);",\ ";",")})



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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.