The row number of the last cell containing a particular item

[Michael6]

Different items are repeatedly entered in a column. A formula is required to workout the row number of the cell cotaining the last entry of a particular item.
Please see attached sample worksheet.
Thanks in advance.

[tigeravatar]

Michael6,

The following formula is an array formula, and so needs to be entered with CTRL+SHIFT+ENTER:
=MAX(IF(A:A="B",ROW(A:A),0))

Hope that helps,
~tigeravatar

[Mordred]

Hi Michael, try this the uploaded workbook

[Mordred]

You beat me to it Tiger!! I did mine with code though because I am not fluent with worksheet functions.

[tigeravatar]

@Mordred, hehe, sorry about that I was just looking at your code. You're definitely improving

[Mordred]

Thanks for that tiger although I did trap an error if there are no "B"s is the cells and had to change my code to this:

Please Login or Register  to view this content.

[Michael6]

Thank you tigeravatar, but this formula gives me the error #NUM!
And apart from that,for some reason, I would rather use a nonarray formula if possible.
Your help is appreciated

[tigeravatar]

Michael6,

The #NUM error is because I was using excel 2007 and referred to an entire column. I have changed the formula. Its still an array formula so need to use CTRL+SHIFT+ENTER:
=MAX(IF(TRIM($A$1:$A$10000)="B",ROW($A$1:$A$10000),0))


Attached is a modified version of your sample workbook so you can see how it works

Hope that helps,
~tigeravatar

[daddylonglegs]

Another way is an array formula like this

=MATCH(2,1/(A1:A20="B"))

or to make it non-array, with added INDEX

=MATCH(2,INDEX(1/(A1:A20="B"),0))

[Michael6]

Hi Mordred,
Thanks for your trying to help, but I am absolutely unfamiliar with this sort of formula, and I don't think I will be able to fit it in a bigger formula in my original Spreadsheet.

[daddylonglegs]

See my post for a non-array version....

....but I note you talk about a "bigger formula". What's the ultimate aim here - if you are finding the row number to use in a larger formula then there are probably better ways to achieve the final result

[Michael6]

Thank you daddylonglegs.Your formula is what I actually asked for.But it will be great of you if you can modify it by changing the range [$A$1:$A$20] to the range[ $A$1: WEEKNUM(TODAY()).] I mean that the end of the range has to be the cell of the row number that is the number of today's week.The week starts MONDAY.

[Michael6]

Thank you tigeravatar. This modified formula works OK. But the nonarray formula given by daddylonglegs is what I am actually after.
Regards
Michael