Below I have a group of Cells in Columns A & B. They represent backup tape
numbers and a description. I need to build some kind of a statement that
will tell me the last tape numbe in each set. A set consists of tapes 1 thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3 of 4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

What data is in what column?

Col. A Col. B Col. C
200504 Feb 2004 Onsite Tape 1 of 4 200507

Col A = 200504
Col B = Feb 2004 Onsite Tape 1 of 4
Col C = obvious

It looks like all you need to do is find the max for the month year.

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=MAX((LEFT(B$2:B$10,8)=LEFT(B2,8))*A$2:A$10)

Copy down as needed. This assumes the "date" format is always mmm yyyy.

Biff

"El Bee" wrote in message
...
Below I have a group of Cells in Columns A & B. They represent backup
tape
numbers and a description. I need to build some kind of a statement that
will tell me the last tape numbe in each set. A set consists of tapes 1
thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3 of
4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

I need to clarify. I failed to include another column, column "D" which
contains the date each tape was written to.
There are approx. 800 records that have bee sorted by Col. D then Col. B and
the formula would go in Col. C

So it should look like this:


Col. A Col. B Col. C
Col. D
tape # Description Master
Date Written to
200504 Feb 2004 Onsite Tape 1 of 4 200507 02/05/04
200505 Feb 2004 Onsite Tape 2 of 4 200507 02/05/04
200506 Feb 2004 Onsite Tape 3 of 4 200507 02/05/04
200507 Feb 2004 Onsite Tape 4 of 4 200507 02/05/04
200584 Feb 2005 Offsite Tape 1 of 2 200586 02/10/05
200586 Feb 2005 Offsite Tape 2 of 2 200586 02/10/05
200775 Feb 2006 Offstie Tape 1 of 3 200777 02/19/06
200776 Feb 2006 Offstie Tape 2 of 3 200777 02/19/06
200777 Feb 2006 Offstie Tape 3 of 3 200777 02/19/06


"T. Valko" wrote:

What data is in what column?

Col. A Col. B Col. C
200504 Feb 2004 Onsite Tape 1 of 4 200507

Col A = 200504
Col B = Feb 2004 Onsite Tape 1 of 4
Col C = obvious

It looks like all you need to do is find the max for the month year.

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=MAX((LEFT(B$2:B$10,8)=LEFT(B2,8))*A$2:A$10)

Copy down as needed. This assumes the "date" format is always mmm yyyy.

Biff

"El Bee" wrote in message
...
Below I have a group of Cells in Columns A & B. They represent backup
tape
numbers and a description. I need to build some kind of a statement that
will tell me the last tape numbe in each set. A set consists of tapes 1
thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3 of
4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

The formula will still work as is but this one may be slightly more
efficient:

Still, array entered (CSE):

=MAX((D$2:D$10=D2)*A$2:A$10)

Biff

"El Bee" wrote in message
...
I need to clarify. I failed to include another column, column "D" which
contains the date each tape was written to.
There are approx. 800 records that have bee sorted by Col. D then Col. B
and
the formula would go in Col. C

So it should look like this:


Col. A Col. B Col. C
Col. D
tape # Description Master
Date Written to
200504 Feb 2004 Onsite Tape 1 of 4 200507 02/05/04
200505 Feb 2004 Onsite Tape 2 of 4 200507 02/05/04
200506 Feb 2004 Onsite Tape 3 of 4 200507 02/05/04
200507 Feb 2004 Onsite Tape 4 of 4 200507 02/05/04
200584 Feb 2005 Offsite Tape 1 of 2 200586 02/10/05
200586 Feb 2005 Offsite Tape 2 of 2 200586 02/10/05
200775 Feb 2006 Offstie Tape 1 of 3 200777 02/19/06
200776 Feb 2006 Offstie Tape 2 of 3 200777 02/19/06
200777 Feb 2006 Offstie Tape 3 of 3 200777 02/19/06


"T. Valko" wrote:

What data is in what column?

Col. A Col. B Col. C
200504 Feb 2004 Onsite Tape 1 of 4 200507

