0

I have a Google sheet with about 10000 rows . It's has invoices which are marked as paid and open. Many of them are marked as paid in one row and open on another row (same invoice number). I want to extract only the paid invoices , which also have an open entry and the open invocies which have no paid entry. Also select where paid only is there.

1
0

There are probably several ways to construct an answer to your question. Consider the following as one approach.

Paid invoices with an Open entry

=query({query({A2:C},"select Col1,Col2, Col3 where Col1 is not null and Col3 = 'Paid' order by Col1")},"select Col1, Col2, Col3 where Col1="&JOIN(" or Col1=",query({A3:C},"select Col1 where Col1 is not null and Col3 = 'Open' order by Col1"))&""&" label Col3 'Paid and Open' ")

Open invoices with no Paid entry

=query({query({A2:C},"select Col1,Col2, Col3 where Col1 is not null and Col3 = 'Open' order by Col1")},"select Col1, Col2, Col3 where Col1<>"&JOIN(" and Col1<>",query({A3:C},"select Col1 where Col1 is not null and Col3 = 'Paid' order by Col1"))&""&"")

Paid invoices with no Open entry

=query({query({A2:C},"select Col1,Col2, Col3 where Col1 is not null and Col3 = 'Paid' order by Col1")},"select Col1, Col2, Col3 where Col1<>"&JOIN(" and Col1<>",query({A3:C},"select Col1 where Col1 is not null and Col3 = 'Open' order by Col1"))&""&"")

Comments:

  • each answer replies on a nested queries
  • the initial query includes where clause for the default Status value
  • the where clause in the second query relies on a JOIN function to include or exclude invoices with a given Status
  • the JOIN function uses a third query to provide the array results for the required Status.
  • the JOIN function also includes AND/OR that operates on the where clause

snapshot

Credit: @benfromredant Using query to find the entries in one column which are not in the other

1
  • WOW!!! Can you pl share the formula which has all 3 in a single formula. – Karan Jul 14 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.