IF statement

[Guest]

In a worksheet I am trying to write an IF statement that will fill-in column
F with the appropriate Picking Style.

Here are the qualifiers:

1) A1 and A2 are not equal and B1 is blank = "Unit Pick".
If A1 and A2 are not equal and B1 is not blank "Case Pick".
If A1 and A2 are equal and B1 has text and B2 is empty "Both types of Picks".



A B C D E F
01201-0083 LR-27-01-01 25.99 3 Unit Pick Only
01201-0084 LQ-16-04-02 22.99 2 Unit Pick Only
01201-0088 1 DISC ARROW S LN-52-04-02 22.99 3 Case Pick
01201-0093 1 BALL BRZ SM LG-49-01-01 79 47 Case Pick
01201-0093 1 BALL BRZ SM LH-62-03-02 79 56 Case Pick
01201-0093 LG-13-04-02 79 23 Both Case and Unit Pick


Thanks for the Help,
Tom

 

[brett.kaplan]

Try this (though I didn't test it out):

=IF(AND(A1<>A2,B1=""),"Unit Pick",IF(AND(A1<>A2,B1<>""),"Case
Pick",IF(AND(A1=A2,ISTEXT(B1)=TRUE,B2=""),"Both types of
Picks","Invalid Combination")))

 

[Bob Phillips]

=IF(A1<>A2,IF(B1="",Unit Pick,"Case
Pick"),IF(AND(NOT(ISNUMBER(B1)),B2=""),"Both types of Picks",""))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Bob,
Thanks for the response however it did not solve my problem, which I
probably did not explain correctly.

I have attached more examples for you to try and understand my needs:


A B C D E
01201-0083 LR-27-01-01 25.99 3 Unit Pick
01201-0084 LQ-16-04-02 22.99 2 Unit Pick
01201-0088 1 DISC ARROW S LN-52-04-02 22.99 3 Case Pick
01201-0093 1 BALL BRZ SM LG-49-01-01 79 47 Case Pick
01201-0093 1 BALL BRZ SM LH-62-03-02 79 56 Case Pick
01201-0093 LG-13-04-02 79 23 Both types of Picks
01201-0094 1 BALL BRZ SM LN-11-05-02 89 36 Case Pick
01201-0094 1 BALL BRZ SM LP-34-03-01 89 10 Case Pick
01201-0094 1 BALL BRZ SM LP-54-01-02 89 88 Case Pick
01201-0095 1 BALL BRZ SM LC-70-05-01 109 9 Case Pick
01201-0095 LN-07-02-01 109 1 Both types of Picks
01201-0096 1 BALL BRZ SM LN-26-01-01 129 3 Case Pick
01201-0096 1 BALL BRZ SM LO-65-04-02 129 24 Case Pick
01201-0096 LN-26-01-01 129 3 Both types of Picks
01201-0097 1 BALL BRZ LG LE-38-02-01 99 40 Case Pick
01201-0097 1 BALL BRZ LG LG-34-02-02 99 29 Case Pick
01201-0098 1 BALL BRZ LG LC-62-05-02 109 12 Case Pick
01201-0098 1 BALL BRZ LG LD-14-05-01 109 8 Case Pick
01201-0098 1 BALL BRZ LG LD-35-05-01 109 20 Case Pick
01201-0099 1 BALL BRZ LG LC-40-06-01 129 6 Case Pick
01201-0099 1 BALL BRZ LG LC-70-06-01 129 2 Case Pick
01201-0100 1 BALL BRZ LG LE-55-03-01 149 20 Case Pick
01201-0100 1 BALL BRZ LG LR-37-03-02 149 15 Case Pick
01201-0101 LD-23-03-01 22 100 Unit Pick
01201-0101 LH-57-01-02 22 139 Unit Pick
01201-0102 1 SQUARE BRZ S LN-58-05-01 79 134 Case Pick
01201-0102 1 SQUARE BRZ S LQ-01-01-02 79 2 Case Pick
01201-0103 1 SQUARE BRZ S LN-10-03-02 89 9 Case Pick
01201-0104 1 SQUARE BRZ S LC-28-03-01 109 1 Case Pick
01201-0104 1 SQUARE BRZ S LG-18-04-02 109 8 Case Pick
01201-0105 1 SQUARE BRZ S LD-67-03-02 129 1 Case Pick
01201-0105 1 SQUARE BRZ S LN-03-02-02 129 1 Case Pick
01201-0106 1 SQUARE BRZ L LO-25-03-01 99 4 Case Pick
01201-0106 1 SQUARE BRZ L LR-45-03-02 99 14 Case Pick
01201-0107 1 SQUARE BRZ L LC-52-05-02 109 3 Case Pick
01201-0107 1 SQUARE BRZ L LP-16-01-01 109 68 Case Pick
01201-0107 LG-09-02-02 109 6 Both types of Picks
01201-0107 LH-64-04-01 109 2 Both types of Picks
01201-0107 LN-15-02-02 109 5 Both types of Picks

TFTH,
Tom

 

[Bob Phillips]

According to your original rules, as A1<>A2, and B1 is not blank, it should
be Case Pick, but you now say it should be Unit Pick. Confused!

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Bob,
I thought that I outlined it correctly, however the rule should be:

1) A1 and A2 are not equal and B1 is blank = "Unit Pick".

TFTH,
Tom

 

[Bob Phillips]

That is what you originally said, that it hat I echoed, so how come this

A B C D E
01201-0083 LR-27-01-01 25.99 3 Unit Pick

B1 is not blank so it shouldn't be Unit Pick
--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)