Col A = 200504
Col B = Feb 2004 Onsite Tape 1 of 4
Col C = obvious

It looks like all you need to do is find the max for the month year.

Try this entered as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER):

=MAX((LEFT(B$2:B$10,8)=LEFT(B2,8))*A$2:A$10)

Copy down as needed. This assumes the "date" format is always mmm yyyy.

Biff

"El Bee" wrote in message
...
Below I have a group of Cells in Columns A & B. They represent backup
tape
numbers and a description. I need to build some kind of a statement
that
will tell me the last tape numbe in each set. A set consists of tapes
1
thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3
of
4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the
max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

Yes it does; thanks for the help!!!! :)

"T. Valko" wrote:

The formula will still work as is but this one may be slightly more
efficient:

Still, array entered (CSE):

=MAX((D$2:D$10=D2)*A$2:A$10)

Biff

"El Bee" wrote in message
...
I need to clarify. I failed to include another column, column "D" which
contains the date each tape was written to.
There are approx. 800 records that have bee sorted by Col. D then Col. B
and
the formula would go in Col. C

So it should look like this:


Col. A Col. B Col. C
Col. D
tape # Description Master
Date Written to
200504 Feb 2004 Onsite Tape 1 of 4 200507 02/05/04
200505 Feb 2004 Onsite Tape 2 of 4 200507 02/05/04
200506 Feb 2004 Onsite Tape 3 of 4 200507 02/05/04
200507 Feb 2004 Onsite Tape 4 of 4 200507 02/05/04
200584 Feb 2005 Offsite Tape 1 of 2 200586 02/10/05
200586 Feb 2005 Offsite Tape 2 of 2 200586 02/10/05
200775 Feb 2006 Offstie Tape 1 of 3 200777 02/19/06
200776 Feb 2006 Offstie Tape 2 of 3 200777 02/19/06
200777 Feb 2006 Offstie Tape 3 of 3 200777 02/19/06


"T. Valko" wrote:

What data is in what column?

Col. A Col. B Col. C
200504 Feb 2004 Onsite Tape 1 of 4 200507

Col A = 200504
Col B = Feb 2004 Onsite Tape 1 of 4
Col C = obvious

It looks like all you need to do is find the max for the month year.

Try this entered as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER):

=MAX((LEFT(B$2:B$10,8)=LEFT(B2,8))*A$2:A$10)

Copy down as needed. This assumes the "date" format is always mmm yyyy.

Biff

"El Bee" wrote in message
...
Below I have a group of Cells in Columns A & B. They represent backup
tape
numbers and a description. I need to build some kind of a statement
that
will tell me the last tape numbe in each set. A set consists of tapes
1
thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3
of
4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the
max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

You're welcome!

Biff

"El Bee" wrote in message
...
Yes it does; thanks for the help!!!! :)

"T. Valko" wrote:

The formula will still work as is but this one may be slightly more
efficient:

Still, array entered (CSE):

=MAX((D$2:D$10=D2)*A$2:A$10)

Biff

"El Bee" wrote in message
...
I need to clarify. I failed to include another column, column "D" which
contains the date each tape was written to.
There are approx. 800 records that have bee sorted by Col. D then Col.
B
and
the formula would go in Col. C

So it should look like this:


Col. A Col. B Col. C
Col. D
tape # Description Master
Date Written to
200504 Feb 2004 Onsite Tape 1 of 4 200507 02/05/04
200505 Feb 2004 Onsite Tape 2 of 4 200507 02/05/04
200506 Feb 2004 Onsite Tape 3 of 4 200507 02/05/04
200507 Feb 2004 Onsite Tape 4 of 4 200507 02/05/04
200584 Feb 2005 Offsite Tape 1 of 2 200586 02/10/05
200586 Feb 2005 Offsite Tape 2 of 2 200586 02/10/05
200775 Feb 2006 Offstie Tape 1 of 3 200777 02/19/06
200776 Feb 2006 Offstie Tape 2 of 3 200777 02/19/06
200777 Feb 2006 Offstie Tape 3 of 3 200777 02/19/06


