How can I separate people per groups on Google Sheets

Elmar Onassis 0
Viewed 68

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

groups of people

both groups work on the same tasks

tasks assigned to people

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)

People per group and general result

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(CONCATENATE("@"; TEXTJOIN(" @"; TRUE ;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:

https://docs.google.com/spreadsheets/d/14fdQJcIuqFTfo8upNOtALk0vhQYjSM0d8nYRML4amuM/edit?usp=sharing

google-sheets-formula conditional-statements if-statement google-sheets
3 Answers

B10:

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

enter image description here

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

enter image description here

player00

Paste this formulas
in Sheet2 cells A3 , A10 , A17 and make sure to apply it in your shared spreadsheet and with the same locale in your case spain

=ArrayFormula(IFERROR(QUERY(TRANSPOSE(
                              { 'Sheet 1'!B2:E2;
                                BYCOL(IF('Sheet 1'!B3:E<>TRUE;;
                                      LAMBDA(r; IF(r="";;"@"&r))('Sheet 1'!A3:A));
                                LAMBDA(j; TEXTJOIN(" ";1;j))) }); 
              " Select * Where Col2 <> '' "); ""))

enter image description here

=ArrayFormula(IFERROR(QUERY(TRANSPOSE(
                              { 'Sheet 1'!B2:E2;
                                BYCOL(IF(IF('Sheet 3'!B3:B<> TRUE; "" ;'Sheet 1'!B3:E )<>TRUE;;
                                      LAMBDA(r; IF(r="";;"@"&r))('Sheet 1'!A3:A));
                                LAMBDA(j; TEXTJOIN(" ";1;j))) }); 
              " Select * Where Col2 <> '' ");""))

enter image description here

=ArrayFormula(IFERROR(QUERY(TRANSPOSE(
                              { 'Sheet 1'!B2:E2;
                                BYCOL(IF(IF('Sheet 3'!C3:C<> TRUE; "" ;'Sheet 1'!B3:E )<>TRUE;;
                                      LAMBDA(r; IF(r="";;"@"&r))('Sheet 1'!A3:A));
                                LAMBDA(j; TEXTJOIN(" ";1;j))) }); 
              " Select * Where Col2 <> '' ");""))

enter image description here

Demo

enter image description here

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

Osm0

Simply replace the opposing group in general category using regex:

For group A, replace B:

=ARRAYFORMULA(REGEXREPLACE(B3:B6,"@"&JOIN("( |$)|@",FILTER(Sheet3!A3:A12,Sheet3!C3:C12)),))

where B3:B6 is the General category's values column and Sheet3 contains Groups.

This creates a regex like

@P1( |$)|@P2( |$)|@P5( |$)|@P6( |$)|@P8( |$)|@P10

Which is @ followed by any group P1 and followed by a space or end of string( |$). For this to work properly, avoid regex meta characters in People names.

TheMaster0
Related