Any other excel function rather than set FILTER

[florencewhm]

Hello excel experts,

this is my daily assignment & I have to filter one by one & sometimes it will create mistake if I miss out 1 of filter value. Furthermore I can not write a VBA scripts, is there any other method rather than filter one by one?

Column A:
Approved
Above 20%
Below 10%
Compliant

Column B
1) C*** 0.01
2) C*** $0.01
3) $0.01C***
4) $0.01/C***
5) $.01/C***
6) C***

7) R12345
8) REG 12345
9) 12345
10) $0.01-12345
11) $.01-12345
12) $0.01+REG
13) 12345 $0.01
14) 12345 $.01
15) 12345 REG

Column C
I set filter Any condition in column A & and if it fulfills the conditions 1) - 6) in column B, then I need the output in column C=CONTRACT

whereas if it fulfills the conditions 7) - 15) in column B, then I need the output in column C=REGISTRATION

Thanks
Flo

 

[viquarshaikh]

Can you add another worksheet to your workbook. Let us say the name of this sheet is Sheet3

Then in your new Sheet Create these two lists one in Column A and another in Column B. Be sure to have the Two Columns formatted as Text.

Column A
C*** 0.01
C*** $0.01
$0.01C***
$0.01/C***
$.01/C***
C***

Column B
R12345
REG 12345
12345
$0.01-12345
$.01-12345
$0.01+REG
12345 $0.01
12345 $.01
12345 REG


Now Come back to your original Sheet and in Column C , Cell C2, write this formula,

=if(iserror(vlookup(B2, 'Sheet3'!A:A, 1, 0)),if(iserror(vlookup(B2, 'Sheet3'!B:B, 1, 0)),"Not in the list","Registration"), "Contract")

Hopefully this should help. I'm sorry I've not tested this myself since, as usual, my Excel's busy running some processes.

 

[Andrew Poulsom]

Do you want to do this using Advanced Filter?