Search code examples
if-statementgoogle-sheetsfiltergoogle-sheets-formulaarray-formulas

Create a list of missing for completness with contact details in Google Sheets

I'm tryying to create a tracking sheet with details depending on the percentage from a table this is the table (Link to the full sheet in google sheets):

Institution name    Management  Radiology   Nuclear number  Pathology   Laboratorie
Hospital 1          Zero        Zero        Zero            Zero        Zero
Hospital 2          Not Valid   Zero        Zero            Zero        Zero

So for each institute there is a person in charge for a task and the zero means they havent do anything and valid means that they are not in the range of 80% of completness to said that they have finished. So I have a contact list with the people assigned to each area in the institute like this:

Hospital 2  Person 3    person.3@ gmail.com Radiology   
Hospital 2  Person 4    person.4@ gmail.com Management  
Hospital 2  Person 5    person.5@ gmail.com All 
Hospital 2  Person 6    person.6@ gmail.com Surgery

So I want to display a list with the people who hasn't complete their task to send them an email and I have this formula that is close to what I want but the problem is that this formula is only for one area:

=ARRAYFORMULA(FILTER(IMPORTDETAILS!A2:D, IFNA(VLOOKUP(IMPORTDETAILS!A2:A&IMPORTDETAILS!D2:D, 
 SPLIT(FLATTEN(PercentageofCompletness!A4:A&PercentageofCompletness!C3&"×"&PercentageofCompletness!C4:C), "×"), 2, 0))<=0.8, 
 IFNA(VLOOKUP(IMPORTDETAILS!A2:A&IMPORTDETAILS!D2:D, 
 SPLIT(FLATTEN(PercentageofCompletness!A4:A&PercentageofCompletness!C3&"×"&PercentageofCompletness!C4:C), "×"), 2, 0))<>""))

Help with this please

Solution

  • You can try with this formula:

    =LAMBDA(filt,{filt,INDEX(SPLIT(BYROW(filt,LAMBDA(each,JOIN("|",FLATTEN(IFNA(FILTER('EMAIL DETAILS'!B:C,REGEXMATCH('EMAIL DETAILS'!A:A,INDEX(each,,1)),REGEXMATCH('EMAIL DETAILS'!D:D,INDEX(each,,2))), FILTER('EMAIL DETAILS'!B:C,REGEXMATCH('EMAIL DETAILS'!A:A,INDEX(each,,1)),REGEXMATCH('EMAIL DETAILS'!D:D,"All")))) ))),"|"))}) (LAMBDA(data,FILTER(data,REGEXMATCH(INDEX(data,,3),"Zero|Not valid"))) (INDEX(SPLIT(FLATTEN(MAP('Percentage of completness'!B2:K13,LAMBDA(a,JOIN("|",INDEX('Percentage of completness'!A1:A13,ROW(a)),INDEX('Percentage of completness'!A1:K1,COLUMN(a)),a)))),"|"))))

    With MAP I check every value and match with hospital and section, flatten and split to have the three columns. Filter by "Zero" and "Not valid", and finally look into the contact sheet. When there is a match with the section, I grab that mail, when there is not, I look for "All" option in that hospital. If you want to have always "All" option with the specific mail of the section, try this variation:

    =LAMBDA(filt,{filt,INDEX(SPLIT(BYROW(filt,LAMBDA(each,JOIN("|",FLATTEN(FILTER('EMAIL DETAILS'!B:C,REGEXMATCH('EMAIL DETAILS'!A:A,INDEX(each,,1)),REGEXMATCH('EMAIL DETAILS'!D:D,INDEX(each,,2)&"|All")),) ))),"|"))}) (LAMBDA(data,FILTER(data,REGEXMATCH(INDEX(data,,3),"Zero|Not valid"))) (INDEX(SPLIT(FLATTEN(MAP('Percentage of completness'!B2:K13,LAMBDA(a,JOIN("|",INDEX('Percentage of completness'!A1:A13,ROW(a)),INDEX('Percentage of completness'!A1:K1,COLUMN(a)),a)))),"|"))))

    enter image description here

    UPDATE

    You can try with this formula. It checked the columns, and added a different kind of input. listing names in different lines of same cell in one column, and corresponding mails together also separated by lines in the next column. It may be useful if you just want to copy all mails together:

    =LAMBDA(filt,{filt,INDEX(SPLIT(BYROW(filt,LAMBDA(each,JOIN("|",FLATTEN(BYCOL(IFNA(FILTER(IMPORTDETAILS!B:C,IMPORTDETAILS!A:A=INDEX(each,,2),REGEXMATCH(IMPORTDETAILS!D:D,INDEX(each,,3))), FILTER(IMPORTDETAILS!B:C,IMPORTDETAILS!A:A=INDEX(each,,2),REGEXMATCH(IMPORTDETAILS!D:D,"All"))),LAMBDA(m,JOIN(CHAR(10),m)) ))))),"|"))}) (LAMBDA(data,FILTER(data,REGEXMATCH(INDEX(data,,4),"Zero|Not valid"))) (INDEX(SPLIT(FLATTEN(MAP(Track!C2:L13,LAMBDA(a,JOIN("|",INDEX(Track!A1:A13,ROW(a)),INDEX(Track!B1:B13,ROW(a)),INDEX(Track!A1:L1,COLUMN(a)),a)))),"|"))))