Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 3
There are 1 users currently browsing forums.
|
 |
08-15-2007, 10:18 AM
|
|
Registered User
|
|
Join Date: 13 Jun 2005
Posts: 10
|
|
|
filter function with criteria
Please Register to Remove these Ads
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
|
08-15-2007, 11:10 AM
|
 |
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,517
|
|
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.
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
08-15-2007, 11:18 AM
|
|
Registered User
|
|
Join Date: 13 Jun 2005
Posts: 10
|
|
fantastic....thank you so much NBVC.
Quote:
|
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 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.
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
