CODEWITHSUNDEEP
excelif-statementgoogle-sheetsvlookupflatten
egflame
egflame

Reputation: 167

Searching many sheets for values and put the results in one sheet

I have a list of employee with dates for leaves they got each month, those dates are different of course, now i want to create one sheet for all leaves to showcase all leaves they taken through year, not depending on the row but depending on the name of the employee

I tried to use query but its not working well for me, any idea please?

Sheet link: https://docs.google.com/spreadsheets/d/1y-L5ICc-HWHYpkD_caxy2isTTHYVYCFBoCORQa5UJo4/edit#gid=833452873

Upvotes: 2

Views: 58

Answers (2)

player0
player0

Reputation: 1

either use:

=INDEX(TRIM(SPLIT(FLATTEN(QUERY(QUERY(TEXT(IFERROR(SPLIT(FLATTEN(SUBSTITUTE(
 {'1'!A2:A;'2'!A2:A;'3'!A2:A}, " ", CHAR(13))&"×"&{'1'!B2:F;'2'!B2:F;'3'!B2:F}), "×")), {"@", "dd/mm/yyyy"}), 
 "select max(Col2) where Col2 <> '30/12/1899' group by Col2 pivot Col1"),,9^9)), " ")))

enter image description here

or in B2:

=INDEX(IFERROR(VLOOKUP(A2:A, TRIM(SPLIT(FLATTEN(QUERY(QUERY(TEXT(IFERROR(SPLIT(FLATTEN(SUBSTITUTE(
 {'1'!A2:A;'2'!A2:A;'3'!A2:A}, " ", CHAR(13))&"×"&{'1'!B2:F;'2'!B2:F;'3'!B2:F}), "×")), {"@", "dd/mm/yyyy"}), 
 "select max(Col2) where Col2 <> '30/12/1899' group bY Col2 pivot Col1"),,9^9)), " ")), {2,3,4,5,6}, )))

enter image description here

Upvotes: 1

z..
z..

Reputation: 12823

I duplicated your DB tab and entered the following in A2:

={unique({'1'!A2:A;'2'!A2:A;'3'!A2:A}),index(trim(split(transpose(query(if((index(split(flatten(query({'1'!A2:A;'2'!A2:A;'3'!A2:A}&"❄️"&{'1'!B2:C;'2'!B2:C;'3'!B2:C},"where not Col1 matches '^❄️$'")),"❄️"),,1)<>transpose(unique({'1'!A2:A;'2'!A2:A;'3'!A2:A})))+(index(split(flatten(query({'1'!A2:A;'2'!A2:A;'3'!A2:A}&"❄️"&{'1'!B2:C;'2'!B2:C;'3'!B2:C},"where not Col1 matches '^❄️$'")),"❄️"),,2)=""),,text(index(split(flatten(query({'1'!A2:A;'2'!A2:A;'3'!A2:A}&"❄️"&{'1'!B2:C;'2'!B2:C;'3'!B2:C},"where not Col1 matches '^❄️$'")),"❄️"),,2),"m/d/yyyy")),,9^9))," ")))}

Your dates format are inconsistent though. Some are d/m/yyyy others are m/d/yyyy so you should fix that.

Upvotes: 1

Related Questions