filter function with criteria

[bklim]

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

[NBVC]

You'll probably need a helper column.

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

In E2, enter

Please Login or Register  to view this content.

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.

[bklim]

fantastic....thank you so much NBVC.

You'll probably need a helper column.

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

In E2, enter

Please Login or Register  to view this content.

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.