|
Real "IF"y Question
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 |
Real "IF"y Question
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 |
Real "IF"y Question
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 |
Real "IF"y Question
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 |
Real "IF"y Question
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 |
Real "IF"y Question
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 |
Real "IF"y Question
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 |
Real "IF"y Question
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 |
| All times are GMT +1. The time now is 05:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com