Copy and Filter out selected fields from 2 tabs in the Same Gsheet to another Tab with common catego-CodePudding

Copy and Filter out selected fields from 2 tabs in the Same Gsheet to another Tab with Fraud = 'Yes'

I have 2 sets Tabs in the Same Gsheet with different information. I would like to copy them into another tab with Fraud ="Yes". I have an example with formulas in Combine Example from Stall A Example and Stall B Example with some help previously. When I tried to replicated into my actual data (Combine NSU and ACH ) set I can't do it.

Can someone please help and guide on this.

Output:

Get all data of sheet NSU and ASH, and re-arrange their orders by QUERY.
Group them up with another QUERY to filter the data you want such as Col4 = 'Yes'.
All the formating and sort can also be done with query.

=ArrayFormula(
 LAMBDA(NSU,ACH,
  QUERY({NSU;ACH},
   " WHERE Col1 IS NOT NULL "
  &" AND Col4 = 'Yes' "
  &" ORDER BY Col1 ASC"
  &" LABEL Col1 'Date',Col2 'Type',Col3 'Amount',Col4 'Fraud',Col5 'ID',Col6 'ERP' "
  &" FORMAT Col1 'yyyy-mm-dd' "
  )
 )(
  LAMBDA(COLS,
   QUERY({NSU!$A:$P},
    " SELECT "&JOIN(",","Col"&COLS)
   &" LABEL "&JOIN(",","Col"&COLS&" '"&REPT(" ",COLS)&"'"),1)
  )({1,2,8,13,15,16}),
  LAMBDA(COLS,
   QUERY({ACH!$A:$N},
    " SELECT "&JOIN(",","Col"&COLS)
   &" LABEL "&JOIN(",","Col"&COLS&" '"&REPT(" ",COLS)&"'"),1)
  )({1,2,14,12,6,8})
 )
)

CodePudding user response：

@Ping enter image description here

Example if there some fields when require manual input like Colmun C, G, I J

https://docs.google.com/spreadsheets/d/1N35wUB-a7hDHFTzdhajlaCTJf_Ce34Ql3Miwq51JCqY/edit?usp=sharing

Can I still use this formula? seems like there is a seperator at the query does that makes a difference?