software

This is the data in cells T, U, and V in the tab called ODDS(Archives)
BOSNYY3-Apr
PITMIL4-Apr
CINNYM4-Apr
PHIWAS4-Apr
BOSNYY4-Apr

This goes on all the way to row 5000.

In my tab called BOS I have cell A2 with the following formula in it:
=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
is what I want. I would like cell A3 to find the next occurance of BOS and
then display the value from cell V (which would be 4-Apr). I would cells,
A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
Personally, I to find those results, I would just do Data gt; Filter gt;
AutoFilter on the ODDS(Archices) sheet......It will bring up all the
instances of BOS, if there were one or fifty, or whatever. Then, that data
or parts of it can be copied to wherever else it might be needed..........

Vaya con Dios,
Chuck, CABGx3quot;Jambruinsquot; wrote:

gt; This is the data in cells T, U, and V in the tab called ODDS(Archives)
gt; BOSNYY3-Apr
gt; PITMIL4-Apr
gt; CINNYM4-Apr
gt; PHIWAS4-Apr
gt; BOSNYY4-Apr
gt;
gt; This goes on all the way to row 5000.
gt;
gt; In my tab called BOS I have cell A2 with the following formula in it:
gt; =VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
gt; is what I want. I would like cell A3 to find the next occurance of BOS and
gt; then display the value from cell V (which would be 4-Apr). I would cells,
gt; A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
gt;
gt;

