|
|
the Last occurance
Hi everybody,
I need to now how I can get the last Row number of last occurance of an item
in a list, for example:
row item
1 A
2 B
3 D
4 A
I want a formula to return me row number 4 after looking up A.
Any hints,
thanks.
|
|
0
|
|
|
|
Reply
|
 Utf
|
2/18/2010 11:51:01 PM |
|
On Thu, 18 Feb 2010 15:51:01 -0800, peyman <peyman@discussions.microsoft.com>
wrote:
>Hi everybody,
>
>I need to now how I can get the last Row number of last occurance of an item
>in a list, for example:
>
>row item
>1 A
>2 B
>3 D
>4 A
>
>I want a formula to return me row number 4 after looking up A.
>Any hints,
>thanks.
=LOOKUP(2,1/($A$1:$A$65535="A"),ROW($A$1:$A$65535))
--ron
|
|
|
0
|
|
|
|
Reply
|
Ron
|
2/19/2010 12:42:31 AM
|
|
Try this array formula** :
=MAX(IF(A1:A100="A",ROW(A1:A100)))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"peyman" <peyman@discussions.microsoft.com> wrote in message
news:9C7C0254-D278-4D88-AA8B-ECECAFBAF8C4@microsoft.com...
> Hi everybody,
>
> I need to now how I can get the last Row number of last occurance of an
> item
> in a list, for example:
>
> row item
> 1 A
> 2 B
> 3 D
> 4 A
>
> I want a formula to return me row number 4 after looking up A.
> Any hints,
> thanks.
|
|
|
0
|
|
|
|
Reply
|
T
|
2/19/2010 3:08:50 AM
|
|
normal entered
=SUMPRODUCT(MAX(($A$1:$A$1000="A")*ROW($A$1:$A$1000)))
--
Jacob
"peyman" wrote:
> Hi everybody,
>
> I need to now how I can get the last Row number of last occurance of an item
> in a list, for example:
>
> row item
> 1 A
> 2 B
> 3 D
> 4 A
>
> I want a formula to return me row number 4 after looking up A.
> Any hints,
> thanks.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
2/19/2010 8:51:01 AM
|
|
|
3 Replies
587 Views
(page loaded in 0.088 seconds)
Similiar Articles: the Last occurance - microsoft.public.excel.miscHi everybody, I need to now how I can get the last Row number of last occurance of an item in a list, for example: row item 1 ... find the last occurance of a character in a cell - microsoft ...I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, t... Match second occurance of value - microsoft.public.excel.worksheet ...How to reference a spacific cell in multiple saved excel files ... find the last occurance of a character in a cell - microsoft ... How to reference a spacific cell in ... How can I clear old Alerts from Daily "Detailed Network ...On our SBS 2008 Server's Daily Report, our "Other Alerts" Status always equals "Error". This is caused by an old alert, the last occurance of which... Count first occurance of text - microsoft.public.excel.worksheet ...How to count the occurrences of a number or text in a range in Excel This article ... Count Occurrence Of String Within String Using Vba - i want to count the number of ... How do I get the last 7 characters of a no. to another column ...find the last occurance of a character in a cell - microsoft ... How do I get the last 7 characters of a no. to another column ... find the last occurance of a character ... cells of varied length extract last 3 digits - microsoft.public ...the Last occurance - microsoft.public.excel.misc cells of varied length extract last 3 digits - microsoft.public ... Use the formula for retreiving the last three ... Find second to last value in cell - microsoft.public.excel ...Match second occurance of value - microsoft.public.excel.worksheet ... find the last occurance of a character in a cell - microsoft ... Match second occurance of value ... Find xth occurrence of a specifiecd character in a string ...Excel - Find The Last Occurrence Of A Character In A String - I ... Find The Last Occurrence Of A Character In A String ... occurrence of specific text within a longer ... Cut last 5 character - microsoft.public.access.formscoding ...Hi, What function can we cut (trim) last 5 char in a string? Ex: I am a student ... Searching on first 5 characters in a field ... find the last occurance of a ... How To Search for the Last Occurrence of a SubstringThe InStr() function provides a method to search for the first occurrence of one string (substring) inside another string. However, there is no intrinsic ... The HOBT: Finding the Last Occurrence of a Pattern in a StringWhen dealing with string data, one of the most useful Transact-SQL functions you can have in your toolbox is PATINDEX. Aside from it’s many creative uses ... VB String Examples: Find Last Occurance of Substring within a StringFind Last Occurance of Substring within a String. Find the starting position of the last occurance of a substring within a string. (With strings, the first character ... Excel - Finding The First And Last Occurance Of A Value - Hello Im ...Finding The First And Last Occurance Of A Value - Hello Im trying to find the value of the first and last... - Free Excel Help Excel - Find The Last Occurrence Of A Character In A String - I ...Find The Last Occurrence Of A Character In A String - I would like to find the position of the last occurrence... - Free Excel Help 7/19/2012 4:49:45 PM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
66 followers. 