Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Using query in google sheet to fetch desired result based on some filters

I need to generate a report using query.

Data looks like as follows:

Model   SerialNumber    IdentityNumber    Status       EmployeeID
Lenovo  12abc           L-001             Issued        E-50
Apple   13abc           M-001             Issued        E-50
Lenovo  12abc           L-001             InStock       E-50
Lenovo  12abc           L-001             Issued        E-51
Lenovo  12abc           L-001             InStock       E-51
Lenovo  12abc           L-001             Issued        E-50

filter criteria's are employeeid, stockstatus and identitynumber. As mentioned in above data identity number L-001 issued to empolyeeid E-50 and after sometime employee(E-50) returned this asset so current assets with employee is M-001 2nd row only. I need to get this data using query in google sheet.

I got the result after crating an additional column where I updated that column with 0 and 1. if asset is in stock than 1 else 0. But in this solution I have to update the additional column for 1st row as well with flag 1 at time when employee return the asset. like below:

Model   SerialNumber    IdentityNumber    Status       EmployeeID    Flag
Lenovo  12abc           L-001             Issued        E-50         1
Apple   13abc           M-001             Issued        E-50         0
Lenovo  12abc           L-001             InStock       E-50         1

Is there any solution to get desired result without using additional column?

Solution

  • try:

    =ARRAYFORMULA(QUERY({A:E, {""; IF(A2:A="",,IF(ISEVEN(IFNA(
 VLOOKUP(A2:A&B2:B&C2:C, SORT({A2:A&B2:B&C2:C, IF(A2:A="",,
 COUNTIFS(A2:A&B2:B&C2:C, A2:A&B2:B&C2:C, 
 ROW(A2:A), "<="&ROW(A2:A)))}, 2, 0), 2, 0))), 1, 0))}}, 
 "select Col1,Col2,Col3,Col4,Col5 
  where Col5 = 'E-50' 
    and Col6 = 0", 1))

    enter image description here

    fix:

    =ARRAYFORMULA({A1:E1; QUERY(ARRAY_CONSTRAIN(SORTN(SORT({A2:F, 
 IF(ISEVEN(VLOOKUP(A2:A&B2:B&C2:C&E2:E, SORTN(SORT({A2:A&B2:B&C2:C&E2:E, IF(A2:A="",, 
 COUNTIFS(A2:A&B2:B&C2:C&E2:E, A2:A&B2:B&C2:C&E2:E,  ROW(A2:A), "<="&ROW(A2:A)))}, 2, 0), 
 99^99, 2, 1, 0), 2, 0)), 1, 0), VLOOKUP(A2:A&B2:B&C2:C&E2:E, 
 SORTN(SORT({A2:A&B2:B&C2:C&E2:E, IF(A2:A="",, 
 COUNTIFS(A2:A&B2:B&C2:C&E2:E, A2:A&B2:B&C2:C&E2:E,  ROW(A2:A), "<="&ROW(A2:A)))}, 2, 0), 
 99^99, 2, 1, 0), {2, 1}, 0), ROW(A2:A)}, 10, 0, 8, 0), 99^99, 2, 9, 0), 99^99, 7), 
 "select Col1,Col2,Col3,Col4,Col5 where Col5 = 'E-50' and Col7 = 0", 0)})

    0