Filter data (Not In) based on condition within two data sets in Google sheet

Question

Have employee data set with status active and relieved. And the second data set has the time they have reported to the office. Trying to achieve the names of employee who are absent for the day using one formula. I have attached the images for easy understanding. Please find the sheet wherein I have created sample data and formula which I have used. Trying to achieve it using one array formula or query

https://docs.google.com/spreadsheets/d/1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM/edit?usp=sharing

Kanted · Accepted Answer · 2019-11-27 10:12:20Z

Have achieved it using Filter and Match

{"Absent For the Day";FILTER((query(importrange("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM","empdata!A2:B30"),"select Col1 where Col2='Active'")),ISERROR(MATCH((query(importrange("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM","empdata!A2:B30"),"select Col1 where Col2='Active'")),A2:A20,0)))}

player0 · Accepted Answer · 2019-11-27 19:04:14Z

0

try:

=ARRAYFORMULA({"Absent For the Day", ""; SPLIT(FILTER(empdata!A2:A, empdata!B2:B="active", NOT(REGEXMATCH(empdata!A2:A, TEXTJOIN("|", 1, A2:A))))&"♦Absent", "♦")})

answered Nov 27, 2019 at 19:04

player0

131k14 gold badges91 silver badges149 bronze badges

3 Comments

Kanted Over a year ago

as the emp data is in another worksheet in actual, used import range in place of empdata!A2:A, empdata!B2:B="active" replaced it with below. But it is not working

(query(importrange("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM","empdata!A2:B30"),"select Col1 where Col2='Active'") ,(query(importrange("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM","empdata!A2:B30"),"select Col1 where Col2='Active'")

player0 Over a year ago

@Kanted I see. in that case it would need to be:

=ARRAYFORMULA({"Absent For the Day", ""; SPLIT(FILTER(QUERY(IMPORTRANGE("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM", "empdata!A2:B"), "select Col1 where lower(Col2) = 'active'", 0), QUERY(IMPORTRANGE("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM", "empdata!A2:B"), "select Col2 where lower(Col2) = 'active'", 0)="active", NOT(REGEXMATCH(QUERY(IMPORTRANGE("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM", "empdata!A2:B"), "select Col1 where lower(Col2) = 'active'", 0), TEXTJOIN("|", 1, A2:A))))&"♦Absent", "♦")})

player0 Over a year ago

i.sstatic.net/Lsw49.png or try the shorter answer bellow

player0 · Accepted Answer · 2019-11-28 08:03:26Z

try:

=ARRAYFORMULA({"Absent For the Day", ""; SPLIT(QUERY(IMPORTRANGE("1Dj7agceCBS_aCm2GVm4EQHqS8_wQYifoKLMcA0fv4fM", "empdata!A2:B"), "select Col1 where lower(Col2) = 'active' and not Col1 matches '"&TEXTJOIN("|", 1, A2:A)&"'", 0)&"♦Absent", "♦")})

Collectives™ on Stack Overflow

Filter data (Not In) based on condition within two data sets in Google sheet

3 Answers 3

Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Related