0

Having this example sheet (just sample data)

       Column-A      Column-B                    Column-C
  1    claudia       claudia@gmail.com           SOLD
  2    claudia       claudia@gmail.com           CONTACTED
  3    natalia       natalia@gmail.com           CONTACTED

How to use FILTER to filter row 3, that is to say: rows where colum-c is CONTACTED but excluding duplicated rows where one of the duplicated instances has "SOLD" within column-c.

Definition of "duplicate": same email (column-b)

sebas
  • 722
  • 1
  • 6
  • 21

1 Answers1

2

try like this:

=FILTER(A1:C, C1:C="contacted", 
 NOT(REGEXMATCH(B1:B, TEXTJOIN("|", 1, FILTER(B1:B, C1:C="sold")))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • This formula is causing a massive delay in my worksheet. How can you turn it into a QUERY function rathen than FILTER function – sebas Oct 16 '19 at 05:22
  • try: `=QUERY(A:C, "where lower(C) ='contacted' and not lower(B) matches '"&LOWER(TEXTJOIN("|", 1, QUERY(B:C, "where lower(C) = 'sold'", 0)))&"'", 0)` – player0 Oct 16 '19 at 09:38
  • how would you turn into QUERY this ? =FILTER({FORMULARIO!C13:C}, FORMULARIO!J13:J<>"PAGÓ", FORMULARIO!B13:B=10, FORMULARIO!C13:C<>"", FORMULARIO!A13:A>TODAY()-5, NOT(REGEXMATCH(FORMULARIO!Q13:Q, TEXTJOIN("|", 1, FILTER(FORMULARIO!Q13:Q, FORMULARIO!J13:J="PAGÓ")))))) – sebas Oct 16 '19 at 16:46
  • Thanks, Amazing! – sebas Oct 16 '19 at 17:12
  • I have adapted your function to do this: is there any way to filter out duplicates before showing result? The criteria i want is to show the last intance (newest) (date is in column A) =QUERY(FORMULARIO!A13:Q, "select G,E,K,M,N,O,Q where not upper(J) = 'PAGÓ' and Q contains '@' and A > date '"&TEXT(TODAY()-5, "yyyy-mm-dd")&"' and not Q matches '"&TEXTJOIN("|", 1, QUERY(FORMULARIO!J13:Q, "select Q where upper(J) = 'PAGÓ'", 0))&"'", 0) – sebas Oct 16 '19 at 21:07