need complex if formula (I think)

[b4nature]

In the sample below I need to be able to isolate students who have a G status
for the VERREV code and an S or W status for all of their other codes. I have
no idea where to begin.
Thanks!

A B C
1 Student Status Code
2 Krystal G VERREV
3 Krystal S LINCBY
4 Krystal S QAWK10
5 Krystal S TAX08S
6 Krystal S W208ST
7 Krystal W TAX08P
8 Krystal W W208PA
9 Margaret T CORREC
10 Margaret S QAWK10
11 Margaret S TAX08S
12 Margaret S VERREV
13 Margaret S W208ST
14 Michael S LINCBY
15 Michael S QAWK10
16 Michael G VERREV
17 Michael W TAX08S
18 Michael W W208ST

 

[Max]

One idea - use autofilter on a helper col
Assuming source data as posted in A2:C2 down
In D2: =IF(AND(TRIM(C2)="VERREV",OR(TRIM(B2)={"S","W"})),"x","")
Copy down all the way. Apply Autofilter on col D, choose: x
to filter/isolate as required.

Success? High-five it here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Ashish Mathur]

Hi,

Try this. Copy A1:C1 and paste to E1:G1. In F1, type G and in G1, type
VERREV. In F2, type S and in F3, type W. Now go to Data > Filter >
Advanced Filter. In the Action section, select "Copy to another location.
In the list range, select range A1:C18. In the Criteria range, select
E1:G3. In the copy to box, select A25 and now click on OK.

This will extract the record to the desired range.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bernd P]

Hello,

Enter
=REPT(A2,(C2="VERREV")*(B2="G")*(SUMPRODUCT(--(A2=$A$2:$A$999),SIGN(($B
$2:$B$999="S")+($B$2:$B$999="W")),--($C$2:$C$999<>"VERREV"))=SUMPRODUCT
(--(A2=$A$2:$A$999))-1))
and copy down, then replace by values and sort, for example.

Please note that Google groups tend to insert unwanted "-" characters
in long lines - you might need to search and delete those...(in my
published formula)

Regards,
Bernd