How to merge 2 dfferents tabs in Gsheet with a common condition as "Yes"-CodePudding

https://docs.google.com/spreadsheets/d/1N35wUB-a7hDHFTzdhajlaCTJf_Ce34Ql3Miwq51JCqY/edit?usp=sharing Please take a look at the Gsheet.

1.) There are 2 different tabs with different fields.

2.) I need selected fields as shown in Combine Tab with a Common condition Damaged = Yes

3.) After that I have tried to sort with dates and the entire column is gone. not sure why. maybe is due to the formula I used.

This is a simpler version of my actual project. I cant share the actual one due to sensitive data. This is the formula I used for my actual project it works but when i sort by date... everything is gone.

It works but when sort by date everything is gone.

={filter(NSU!A:A,NSU!M:M="Yes");filter(ACH!A:A,ACH!L:L="Yes")}

CodePudding user response：

sample

Instead of QUERY, you may also consider LAMBDA, FILTER and INDEX.

=SORT(
 LAMBDA(DATA,
  LAMBDA(ITEM,DAMAGED,DATE,ID,
   FILTER({TEXT(DATE,"dd/mm/yyyy"),ITEM,DAMAGED,ID},DAMAGED="YES")
  )(INDEX(DATA,,1),INDEX(DATA,,2),INDEX(DATA,,4),INDEX(DATA,,6))
 )({'Stall A'!A2:F6;'Stall B'!A2:F6})
,1,TRUE)

the 1st LAMBDA combine 2 data range into one in this format {array1;array2} and named it 'DATA'.

=LAMBDA(DATA,"folmula")({'Stall A'!A2:F6;'Stall B'!A2:F6})

the 2nd LAMBDA uses INDEX to extract required COLUMNS from 'DATA', in this case, Col1, Col2, Col4 and Col6, and named them as 'ITEM','DAMAGED','DATA','ID'.

  LAMBDA(ITEM,DAMAGED,DATE,ID,
   "formula"
  )(INDEX(DATA,,1),INDEX(DATA,,2),INDEX(DATA,,4),INDEX(DATA,,6))

inside the 2nd LAMBDA, setup a dataArray [DATE,ITEM,DAMAGED,ID] with {}, feed it to the FILTER function and sort out the data where DAMAGED="YES", and format the DATE column with TEXT function as date format.

   FILTER({TEXT(DATE,"dd/mm/yyyy"),ITEM,DAMAGED,ID},DAMAGED="YES")

finally, warp the whole thing up with SORT, sort according to COLUMN 1, which is DATE column in our case, in ascending order.

SORT("the_whole_thing",1,TRUE)

CodePudding user response：

Use query() with an { array expression } and an order by clause, like this:

=query( { 'Stall A'!A1:F; 'Stall B'!A2:F }, "select Col4, Col1, Col2, Col6 where Col2 = 'Yes' order by Col4", 1 )