How can I separate people per groups on Google Sheets-CodePudding

I hope you are doing great! I hope someone could help with this.

I have 2 groups of people "A" and "B"

both groups work on the same tasks

but I would like to get them with an "@" on a different sheet; separated into groups (group A and B) and both groups mixed (general)

Does anyone know how can I automatically separate them into groups A and B? I was wondering if I could use a match formula and an if formula, I tried to do it but had no success. The task can vary but no the group so the best would be to get formula or any way to automatically add thee people per group.

As an additional info I used this formula to get the general result: =IFNA(JOIN(", ";QUERY('Sheet 1'!A3:E12 ;"select A where B = TRUE";0));"")

This is the Google sheet I've been using to try to do this:

B17:

=ARRAYFORMULA(TRIM(FLATTEN(QUERY(TRANSPOSE(XLOOKUP(SPLIT(B3:B6; "@ "); 
 IF('Sheet 3'!C3:C12; 'Sheet 3'!A3:A12; ); "@"&'Sheet 3'!A3:A12;;; 1));;9^9))))

CodePudding user response：

Simply replace the opposing group in general category using

=ArrayFormula(IFERROR({ SPLIT('Sheet 3'!B1&" "&'Sheet 3'!B2&", " ; ",");
                TRANSPOSE({  'Sheet 1'!B2:E2;
                             BYCOL(IF(FILTER('Sheet 1'!B3:E; 'Sheet 3'!B3:B=TRUE)<>TRUE;;"@"&'Sheet 1'!A3:A); 
                             LAMBDA(v; TEXTJOIN(" "; 1;v)))}) };""))

=ArrayFormula(IFERROR({ SPLIT('Sheet 3'!B1&" "&'Sheet 3'!C2&", " ; ",");
                TRANSPOSE({  'Sheet 1'!B2:E2;
                             BYCOL(IF(FILTER('Sheet 1'!B3:E; 'Sheet 3'!C3:C=TRUE)<>TRUE;;"@"&'Sheet 1'!A3:A); 
                             LAMBDA(v; TEXTJOIN(" "; 1;v)))}) };""))

Functions used
ARRAYFORMULA - TRANSPOSE - BYCOL - IF - LAMBDA - TEXTJOIN