Google Sheet Formula with complex query and multiple criteria-CodePudding

I need help to fix a query formula. I am trying to pull specific columns with a criteria. Tried my very best to come up with a formula but I keep on having errors.

Here are the criteria needed for each column:
data headers = txn headers
Contact* = full_name
email* = email but will only shows a unique value
Product name* = relevant_item_name where it only contains "Not Your Average Membership" or "The Not So Average Membership"
Billing amt = total where the event is charge
Status = event either contains charge or rebill based on last order_date
Last billed* = order_date but only the last date of that email* (especially if it has multiple order dates)
Cycle = relevant_item_pricing_option when it contains monthly should show "monthly" or One-time payment should show "annual"
Charges* = the order_id usually shows orderidnumber-chargenumber so only the -x

This is the sample file:

CodePudding user response：

use:

=SORTN(QUERY(txn!A2:AR, 
 "select D,S,AO,AB,AM,X 
  where AO = 'Not Your Average Membership' 
    and AM matches 'charge|rebill'
  order by X desc", ), 9^9, 2, 2, 1)

stuff like S = UNIQUE(S) and X = MAX(X) are not supported in QUERY

|      - or logic
9^9    - return all rows
2      - group by unique mode of sortn
2      - unique for 2nd column outputted by query
1      - in ascending order

CodePudding user response：

added formula to your