=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)gt;=ROW()-1,INDIRECT(quot;'ODDS
(Archives)'!Vquot;amp;SMALL(('ODDS
(Archives)'!$T$1:$T$5=quot;BOSquot;)*ROW($T$1:$T$5),ROW() SUM(--('ODDS
(Archives)'!$T$1:$T$5lt;gt;$A$1))-1)),quot;quot;)

This is an array formula. Paste the formula in and hit ctrl shift enter.
Copy down as needed. It outputs blanks if there are not anymore occurences.
I assumed A1 contains quot;BOSquot;.

quot;Jambruinsquot; wrote:

gt; This is the data in cells T, U, and V in the tab called ODDS(Archives)
gt; BOSNYY3-Apr
gt; PITMIL4-Apr
gt; CINNYM4-Apr
gt; PHIWAS4-Apr
gt; BOSNYY4-Apr
gt;
gt; This goes on all the way to row 5000.
gt;
gt; In my tab called BOS I have cell A2 with the following formula in it:
gt; =VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
gt; is what I want. I would like cell A3 to find the next occurance of BOS and
gt; then display the value from cell V (which would be 4-Apr). I would cells,
gt; A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
gt;
gt;

On Fri, 17 Mar 2006 10:55:26 -0800, Jambruins
gt; wrote:

gt;This is the data in cells T, U, and V in the tab called ODDS(Archives)
gt;BOSNYY3-Apr
gt;PITMIL4-Apr
gt;CINNYM4-Apr
gt;PHIWAS4-Apr
gt;BOSNYY4-Apr
gt;
gt;This goes on all the way to row 5000.
gt;
gt;In my tab called BOS I have cell A2 with the following formula in it:
gt;=VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
gt;is what I want. I would like cell A3 to find the next occurance of BOS and
gt;then display the value from cell V (which would be 4-Apr). I would cells,
gt;A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
gt;
gt;You could try this **array-entered** formula:

=IF(ROWS($1:1)gt;COUNTIF(OFFSET(tbl,,,,1),$A$1),quot;quot;,
LARGE((OFFSET(tbl,,,,1)=$A$1)*OFFSET(tbl,,2,,1),
COUNTIF(OFFSET(tbl,,,,1),$A$1) 1-ROWS($1:1)))

You will need to define rng (Insert/Name/Define) as

'ODDS (Archives)'!T1:Vnnnn

where nnnn is as small a number that will safely encompass your entire table.

Two reasons:
1. The smaller the array, the faster the formula will run.
2. An array formula may not refer to an entire column.

I don't know whether this formula will run quickly enough for you on your DB.

To enter an **array** formula, hold down lt;ctrlgt;lt;shiftgt; while hitting lt;entergt;.
Excel will place braces {...} around the formula.
--ron

Sloth, thanks that works great. Another similar question.

I would like the same thing to happen in cells B3, B4, etc. except instead
of taking the value from cell V I would like it to take the value from cell
U. How would I modify the formula you gave me? Thanks.
gt; gt; This is the data in cells T, U, and V in the tab called ODDS(Archives)
gt; gt; BOSNYY3-Apr
gt; gt; PITMIL4-Apr
gt; gt; CINNYM4-Apr
gt; gt; PHIWAS4-Apr
gt; gt; BOSNYY4-Apr
gt; gt;
gt; gt; This goes on all the way to row 5000.
gt; gt;
gt; gt; In my tab called BOS I have cell A2 with the following formula in it:
gt; gt; =VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
gt; gt; is what I want. I would like cell A3 to find the next occurance of BOS and
gt; gt; then display the value from cell V (which would be 4-Apr). I would cells,
gt; gt; A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
gt; gt;
gt; gt;

=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)gt;=ROW()-1,INDIRECT(quot;'ODDS
(Archives)'!Uquot;amp;SMALL(('ODDS
(Archives)'!$T$1:$T$5=quot;BOSquot;)*ROW($T$1:$T$5),ROW() SUM(--('ODDS
(Archives)'!$T$1:$T$5lt;gt;$A$1))-1)),quot;quot;)

quot;Jambruinsquot; wrote:

gt; Sloth, thanks that works great. Another similar question.
gt;
gt; I would like the same thing to happen in cells B3, B4, etc. except instead
gt; of taking the value from cell V I would like it to take the value from cell
gt; U. How would I modify the formula you gave me? Thanks.
gt;
gt;
gt;
gt; gt; gt; This is the data in cells T, U, and V in the tab called ODDS(Archives)
gt; gt; gt; BOSNYY3-Apr
gt; gt; gt; PITMIL4-Apr
gt; gt; gt; CINNYM4-Apr
gt; gt; gt; PHIWAS4-Apr
gt; gt; gt; BOSNYY4-Apr
gt; gt; gt;
gt; gt; gt; This goes on all the way to row 5000.
gt; gt; gt;
gt; gt; gt; In my tab called BOS I have cell A2 with the following formula in it:
gt; gt; gt; =VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
gt; gt; gt; is what I want. I would like cell A3 to find the next occurance of BOS and
gt; gt; gt; then display the value from cell V (which would be 4-Apr). I would cells,
gt; gt; gt; A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
gt; gt; gt;
gt; gt; gt;

change both formulas to the following. I had an error that wouldn't allow
for other tabs to work properly.

For column V:
=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)gt;=ROW()-1,INDIRECT(quot;'ODDS
(Archives)'!Vquot;amp;SMALL(('ODDS
(Archives)'!$T$1:$T$5=$A$1)*ROW($T$1:$T$5),ROW() S UM(--('ODDS
(Archives)'!$T$1:$T$5lt;gt;$A$1))-1)),quot;quot;)

For column U:
=IF(COUNTIF('ODDS (Archives)'!$T$1:$T$5,$A$1)gt;=ROW()-1,INDIRECT(quot;'ODDS
(Archives)'!Uquot;amp;SMALL(('ODDS
(Archives)'!$T$1:$T$5=$A$1)*ROW($T$1:$T$5),ROW() S UM(--('ODDS
(Archives)'!$T$1:$T$5lt;gt;$A$1))-1)),quot;quot;)

quot;Jambruinsquot; wrote:

gt; Sloth, thanks that works great. Another similar question.
gt;
gt; I would like the same thing to happen in cells B3, B4, etc. except instead
gt; of taking the value from cell V I would like it to take the value from cell
gt; U. How would I modify the formula you gave me? Thanks.
gt;
gt;
gt;
gt; gt; gt; This is the data in cells T, U, and V in the tab called ODDS(Archives)
gt; gt; gt; BOSNYY3-Apr
gt; gt; gt; PITMIL4-Apr
gt; gt; gt; CINNYM4-Apr
gt; gt; gt; PHIWAS4-Apr
gt; gt; gt; BOSNYY4-Apr
gt; gt; gt;
gt; gt; gt; This goes on all the way to row 5000.
gt; gt; gt;
gt; gt; gt; In my tab called BOS I have cell A2 with the following formula in it:
gt; gt; gt; =VLOOKUP(A1,'ODDS (Archives)'!T:V,3,0). This gives me the value 3-Apr which
gt; gt; gt; is what I want. I would like cell A3 to find the next occurance of BOS and
gt; gt; gt; then display the value from cell V (which would be 4-Apr). I would cells,
gt; gt; gt; A4, A5, etc. to keep doing this. Any idea how to do this? Thanks
gt; gt; gt;
gt; gt; gt;