Match: Find last matching row

Hi,

I have one column sorted where are different values. In order to fin
the row of matching value of my interest I use match function that i
perfect here. Almost perfect....

The trouble is match gives me first row where it accounters give
value. Unfortunately I need to find the smallest number, so I may no
say what exactly number I'm searching for and as a result use match. 

I need to add that there are some strings and numbers in the column, s
when I sort it strings are separated from values and values are sorte
as asked. So if I knew function that wil
'match_the_last_string_value_in _the_column' that would solve th
issue.


Thanks,


Michal Bobrowsk

--
Message posted from http://www.ExcelForum.com

0
8/29/2004 6:25:07 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
539 Views

Similar Articles

[PageSpeed] 17

Hi
so you're searching for the smallest string? Could you post some
example data (plain text - no attachments please)

--
Regards
Frank Kabel
Frankfurt, Germany

"mbobro >" <<mbobro.1bqljt@excelforum-nospam.com> schrieb im
Newsbeitrag news:mbobro.1bqljt@excelforum-nospam.com...
> Hi,
>
> I have one column sorted where are different values. In order to find
> the row of matching value of my interest I use match function that is
> perfect here. Almost perfect....
>
> The trouble is match gives me first row where it accounters given
> value. Unfortunately I need to find the smallest number, so I may not
> say what exactly number I'm searching for and as a result use match.
>
> I need to add that there are some strings and numbers in the column,
so
> when I sort it strings are separated from values and values are
sorted
> as asked. So if I knew function that will
> 'match_the_last_string_value_in _the_column' that would solve the
> issue.
>
>
> Thanks,
>
>
> Michal Bobrowski
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

0
frank.kabel (11126)
8/29/2004 6:41:10 AM
Hi!

So, you want to find the position of the last string in 
the column?

=MATCH(REPT("Z",255),A:A)

Biff

>-----Original Message-----
>Hi,
>
>I have one column sorted where are different values. In 
order to find
>the row of matching value of my interest I use match 
function that is
>perfect here. Almost perfect....
>
>The trouble is match gives me first row where it 
accounters given
>value. Unfortunately I need to find the smallest number, 
so I may not
>say what exactly number I'm searching for and as a result 
use match. 
>
>I need to add that there are some strings and numbers in 
the column, so
>when I sort it strings are separated from values and 
values are sorted
>as asked. So if I knew function that will
>'match_the_last_string_value_in _the_column' that would 
solve the
>issue.
>
>
>Thanks,
>
>
>Michal Bobrowski
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
biffinpitt (3172)
8/29/2004 7:05:13 AM
Reply:

Similar Artilces:

Multiple matches using LOOKUP
Hello everyone, Here is what I am trying to accomplish. Based on Column A or ORDER_NOTE I need to find the respective match in column C. It perfectly works for 1 match. What if I have three or more matches how to force excel to look for others? This formula is inside of macro. I can use count function to find out how many times 'ORDER_NOTE' appeared. ActiveCell.FormulaR1C1 = "=LOOKUP(""ORDER_NOTE"",Range(""A:A""),Range(""C:C""))" Also, is it possible to concatenate all the finding of column C that match 'ORDE...

Hide multiple rows when particular cell is zero
Hi All, Can anyone tell me how I can hide multiple rows when a particular cell is zero? It has to go automaticly, so no buttons... When D37=0, hide rows 37-48 When D49=0, hide rows 49-58 When D59=0, hide rows 59-68 When D69=0, hide rows 69-78 When D79=0, hide rows 79-88 etcetera. How can help me with this macro?? Thanks in advanced. Best regards, BL Hi, What should happen if e.g. D38 = 0? Maybe this applied to the desired sheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a, rng As Range Set rng = Range("D37:D1000") For Each a In rng ...

Sorting blank rows in Excel
Hello, I have a worksheet that exists of the following: Student ID Last Name First Name 1234567 Doe John x x x x x The x's above indicate blank rows - this is set up for multiple students. When I do a sort, the blank rows are ignored - how can I include the blank rows for each student during a sort? Thanks Keith >-----Original Message----- >Hello, > >I have a worksheet that exists of the following: > >Student ID Last Name First Name >1234567 Doe John >x >x >x >x >x > > >The x's ...

Matching downloaded transactions #2
I am new to money 2004 and to me its not extremely clear which transaction money is auto matching and what its matching it to. It usually displays 3 transactions one of which has an exclaimation point. I know it should be simple to understand but in talking with other user's there seems to be a bit of uncertainty. On 2005-03-24, anti updates <anti> wrote: > I am new to money 2004 and to me its not extremely clear which transaction > money is auto matching and what its matching it to. It usually displays 3 > transactions one of which has an exclaimation point. I know ...

