Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need complex if formula (I think)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need complex if formula (I think)
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 --- "b4nature" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need complex if formula (I think)
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 "b4nature" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need complex if formula (I think)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex formula? | Excel Discussion (Misc queries) | |||
Complex Formula | Excel Discussion (Misc queries) | |||
Complex Formula | Excel Worksheet Functions | |||
Can someone help with this complex formula? | Excel Worksheet Functions | |||
Complex formula | Excel Discussion (Misc queries) |