|
IF statement
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 |
IF statement
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"))) tom wrote: 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 |
IF statement
=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) "tom" wrote in message ... 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 |
IF statement
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" wrote: =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) "tom" wrote in message ... 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 |
IF statement
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) "tom" wrote in message ... 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" wrote: =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) "tom" wrote in message ... 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 |
IF statement
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" wrote: 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) "tom" wrote in message ... 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" wrote: =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) "tom" wrote in message ... 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 |
IF statement
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) "tom" wrote in message ... 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" wrote: 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) "tom" wrote in message ... 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" wrote: =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) "tom" wrote in message ... 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 |
| All times are GMT +1. The time now is 03:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com