Finding last appearance of given value
 

Hi, let's suppose we've got following matrix:

A B C D E
1 N Y Y Y
2 N N Y Y
3 N N N Y
4 N N N N


Is there any way to find the row number with given column and taking
into account the last appearance of given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek

Finding last appearance of given value
 

=MAX(IF(D1:D100="Y",ROW(D1:D100)))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200817


"Przemek" wrote:

Hi, let's suppose we've got following matrix:

A B C D E
1 N Y Y Y
2 N N Y Y
3 N N N Y
4 N N N N


Is there any way to find the row number with given column and taking
into account the last appearance of given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek

Finding last appearance of given value
 

You can use this array-entered** formula...

=MAX((D1:D200="Y")*ROW(D1:D200))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

Once entered as described, this formula can be copied across to see the
results for other columns. The maximum row of 200 used in both ranges is for
example purposes... set it to the maximum row number you expect to have data
in.

--
Rick (MVP - Excel)


"Przemek" wrote in message
...
Hi, let's suppose we've got following matrix:

A B C D E
1 N Y Y Y
2 N N Y Y
3 N N N Y
4 N N N N


Is there any way to find the row number with given column and taking
into account the last appearance of given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek

Finding last appearance of given value
 

You beat me by 7 seconds (probably because of all the extra words I
typed).<g

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
=MAX(IF(D1:D100="Y",ROW(D1:D100)))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200817


"Przemek" wrote:

Hi, let's suppose we've got following matrix:

A B C D E
1 N Y Y Y
2 N N Y Y
3 N N N Y
4 N N N N


Is there any way to find the row number with given column and taking
into account the last appearance of given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek

Finding last appearance of given value
 

Thank you very much both of them. It works :)

Przemek

On 1 Gru, 17:58, "Rick Rothstein"
wrote:
You beat me by 7 seconds (probably because of all the extra words I
typed).<g

--
Rick (MVP - Excel)

Finding last appearance of given value
 

Well, I have another problem.

In some cells in D1:D200 I have #number instead of figure. Is there a
way to tell max function to avoid these cells?

Przemek

On 1 Gru, 17:41, "Rick Rothstein"
wrote:
You can use this array-entered** formula...

=MAX((D1:D200="Y")*ROW(D1:D200))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

Once entered as described, this formula can be copied across to see the
results for other columns. The maximum row of 200 used in both ranges is for
example purposes... set it to the maximum row number you expect to have data
in.

--
Rick (MVP - Excel)

"Przemek" wrote in message

...

Hi, let's suppose we've got following matrix:

A * *B * *C * *D * *E
1 * *N * *Y * *Y * *Y
2 * *N * *N * *Y * *Y
3 * *N * *N * *N * *Y
4 * *N * *N * *N * *N

Is there any way to find the row number with given column and taking
into account the last appearance of *given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek

Finding last appearance of given value
 

I'm afraid I don't understand your problem. The formula we gave you is looking for the letter "Y"... if there is a number in any of the cells, it will not match the "Y" and it the formula will not react to it. One thing to reiterate in case you are attributing a problem to the wrong thing... **every** time you edit (touch) the formula (by placing your cursor in the Formula Bar), you must re-commit it using the Ctrl+Shift+Enter keystroke (unless you press the Esc key instead).

--
Rick (MVP - Excel)


"Przemek" wrote in message ...
Well, I have another problem.

In some cells in D1:D200 I have #number instead of figure. Is there a
way to tell max function to avoid these cells?

Przemek

On 1 Gru, 17:41, "Rick Rothstein"
wrote:
You can use this array-entered** formula...

=MAX((D1:D200="Y")*ROW(D1:D200))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

Once entered as described, this formula can be copied across to see the
results for other columns. The maximum row of 200 used in both ranges is for
example purposes... set it to the maximum row number you expect to have data
in.

--
Rick (MVP - Excel)

"Przemek" wrote in message

...

Hi, let's suppose we've got following matrix:

A B C D E
1 N Y Y Y
2 N N Y Y
3 N N N Y
4 N N N N

Is there any way to find the row number with given column and taking
into account the last appearance of given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek