From: Mr. Low on 2 Apr 2010 08:35 Hi, Let's consider the table below: A B C 1 Invoice Line code Selection 2 2010030101 18 A 3 2010030101 2 A 4 2010030102 3 B 5 2010030102 3 B 6 2010030103 18 A 7 2010030103 6 A 8 2010030104 4 B 9 2010030104 6 B 10 2010030105 5 B 11 2010030105 2 B 12 2010030106 1 A 13 2010030106 1 A 14 2010030106 18 A 15 2010030106 2 A May I know the formula to input at cell C2 in order to label each invoice batch as "A" when one of its line codes is "18" ? Otherwise label it as "B". Thanks Low From: Jeanette on 2 Apr 2010 08:58 =IF(B1=18,"A","B") "Mr. Low" wrote: > Hi, > > Let's consider the table below: > > A B C > 1 Invoice Line code Selection > 2 2010030101 18 A > 3 2010030101 2 A > 4 2010030102 3 B > 5 2010030102 3 B > 6 2010030103 18 A > 7 2010030103 6 A > 8 2010030104 4 B > 9 2010030104 6 B > 10 2010030105 5 B > 11 2010030105 2 B > 12 2010030106 1 A > 13 2010030106 1 A > 14 2010030106 18 A > 15 2010030106 2 A > > May I know the formula to input at cell C2 in order to label each invoice > batch as "A" when one of its line codes is "18" ? Otherwise label it as "B". > > Thanks > > Low > > From: Jeanette on 2 Apr 2010 09:01 Sorry B2, not B1 =IF(B2=18,"A","B") "Jeanette" wrote: > =IF(B1=18,"A","B") > > "Mr. Low" wrote: > > > Hi, > > > > Let's consider the table below: > > > > A B C > > 1 Invoice Line code Selection > > 2 2010030101 18 A > > 3 2010030101 2 A > > 4 2010030102 3 B > > 5 2010030102 3 B > > 6 2010030103 18 A > > 7 2010030103 6 A > > 8 2010030104 4 B > > 9 2010030104 6 B > > 10 2010030105 5 B > > 11 2010030105 2 B > > 12 2010030106 1 A > > 13 2010030106 1 A > > 14 2010030106 18 A > > 15 2010030106 2 A > > > > May I know the formula to input at cell C2 in order to label each invoice > > batch as "A" when one of its line codes is "18" ? Otherwise label it as "B". > > > > Thanks > > > > Low > > > > From: Mr. Low on 2 Apr 2010 13:35 Hi Jeanette, This only select the row with "18", not the the rest of the line with the similar invoice number, eg, I need to select row 12 to row 15 as "A" when onlt row 14 has the code "18". Any other solution ? "Jeanette" wrote: > Sorry B2, not B1 > > =IF(B2=18,"A","B") > > "Jeanette" wrote: > > > =IF(B1=18,"A","B") > > > > "Mr. Low" wrote: > > > > > Hi, > > > > > > Let's consider the table below: > > > > > > A B C > > > 1 Invoice Line code Selection > > > 2 2010030101 18 A > > > 3 2010030101 2 A > > > 4 2010030102 3 B > > > 5 2010030102 3 B > > > 6 2010030103 18 A > > > 7 2010030103 6 A > > > 8 2010030104 4 B > > > 9 2010030104 6 B > > > 10 2010030105 5 B > > > 11 2010030105 2 B > > > 12 2010030106 1 A > > > 13 2010030106 1 A > > > 14 2010030106 18 A > > > 15 2010030106 2 A > > > > > > May I know the formula to input at cell C2 in order to label each invoice > > > batch as "A" when one of its line codes is "18" ? Otherwise label it as "B". > > > > > > Thanks > > > > > > Low > > > > > > From: Max on 5 Apr 2010 02:54 Try this in C2, which will involve both the invoice number and line code: =IF(SUMPRODUCT((\$A\$2:\$A\$15=A2)*(\$B\$2:\$B\$15=18))>0,"A","B") Copy down to return desired results. Success? hit YES below -- Max Singapore --- "Mr. Low" wrote:> This only select the row with "18", not the the rest of the line with the > similar invoice number, eg, I need to select row 12 to row 15 as "A" when > only row 14 has the code "18". Any other solution ?  |  Next  |  Last