Prev: Vlookup based on a unique "Text" occur
Next: Having a macro/function available in new spreadsheets
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
Pages: 1 2
Prev: Vlookup based on a unique "Text" occur
Next: Having a macro/function available in new spreadsheets