Thread: filter function with criteria

Dear Excel Experts

Name | Mod No. | Marks | Date
John 1 0 200408
John 3 0 200408
John 5 0 200408
Peter 1 0 200408
Peter 3 0 200408
Peter 5 43 200507
Mary 1 82 200509
Mary 2 76 200509
Mary 2 80 200609
Sally 1 0 200408
Sally 3 0 200408
Luke 1 54 200504
Luke 2 72 200504
Luke 1 80 200509
Luke 3 64 200509
Luke 3 50 200604
Sam 1 0 200408
Sam 3 0 200408
Alice 1 0 200408
Alice 3 0 200408
Nina 1 0 200509
Nina 2 0 200509
Alex 1 94 200409
Alex 2 88 200409
Alex 4 55 200412
Alex 4 72 200507
Alex 3 80 200604
Henry 1 90 200604
Henry 3 76 200609
Jane 1 66 200609
Jane 3 74 200609
Ronald 1 0 200408
Ronald 3 0 200408
Carla 1 78 200504
Carla 2 94 200504
Carla 1 90 200509
Carla 4 64 200512
Carol 1 0 200408
Carol 3 0 200408
Angela 1 66 200604
Angela 2 68 200604
Angela 1 52 200609
Angela 2 70 200609

How can i highlight or filter records with criteria only those with Module 1 and Module 2 with the same date. For example, based on the table above, my expected outcome is as follow:


Mary 1 82 200509
Mary 2 76 200509
Luke 1 54 200504
Luke 2 72 200504
Nina 1 0 200509
Nina 2 0 200509

**Condition: must have Mod. No. 1 AND 2 with the same date

fantastic....thank you so much NBVC.

Originally Posted by NBVC

You'll probably need a helper column.

Assuming your data is in columns A:D with headers in Row 1...

In E2, enter

Code:

=IF(B2=1,IF(SUMPRODUCT(--(B3:$B$44=2),--(D3:$D$44=D2),--(A3:$A$44=A2))>0,"Duplicate",""),IF(B2=2,IF(SUMPRODUCT(--(B3:$B$44=1),--(D3:$D$44=D2),--(A3:$A$44=A2))>0,"Duplicate",IF(B2=1,IF(SUMPRODUCT(--($B$1:$B1=2),--($D$1:$D1=D2),--($A$1:$A1=A2))>0,"Duplicate",""),IF(B2=2,IF(SUMPRODUCT(--($B$1:$B1=1),--($D$1:$D1=D2),--($A$1:$A1=A2))>0,"Duplicate",""),""))),""))

then copy it down to the bottom of the list,

Then you can filter or highlight based on column E having the word "Duplicate" in it.

Note: The 44's in the formula represent the bottom of your table range. If needed change all the 44's only to match the bottom row of your range.