CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Keto
Keto

Reputation: 31

query different rows

I have a sheet of creditors and there is a checkbox on each entry next to total price column. When I receive the payment I check the checkbox. Now I am trying to query all the unpaid creditors to another sheet but I don't know how to query creditor name when the checkbox is on another row. I can query same row entries. here is how it looks like

Name Items Payment Received (Checkbox)
Jack chips TRUE
Joe beer FALSE
Katrine chips
fruits FALSE
Bob coke
vegies TRUE
Adrine chips
beer FALSE

I tried QUERY. But didn't help. It just shows same row items. What happens here is first column customers buy the items on second column and if customer pays then third column becomes TRUE and if it is a credit, then it is FALSE. And if customer buys more than 1 item, then the TRUE value will be next to the last item of second column. So what I am trying to achieve is, I am trying to query or whatever somehow filter those customers who haven't paid their payment. –

Upvotes: 0

Views: 159

Answers (2)

Aresvik
Aresvik

Reputation: 4630

I'm not completely clear what you need, but if it's the value in col1 to repeat down, with a query pulling out data where col 3 is false, then try this:

=index(query({if(B:B<>"",vlookup(row(A:A),query({row(A:A),A:A},"where Col2 is not null",0),2,1),),B:C},"select Col1,Col2 where Col3 = false",1))

You can alter the query to only select Col1 if you only want the Name.

enter image description here

Upvotes: 0

TheMaster
TheMaster

Reputation: 50759

It would be better if the table is formatted in . But here, for each name( using MAP),we get the corresponding last checkbox per group using XMATCH's wildcard match and OFFSET. Once, we got the checkbox, we use FILTER to filter out what's not needed.

=FILTER(A2:A9,
  MAP(C2:C9,A2:A9,
    LAMBDA(checkbox,name,
      IF(OR(checkbox,name=""),
        ,
        NOT(IFNA(
          OFFSET(checkbox,
            XMATCH("*",OFFSET(name,1,0,5,1),2)-1,0
          ),
        C9)
        )
      )
    )
  )
)

  • Since, XMATCH can't find the last checkbox, as there's no corresponding value, we reference the last checkbox at C9 manually.

  • For each name, OFFSET(...,5) gets 5 rows to get the last checkbox per group. If there are a maximum of 10 items per group, you have to change 5 to 10.

Upvotes: 0

Related Questions