how do i merge 2 rows of data into one
I am trying to combine data from 2 rows so that i can use the data as one row which will enable me to sort the data accordingly. Hi you may provide some more details how your data is structured and how you want to combine it -- Regards Frank Kabel Frankfurt, Germany "willid" <willid@discussions.microsoft.com> schrieb im Newsbeitrag news:14D9237F-69DA-4696-AC15-38585C65B28E@microsoft.com... > I am trying to combine data from 2 rows so that i can use the data as one row > which will enable me to sort the data accordingly. You can't merge data without losing parts ...

Sharing a personal view built with Advanced Find
Is is possible to share a personal view built with Advanced Find in CRM 4.0 -- Samyra Absolutely. Assuming you have already saved the view, if you look at the top of the Advanced Find window, you will see two tabs (1)Find and (2)Saved Views. Click on the Saved Views tab to see a list of all the views you have created. Then, highlight the view you want to share and click More Actions / Sharing from the menu above the grid. You can then select the individuals with whom you wish to share and the security privileges you wish to allow. Hope this helps, Mark "Samyra" wrote: ...

Couldn't find my .aspx file
Hi There, I have developed a .aspx page in sharepoint designer which has link like this:http://ServerName:8080/AccessForms/Entry_Form.aspx I have access to server(ServerName) but I am not able to find this file (Entry_Form.aspx) anywhere on 'ServerName'. I have gone through each and every directory on server but couldn't find it. Your help will be highly appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice No-SOAP, No-Webservice WSE Xml Signature http://www.eggheadcafe.com/tutorials/aspnet/fb38417b-77c0-4f9b-901a-bc2341bc3a08/nosoap-nowebser...

Use VBA to duplicate a row structure/formulas?
Hi all, I'm looking for an automated way to duplicate the last row in a worksheet as soon as it has been edited. I need the new row to be empty but maintain the formulas of the previous row. Is this possible please? I'm working on a continuous form for other users so there's no way of knowing how many rows will be needed in advance. I've never done anything like this in excel before so I'm not even sure if it's possible! Thanks a lot, Ciar=E1n I do something similar in a "Logbook" file I made for our shop. What I did was create the formulas and formatting ...

Separating First Name(s) and Last Name into Separate Columns
I have a spreadsheet with one column ("name") that contains names in the following formats occuring randomly down the list: Firstname Lastname Firstname & Firstname Lastname Firstname Lastname & Firstname Lastname I need to get them all into two new columns "firstname" and "lastname" and I'm having difficulties writing a formula to account for all 3 scenarios in one cell. (If I have to do it in multiple steps - so be it!) For the 1st example, the contents of the new "firstname" column should be Firstname, while the "lastname"...

Maintaining Formula after adding a new row
I have a formula that adds data in a column. =SUM(A5:A10). The proble is that every time I copy a new row and insert it at row 5, my formul changes to =SUM(*A6*:A10). I just want it to stay the same. I'v tried using an absolute value $A$5, but it still changes when a new ro is added. I've tried using a named reference as well as locking, but m merged cells caused some problems with the lock -- shaugh ----------------------------------------------------------------------- shaught's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3080 View this thread: ...

64K (65,536) row maximum on worksheet
Does anyone know of a way to increase the maximum number of rows (65,536) on a worksheet ? I maintain an application which queries a shipments database which grows beyond this limit late in the year. Splitting the data between two worksheets is an obvious option, but complicates the production of numerous reports and pivot tables. Or I can redo the entire application in Access, but that would involve considerable effort and retraining of users. Is there a third-party add-in which can be purchased ? Any suggestions would be greatly appreciated. Thanks, Chris Curry The only way I k...

Picking matching value from a list
Suppose I have a pair of columns giving nicknames and full names, e.g. John John Q. Public Bob Robert F. Kennedy Marty Martin Luther King Abe Abraham Lincoln Dick Richard M. Nixon Is there a worksheet function which will accept the value of a nickname from one cell along with the location of the above table, and return the name from the second column corresponding to the matching nickname in the first column? E.g. I type "Abe" and the function returns "Abraham Lincoln"? Seems like there should be, but I can't find it. - Rich Have a ...

Finding an element by name in a schema
Is there an easy way to find an element by name given a XmlSchema object? Or do I have to parse the entire schema myself to search for it? Do I somehow need to use XmlSchemaXPath? If so, how? Thanks, -- Greg Maybe I need to be clearer here. Here is a sample schema fragment: <?xml version="1.0" encoding="utf-16"?> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="t...

Excel should accomodate more number of rows than 65536, to load l.
It would be good to make excel sheet load more than 65536 rows. This is a common requirement for Data conversion/ Data Loading process. And it what databases handle very easily. -- HTH RP (remove nothere from the email address if mailing direct) "Laxman Charipally" <Laxman Charipally@discussions.microsoft.com> wrote in message news:8D26B56B-F22B-4E75-B491-1CDBFE2431F8@microsoft.com... > It would be good to make excel sheet load more than 65536 rows. > This is a common requirement for Data conversion/ Data Loading process. > ...

How can I remove an unused shortcut label from a Worksheet row?
I put a macro shortcut on Row 1 of a worksheet using Forms controls. Now I no longer need this shortcut; I've deleted the associated macro and am now left only with a labelled box which I cannot remove using the Help suggestions. -- tanda You should be able to delete it. Press the F5 key, click on Special, select Objects click on OK It should now be selected - press the Delete key to get rid of it. "tanda" wrote: > I put a macro shortcut on Row 1 of a worksheet using Forms controls. Now I > no longer need this shortcut; I've deleted the associated macro and...

Find a Case by ID
I know this seems simple but how the heck can you find a case by ID. As a manager I dont know details. I have staff coming to me with case numbers from CRM. Using the Advanced find window seems like the wrong way to do it but I cant seem to find any other way. The Find Cases box at the top wont work. Is this really a result of poor design or just something I can find? unfortunately, the advanced find is the only way to find by case #. alternatively, you can always sort the case grid by the case # column and find it quickly. The Find Cases box actually seraches the Title of the cas...

Find & Replace All
Hi Query regarding Word 2003: I have a number of documents to format, required to change all AAl2O3 to have the 2 & 3 as subscript to show as chemical formula for the mineral. I've set up autocorrect within my templates, so it does it when I type.... however working for a large corporation I'm unable to change the normal.dot template, therefore get the document back to re-format! I've tried to find & replace the word however within the advance options you can only select the whole word to be subscript not individual characters.... Any help would be ...

special type of rows to columns procedure
This is how i have the data column1 column2 column3 column4 row1 1 2 3 4 row2 5 6 7 8 row3 9 10 11 12 And this how i would like to transform it with the most automated way i can. column1 column2 column3 column4 row1 1 row2 2 row3 3 row4 4 row5 5 6 7 8 row6 9 10 11 12 Which means that i'd like Excel to insert new rows in order to paste...

Deletion of rows based on Conditions
Hi, This is a sample of the data i am working on: 2012088552 300 04/27/2007 16:53:14 2012091284 300 04/28/2007 19:41:34 2012421319 300 04/25/2007 11:52:00 2012817722 300 04/28/2007 12:49:36 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 I want to keep all data where the date is 04/27/2007 and delete all the other rows EXCEPT the ones that have the same value in the first column as the value where the date is 04/27/2007. This means the result would be: 2012088552 300 04/27/2007 16:53:14 2013558438 300 04/25/2007 12:15:59 2013558438 300 04/27/2007 17:06:59 Any help...

Can't find e-mail address in my organization
I need to add an e-mail address in my Exchange 2003 server. When I add it to a user, it says "This e-mail address already exists in this organization. ID no: c10312e7 Microsoft Active Directory - Exchange Extension". I looked at all users but did not see it any user profile. Is there a way to search for the user that has this address assigned to it? we have just answered same question, Where is this email address posted by Jamie Dawes (-: Just to make it easier for you, here are the steps for locating an smtp address which is being used already go to ADUC Click on Find ob...

How to Delete Rows based on conditions?
Dear All Happy Mothers' Day!!! I would like to ask how to write vb code to delete rows based o conditions.. I have 4 columns of data and I would like to delete all data in colum B if they are not specified "Unused" and I also want to delete al those in Column C that are "0" in value. In a nutshell, I only want data that are "Unused" and not equal to 0 Could someone kindly show me how to do it...? Cheer -- Message posted from http://www.ExcelForum.com The trick is to work from the bottom up. Here is one that deletes rows with a date. Sub deltetedate...

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

Lookup based on two criteria in 1 row
Hi all, In looking thru the help, I didn't find anything that looked quite like my issue, though there were some similar. I have a table with 2 criteria in the same row, and I am attempting to essentially graph my answers on a separate sheet. Data: Group # Option # On/Off (8) 1 On (8) 2 Off (14) 1 On (14) 2 On Ideal results: " (8) (14) 1 On On 2 Off On My actual data is obviously a much larger table, and I need this to be something I can view all at once to print, etc. Normally, I would do a vlookup to get myself one set of data on this, and I could manually run a vlooku...

Lookup/Index/Match does not work with Numbers
Hello, I have a few master reference tables that I use to complete entries on another worksheet that basically compiles all of the reference data for a given set of key values. One of my reference tables has codes that are either numeric, alphanumeric, or alphabetic. Associated with each code are several other values that I want to look up and report in the compiling spreadsheet. Entering either the alphanumeric or the alphabetic results in the additional values being references and entered properly. However, when I try to enter any of the numeric codes, I get #N/A. The cell format does ...

Contact Name First to last
whenever I create a contact information the name appearing the fileas box as"last name to first", is there a permanent way to keep the file as is exactly as full name? Dear, Whynot - In OL XP and 2003 Go to Tools -Email Accounts-click on View or change existing directories or address book(next), click on change, Select show names by File As. (Please respond to this question Was this post helpful to you?) Hope this helps you more Regards APK "Alexbj" wrote: > whenever I create a contact information the name appearing the fileas box > as"last name to f...