Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find last match in a column?
How can I find the __last__ match in a column? And how do I get the
value of another cell in the same row? Suppose C1:C6500 contains "random" values, some of which might be duplicates. (By "random", I mean they are not sorted.) I want to find the __last__ occurence of the largest value. And I want to get the value of the corresponding cell in column A. I believe the following should find the __first__ occurence and returns the corresponding column-A value: =index(A1:A6500, match(max(C1:C6500), C1:C6500, 0), 1) I don't see any way to extend the concept so that I find the __last__ match; and I have not found alternate Excel functions to use. If VBA is the only solution, I would appreciate it if someone would offer a good implementation. My VBA skills are poor. Below is my first stab at it, just for grins. PS: Forgive me if this question has been asked and answered recently. I think it has. But I think Google Groups search is screwed up today. It coughs up "matches" that do not match my search criteria. I cannot see the forest for the trees. Function matchlast(v As Variant, r As Range) As Long Dim i As Long ' we should select from last to first cells in range; ' that would avoid searching the entire range every ' time. but I do not remember how to do that. matchlast = 0 i = 0 For Each cell In r i = i + 1 If cell = v Then matchlast = i Next End Function |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find last match in a column?
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find last match in a column?
That was a typo on my part.
Thanks for catching it, RD. Ragdyer wrote: Dave also meant to say that the size of the Column A range should match the size of the Column C range. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... =LOOKUP(2,1/(C1:C99=MAX(C1:C99)),A1:A11) Adjust the range to match, but don't use the whole column (unless you're using xl2007). wrote: How can I find the __last__ match in a column? And how do I get the value of another cell in the same row? Suppose C1:C6500 contains "random" values, some of which might be duplicates. (By "random", I mean they are not sorted.) I want to find the __last__ occurence of the largest value. And I want to get the value of the corresponding cell in column A. I believe the following should find the __first__ occurence and returns the corresponding column-A value: =index(A1:A6500, match(max(C1:C6500), C1:C6500, 0), 1) I don't see any way to extend the concept so that I find the __last__ match; and I have not found alternate Excel functions to use. If VBA is the only solution, I would appreciate it if someone would offer a good implementation. My VBA skills are poor. Below is my first stab at it, just for grins. PS: Forgive me if this question has been asked and answered recently. I think it has. But I think Google Groups search is screwed up today. It coughs up "matches" that do not match my search criteria. I cannot see the forest for the trees. Function matchlast(v As Variant, r As Range) As Long Dim i As Long ' we should select from last to first cells in range; ' that would avoid searching the entire range every ' time. but I do not remember how to do that. matchlast = 0 i = 0 For Each cell In r i = i + 1 If cell = v Then matchlast = i Next End Function -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find last match in a column?
On Oct 11, 6:09*pm, Dave Peterson wrote:
=LOOKUP(2,1/(C1:C99=MAX(C1:C99)),A1:A11) Cute! Thanks. Adjust the range to match, but don't use the whole column (unless you're using xl2007). By "whole column", I presume you mean a range ending with row 65536. If not, please elaborate. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find last match in a column?
I mean this won't work in xl2003 and befo
=LOOKUP(2,1/(C:C=MAX(C:C)),A:A) But this will work: =LOOKUP(2,1/(C2:C65536=MAX(C2:C65536)),A2:A65536) wrote: On Oct 11, 6:09 pm, Dave Peterson wrote: =LOOKUP(2,1/(C1:C99=MAX(C1:C99)),A1:A11) Cute! Thanks. Adjust the range to match, but don't use the whole column (unless you're using xl2007). By "whole column", I presume you mean a range ending with row 65536. If not, please elaborate. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup/match to find column name | Excel Worksheet Functions | |||
find last row value in column when using MATCH to find column | Excel Worksheet Functions | |||
How can find match of one cell from a column of cells? | Excel Discussion (Misc queries) | |||
find LAST match in column | Excel Worksheet Functions | |||
find LAST match in column | Excel Worksheet Functions |