OK, I've read ad nauseam all the posts about finding the last row in a column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and the
latest test date at row 61. If a person did not take the test, the column is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

Maybe this

=LOOKUP(10^23,A14:A61)

Format the cell as a date

Mike

"fedude" wrote:

> OK, I've read ad nauseam all the posts about finding the last row in a column
> with data. I couldn't find what I need. I have a slightly different
> question that I could use some help with.
>
> Lots of columns. In the "middle" of each column are 48 rows for a test
> scores. They are by date with the earliest date starting at row 14 and the
> latest test date at row 61. If a person did not take the test, the column is
> blank, otherwise their score is in the cell.
>
> For each of these students, I need to find the date at which they have the
> last score. So I need to search back starting at row 61 to row 14 to find the
> first non-blank cell and then record the date somehow.
>
> Is this something I can do with the existing excel formulas? If so I came
> up empty. My thought was to insert a custom function in each column.
>
> TIA!

Mike,

Two other pieces of information:

1) The dates of the tests are in column B, scores start in column CZ
2) The scores may be any number from 0 to 100 and not in any sorted order.
The highest score may not be the last score.

Maybe I just don't know how the LOOKUP function works, but with only 2
parameters, it's using the array form. Won't the result be the highest score
not the last score?



"Mike H" wrote:

> Maybe this
>
> =LOOKUP(10^23,A14:A61)
>
> Format the cell as a date
>
> Mike
>
> "fedude" wrote:
>
> > OK, I've read ad nauseam all the posts about finding the last row in a column
> > with data. I couldn't find what I need. I have a slightly different
> > question that I could use some help with.
> >
> > Lots of columns. In the "middle" of each column are 48 rows for a test
> > scores. They are by date with the earliest date starting at row 14 and the
> > latest test date at row 61. If a person did not take the test, the column is
> > blank, otherwise their score is in the cell.
> >
> > For each of these students, I need to find the date at which they have the
> > last score. So I need to search back starting at row 61 to row 14 to find the
> > first non-blank cell and then record the date somehow.
> >
> > Is this something I can do with the existing excel formulas? If so I came
> > up empty. My thought was to insert a custom function in each column.
> >
> > TIA!

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
where h1 has the scorer and col b has the names and col d has the scores
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"fedude" <(E-Mail Removed)> wrote in message
news:CF5279A1-9BFD-4D68-A42F-(E-Mail Removed)...
> OK, I've read ad nauseam all the posts about finding the last row in a
> column
> with data. I couldn't find what I need. I have a slightly different
> question that I could use some help with.
>
> Lots of columns. In the "middle" of each column are 48 rows for a test
> scores. They are by date with the earliest date starting at row 14 and
> the
> latest test date at row 61. If a person did not take the test, the column
> is
> blank, otherwise their score is in the cell.
>
> For each of these students, I need to find the date at which they have the
> last score. So I need to search back starting at row 61 to row 14 to find
> the
> first non-blank cell and then record the date somehow.
>
> Is this something I can do with the existing excel formulas? If so I came
> up empty. My thought was to insert a custom function in each column.
>
> TIA!

Don, you are almost my hero. This formula looks simple, but..... I probably
wasn't clear in my description

I can't seem to get this to work. Probably because I don't understand how
array functions work. Partly because I can't figure out anyway MAX will work
for this problem. I believe I entered it properly because I can see the
braces.

In your formula, I don't understand what you use H1 for? The student names
are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
looking for the last cell in the 14:61 range of each column that has
anything in it. I'll put this formula in row 136 of each column. This
formula will tell me the date of the last test score in the 14:61 range in
that column

The test dates are in B14:B61. Only column B has dates in them.
The test scores are in the next 200 columns in the same rows as the dates.


"Don Guillett" wrote:

> This is an ARRAY formula that must be entered using ctrl+shift+enter
>
> =MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
> where h1 has the scorer and col b has the names and col d has the scores
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "fedude" <(E-Mail Removed)> wrote in message
> news:CF5279A1-9BFD-4D68-A42F-(E-Mail Removed)...
> > OK, I've read ad nauseam all the posts about finding the last row in a
> > column
> > with data. I couldn't find what I need. I have a slightly different
> > question that I could use some help with.
> >
> > Lots of columns. In the "middle" of each column are 48 rows for a test
> > scores. They are by date with the earliest date starting at row 14 and
> > the
> > latest test date at row 61. If a person did not take the test, the column
> > is
> > blank, otherwise their score is in the cell.
> >
> > For each of these students, I need to find the date at which they have the
> > last score. So I need to search back starting at row 61 to row 14 to find
> > the
> > first non-blank cell and then record the date somehow.
> >
> > Is this something I can do with the existing excel formulas? If so I came
> > up empty. My thought was to insert a custom function in each column.
> >
> > TIA!
>
>

