Searching backwards through Excel spreadsheet 3

[SantaMufasa]

Please pardon my dimness if searching backwards through an Excel spreadsheet is an obvious feature that I cannot find for myself. (If that is the case, this will be an easy Purple Star for the first person that can set this dimwit straight.)

I have a 16,000-row spreadsheet with data in chronological order. I need the most recent occurrence of a value for which I am searching.

Can someone please bail me out?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bkpchs237]

SantaMufasa,

Depending on the "order" your data is laid out, try using the Min or Max functions to find your values.

Hope this helps.

 

[SantaMufasa]

Thanks, BK, for your suggestion. My problem is that I am typically searching for a text string that is embedded somewhere in the midst of another text string, which diminishes altogether the utility of the MIN/MAX functions.

By your suggestion, can I infer that Excel cannot search backwards...that there is no tick box somewhere through which I specify "search backwards"? If this is the case, then in my frustration, may I assert that such is tantamount to buying a Ferrari with no reverse gear?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[GlennUK]

Do a Find ( I know it only searches forward ), then close the Find dialog, and press Ctrl-Shift-F4 ( Excel 97 ... don't know about other versions ), and Excel will do a Find Previous, i.e. backwards searching.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Dave - do you need to do this with a formula or is this a manual operation ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

If it's a formula you need, to find the last occurence in a column of a string ( when embedded in a string in a cell ), then something like this will work:

=INDEX(B1:B999,MAX(IF(NOT(ISERROR(FIND("xyz",B1:B999))),ROW($1:$999),0)))

entered using Ctrl-Shift-Enter instead of Enter.

It's case sensitive. Be careful if you adjust the ranges, as the range inside the ROW() function must start at 1 for the indexing to work properly.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

It helps a lot if you can give an example of your data layout.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SantaMufasa]

Ken, here are some sample data and a sample scenario:

This is the occurrence 1 of 'xyz' in my data.
This is the occurrence 2 of 'xyz' in my data.
This is the occurrence 3 of 'xyz' in my data.
This is the occurrence 4 of 'xyz' in my data.

If my cursor's position is currently at the beginning of Line 3 and if I issue my "search backward" command for the string "xyz", Excel should change my focus to the cell on Line 2, not Line 3.

Glenn and Geoff, I am looking for a simple manual operation, as simple as an <alt-EF> ("Edit, Find") is now (which, in Excel, "Searches Forward" from current cursor position). What I want/need is the exact behaviour that you see on this very page (if you are using Internet Browser, for example) when you do an <alt-EF>: you have, as a radio-box option, "Direction: UP / Down" !!! (...with my "screaming emphasis" at Microsoft, not at you good Tek-Tipsters). If MS made this simple functionality for IE, why, in the name of the Queen, can they not provide the same functionality for their flagship spreadsheet!!!

And additionally, Glenn, thank you for taking the time to code your fine "INDEX..." function, but do you not agree that if MS has already coded the functionality for IE, that they could/should provide the functionality in Excel so we do not need to reinvent, and reinvent, and reinvent an existing wheel?

Thanks for your patience while I rant.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Zathras]

Is it not possible to sort the entire spreadsheet in reverse order so that the normal operation of Find will work for you?

You can always return the spreadsheet to the current order when you are done. (It may require insertion of a temporary column to hold sequence numbers if there is not a "natural" way to do the sort.)

 

[AnotherHiggins]

If you want a keyboard shortcut, you could create a small custom VBA module, then assign a shortcut to it.

 Sub SearchBackwards()
mySearchString = InputBox("Enter text for which you wish to search", _
    "Search Previous")
On Error GoTo NotFound
Cells.Find(What:=mySearchString, After:=ActiveCell, LookAt:= _
    xlPart, SearchDirection:=[b]xlPrevious[/b]).Activate
Exit Sub
NotFound:
MsgBox mySearchString & " was not found", vbInformation, "Not Found"
End Sub

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SantaMufasa]

Bravo, Zathras! (If I would have spent less time ranting and more time thinking, perhaps I would have come up with this clever work-around.) Given this silly omission on the part of Microsoft, your solution was simply elegant (because it was elegantly simple). Have a Purple Star and my thanks for your brain power!

Glenn and John, to you two too <grin>, I offer a Purple Kudo for your coding skills. Thanks!

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.