Check if text exists within a cell range and return logical vaule - possible?
 

Hi guys,

I have a column in a worksheet that checks whether a particular text
string (a shipment code) in a different column, but same row, has
changed compared to the string above it. If it has (i.e a new shipment
code is present), then the value in the column increments by 1
indicating that it is the next shipment.

e.g.

Column A is a list of shipment code - 01GRT3 for example
Column B is a list of the shipment number, let's called 01GRT3
shipment 1 for simplicity.

If we say that A1:A5 have the text 01GRT3 in them, then B1:B5 will
contain the number 1.

If then a new shipment starts in A6, lets say 01GRT4 then B6 will
contain the number 2.

I currently have an IF statement that checks whether the previous cell
has changed so that column B updates:

So for cell B6:

=IF(A6=A5,B5,B5+1)

so if the shipment code is still the same, it returns the number in
the cell above, if not it increments by 1. In this case it would
increment because A5 is 01GRT3 and A6 is 01GRT4.

Now sometimes, a shipment code can return further down i.e 01GRT3
could reappear, however when it does column B would still increment
the shipment number and I do not want this to happen because this
particular shipment number has already been accounted for previously.

Is it therefore possible to add an additional condition to my IF
statement that checks whether that particular shipment code has been
present in the previous range i.e. check whether "01GRT3" exists at
all within A1:A5 and then return a logical value of "true"? cell B6
would then rely on both the 'A6=A5' check and this new lookup type
check, with both returning logical values.

Any ideas? I'm very stuck!

Tom

Check if text exists within a cell range and return logical vaule
 

try
=IF(countif(A$1:A5,A6)0,B5,B5+1)

" wrote:

Hi guys,

I have a column in a worksheet that checks whether a particular text
string (a shipment code) in a different column, but same row, has
changed compared to the string above it. If it has (i.e a new shipment
code is present), then the value in the column increments by 1
indicating that it is the next shipment.

e.g.

Column A is a list of shipment code - 01GRT3 for example
Column B is a list of the shipment number, let's called 01GRT3
shipment 1 for simplicity.

If we say that A1:A5 have the text 01GRT3 in them, then B1:B5 will
contain the number 1.

If then a new shipment starts in A6, lets say 01GRT4 then B6 will
contain the number 2.

I currently have an IF statement that checks whether the previous cell
has changed so that column B updates:

So for cell B6:

=IF(A6=A5,B5,B5+1)

so if the shipment code is still the same, it returns the number in
the cell above, if not it increments by 1. In this case it would
increment because A5 is 01GRT3 and A6 is 01GRT4.

Now sometimes, a shipment code can return further down i.e 01GRT3
could reappear, however when it does column B would still increment
the shipment number and I do not want this to happen because this
particular shipment number has already been accounted for previously.

Is it therefore possible to add an additional condition to my IF
statement that checks whether that particular shipment code has been
present in the previous range i.e. check whether "01GRT3" exists at
all within A1:A5 and then return a logical value of "true"? cell B6
would then rely on both the 'A6=A5' check and this new lookup type
check, with both returning logical values.

Any ideas? I'm very stuck!

Tom

Check if text exists within a cell range and return logical vaule - possible?
 

On Thu, 19 Jul 2007 07:01:28 -0700, wrote:

Hi guys,

I have a column in a worksheet that checks whether a particular text
string (a shipment code) in a different column, but same row, has
changed compared to the string above it. If it has (i.e a new shipment
code is present), then the value in the column increments by 1
indicating that it is the next shipment.

e.g.

Column A is a list of shipment code - 01GRT3 for example
Column B is a list of the shipment number, let's called 01GRT3
shipment 1 for simplicity.

If we say that A1:A5 have the text 01GRT3 in them, then B1:B5 will
contain the number 1.

If then a new shipment starts in A6, lets say 01GRT4 then B6 will
contain the number 2.

I currently have an IF statement that checks whether the previous cell
has changed so that column B updates:

So for cell B6:

=IF(A6=A5,B5,B5+1)

so if the shipment code is still the same, it returns the number in
the cell above, if not it increments by 1. In this case it would
increment because A5 is 01GRT3 and A6 is 01GRT4.

Now sometimes, a shipment code can return further down i.e 01GRT3
could reappear, however when it does column B would still increment
the shipment number and I do not want this to happen because this
particular shipment number has already been accounted for previously.

Is it therefore possible to add an additional condition to my IF
statement that checks whether that particular shipment code has been
present in the previous range i.e. check whether "01GRT3" exists at
all within A1:A5 and then return a logical value of "true"? cell B6
would then rely on both the 'A6=A5' check and this new lookup type
check, with both returning logical values.

Any ideas? I'm very stuck!

Tom

Assuming your first entries are

A1: Code1
B1: 1

Then

B2: =IF(ISNA(VLOOKUP(A2,$A$1:B1,2,0)),MAX($B$1:B1)+1,I F(A2=A1,B1,TRUE))

should do what you describe.

However, it occurs to me that what you might want to do is still identify the
out of order code with the original sequential number, instead of returning
TRUE.

If that is the case, you can try this formula:

B2:
=IF(ISNA(VLOOKUP(A2,$A$1:B1,2,0)),MAX($B$1:B1)+1,V LOOKUP(A2,$A$1:B1,2,0))


WHichever formula you use in B2, copy/drag (fill) down as far as required.
--ron