About Us

Ron Rosenfeld

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Check if a cell contains text compared to a range of cells	GD1226, Captain Ahab, manface	Excel Discussion (Misc queries)	5	April 13th 07 01:00 AM
Return adjacent cell if conditional formatting exists.	Donna	Excel Worksheet Functions	12	November 10th 06 04:34 AM
Looking and matching vaules to return another vaule	JakeMan	Excel Discussion (Misc queries)	0	June 29th 06 04:40 PM
Check if a number exists in a range?	gkaste	Excel Discussion (Misc queries)	2	July 13th 05 08:00 PM
check if reference exists, then return its value or return 0	doudou	Excel Worksheet Functions	1	June 4th 05 09:17 PM

Menu

About Us