Query Function with unique Filter 2 column when the criteria match in google sheet-CodePudding

i have Maintenance Schedule sheet for Fleet maintenance in that i need copy the data to another sheet whenever service status is "Live" and Equipment, Types of service to filter unique. need help to find out the formula Sample formula i tried whether is right or wrong,

Spreadsheet Link : https://docs.google.com/spreadsheets/d/1BUDC1euAHQXZ9rME7XEmV8YECrCH5DBmC7pM2lCzln0/edit?usp=sharing `

=ARRAYFORMULA(query('Master Schedule'!A1:O,"Select B,E,F,M,N,O where O='Live'",1),Query('Master Schedule'!A1:O,"Select * "&UNIQUE('Master Schedule'!B2:E)&"",1))

=Query(Query('Master Schedule'!A1:O,"Select B,E,F,M,N,O where O='Live'"),"Select '"&UNIQUE({'Master Schedule'!B:B, 'Master Schedule'!B:B&'Master Schedule'!E:E, 'Master Schedule'!E:E})&"'",1)

=Arrayformula(VLookup(Filter(Unique('Master Schedule'!B:B &'Master Schedule'!E:E);Unique('Master Schedule'!B2:E)<>"");Query('Master Schedule'!A:O; "Select B, E,F,M,N,O ";1); {1\2\3\4\5\6};0))

Master Schedule

Result Required

CodePudding user response：

Try this

=query(arrayformula(IFERROR(VLOOKUP(unique((Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M)),
query({arrayformula(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M),Data!B:B,Data!E:E,Data!F:F,Data!M:O},"select * where Col7='Live'"),
{2,3,4,5,6,7},0))),"select * where Col1 is not null")

or with semicolon notation

=query(arrayformula(IFERROR(VLOOKUP(unique((Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M));
query({arrayformula(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M)\Data!B:B\Data!E:E\Data!F:F\Data!M:O};"select * where Col7='Live'");
{2\3\4\5\6\7};0)));"select * where Col1 is not null")

explanation

build a unique reference as unique(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M)

build a new matrix with this reference and only 'live' rows as query({arrayformula(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M),Data!B:B,Data!E:E,Data!F:F,Data!M:O},"select * where Col7='Live'")

then apply vlookup