You may send your workbook to my address below along with a snippet of this
page and I'll take a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"fedude" <(E-Mail Removed)> wrote in message
news:78CF3AB1-F2B7-45A4-A5E8-(E-Mail Removed)...
> Don, you are almost my hero. This formula looks simple, but..... I
> probably
> wasn't clear in my description
>
> I can't seem to get this to work. Probably because I don't understand how
> array functions work. Partly because I can't figure out anyway MAX will
> work
> for this problem. I believe I entered it properly because I can see the
> braces.
>
> In your formula, I don't understand what you use H1 for? The student
> names
> are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
> looking for the last cell in the 14:61 range of each column that has
> anything in it. I'll put this formula in row 136 of each column. This
> formula will tell me the date of the last test score in the 14:61 range in
> that column
>
> The test dates are in B14:B61. Only column B has dates in them.
> The test scores are in the next 200 columns in the same rows as the dates.
>
>
> "Don Guillett" wrote:
>
>> This is an ARRAY formula that must be entered using ctrl+shift+enter
>>
>> =MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
>> where h1 has the scorer and col b has the names and col d has the scores
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "fedude" <(E-Mail Removed)> wrote in message
>> news:CF5279A1-9BFD-4D68-A42F-(E-Mail Removed)...
>> > OK, I've read ad nauseam all the posts about finding the last row in a
>> > column
>> > with data. I couldn't find what I need. I have a slightly different
>> > question that I could use some help with.
>> >
>> > Lots of columns. In the "middle" of each column are 48 rows for a test
>> > scores. They are by date with the earliest date starting at row 14 and
>> > the
>> > latest test date at row 61. If a person did not take the test, the
>> > column
>> > is
>> > blank, otherwise their score is in the cell.
>> >
>> > For each of these students, I need to find the date at which they have
>> > the
>> > last score. So I need to search back starting at row 61 to row 14 to
>> > find
>> > the
>> > first non-blank cell and then record the date somehow.
>> >
>> > Is this something I can do with the existing excel formulas? If so I
>> > came
>> > up empty. My thought was to insert a custom function in each column.
>> >
>> > TIA!
>>
>>

=IF(ISNA(MATCH(999999,C14:C61)),"",INDEX($B:$B,MATCH(999999,C14:C61)+13))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You may send your workbook to my address below along with a snippet of
> this page and I'll take a look.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "fedude" <(E-Mail Removed)> wrote in message
> news:78CF3AB1-F2B7-45A4-A5E8-(E-Mail Removed)...
>> Don, you are almost my hero. This formula looks simple, but..... I
>> probably
>> wasn't clear in my description
>>
>> I can't seem to get this to work. Probably because I don't understand
>> how
>> array functions work. Partly because I can't figure out anyway MAX will
>> work
>> for this problem. I believe I entered it properly because I can see the
>> braces.
>>
>> In your formula, I don't understand what you use H1 for? The student
>> names
>> are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
>> looking for the last cell in the 14:61 range of each column that has
>> anything in it. I'll put this formula in row 136 of each column. This
>> formula will tell me the date of the last test score in the 14:61 range
>> in
>> that column
>>
>> The test dates are in B14:B61. Only column B has dates in them.
>> The test scores are in the next 200 columns in the same rows as the
>> dates.
>>
>>
>> "Don Guillett" wrote:
>>
>>> This is an ARRAY formula that must be entered using ctrl+shift+enter
>>>
>>> =MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
>>> where h1 has the scorer and col b has the names and col d has the scores
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "fedude" <(E-Mail Removed)> wrote in message
>>> news:CF5279A1-9BFD-4D68-A42F-(E-Mail Removed)...
>>> > OK, I've read ad nauseam all the posts about finding the last row in a
>>> > column
>>> > with data. I couldn't find what I need. I have a slightly different
>>> > question that I could use some help with.
>>> >
>>> > Lots of columns. In the "middle" of each column are 48 rows for a
>>> > test
>>> > scores. They are by date with the earliest date starting at row 14
>>> > and
>>> > the
>>> > latest test date at row 61. If a person did not take the test, the
>>> > column
>>> > is
>>> > blank, otherwise their score is in the cell.
>>> >
>>> > For each of these students, I need to find the date at which they have
>>> > the
>>> > last score. So I need to search back starting at row 61 to row 14 to
>>> > find
>>> > the
>>> > first non-blank cell and then record the date somehow.
>>> >
>>> > Is this something I can do with the existing excel formulas? If so I
>>> > came
>>> > up empty. My thought was to insert a custom function in each column.
>>> >
>>> > TIA!
>>>
>>>
>

Perfect.... You are my hero!

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"fedude" <(E-Mail Removed)> wrote in message
news:3C590FB9-AEF7-4CA2-A03A-(E-Mail Removed)...
> Perfect.... You are my hero!
>
>