"T. Valko" wrote:

What data is in what column?

Col. A Col. B Col. C
200504 Feb 2004 Onsite Tape 1 of 4 200507

Col A = 200504
Col B = Feb 2004 Onsite Tape 1 of 4
Col C = obvious

It looks like all you need to do is find the max for the month year.

Try this entered as an array using the key combination of
CTRL,SHIFT,ENTER
(not just ENTER):

=MAX((LEFT(B$2:B$10,8)=LEFT(B2,8))*A$2:A$10)

Copy down as needed. This assumes the "date" format is always mmm
yyyy.

Biff

"El Bee" wrote in message
...
Below I have a group of Cells in Columns A & B. They represent
backup
tape
numbers and a description. I need to build some kind of a statement
that
will tell me the last tape numbe in each set. A set consists of
tapes
1
thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have
1,2,3
of
4.
In the first 4 records the last tape in that sequence would be
200507

The formula would go in Col. C and I showed what the results should
be
(manually entered)

I have tried using vlookup and building an IF statement but I hit
the
max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

El Bee,
I am assuming that the month/year is the deciding element, hence we can
rely on the first 8 characters to identify groups). I am also assuming
that the code is a number. Thus, in C2:

=MAX(IF(LEFT($B$2:$B$100,8)=LEFT(B2,8),$A$2:$A$100 ))

This is an array formula, thus you need to use Ctrl+Shift+Enter when
entering it.

HTH
Kostis Vezerides


El Bee wrote:
Below I have a group of Cells in Columns A & B. They represent backup tape
numbers and a description. I need to build some kind of a statement that
will tell me the last tape numbe in each set. A set consists of tapes 1 thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3 of 4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777

I need to clarify. I failed to include another column, column "D" which
contains the date each tape was written to.
There are approx. 800 records that have bee sorted by Col. D then Col. B and
the formula would go in Col. C

So it should look like this:


Col. A Col. B Col. C
Col. D
tape # Description Master
Date Written to
200504 Feb 2004 Onsite Tape 1 of 4 200507 02/05/04
200505 Feb 2004 Onsite Tape 2 of 4 200507 02/05/04
200506 Feb 2004 Onsite Tape 3 of 4 200507 02/05/04
200507 Feb 2004 Onsite Tape 4 of 4 200507 02/05/04
200584 Feb 2005 Offsite Tape 1 of 2 200586 02/10/05
200586 Feb 2005 Offsite Tape 2 of 2 200586 02/10/05
200775 Feb 2006 Offstie Tape 1 of 3 200777 02/19/06
200776 Feb 2006 Offstie Tape 2 of 3 200777 02/19/06
200777 Feb 2006 Offstie Tape 3 of 3 200777 02/19/06



"El Bee" wrote:

Below I have a group of Cells in Columns A & B. They represent backup tape
numbers and a description. I need to build some kind of a statement that
will tell me the last tape numbe in each set. A set consists of tapes 1 thru
# where # could be 1,2,3 or 4. Some sets have 1 of 2 others have 1,2,3 of 4.
In the first 4 records the last tape in that sequence would be 200507

The formula would go in Col. C and I showed what the results should be
(manually entered)

I have tried using vlookup and building an IF statement but I hit the max
of the If's.
Any ideas would be appreciatied.

here's the data:

Col. A Col. B Col. C
tape # Description Master
200504 Feb 2004 Onsite Tape 1 of 4 200507
200505 Feb 2004 Onsite Tape 2 of 4 200507
200506 Feb 2004 Onsite Tape 3 of 4 200507
200507 Feb 2004 Onsite Tape 4 of 4 200507
200584 Feb 2005 Offsite Tape 1 of 2 200586
200586 Feb 2005 Offsite Tape 2 of 2 200586
200775 Feb 2006 Offstie Tape 1 of 3 200777
200776 Feb 2006 Offstie Tape 2 of 3 200777
200777 Feb 2006 Offstie Tape 3 of 3 200777