Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-16-2011, 06:37 AM
OTPM OTPM is offline Backwards Searching Using a Formula Windows 7 32bit Backwards Searching Using a Formula Office 2010 32bit
Expert
Backwards Searching Using a Formula
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default Backwards Searching Using a Formula

Hi
I am seaerching for a formula that will reverse search a single column array for the first non blank cell and return the number in the cell to the right of that value.
For example:

A10 contains "Object Number:", B10 contains "2456"

Current Active Cell is A20
Search from A20 backwards to A10 and then when it finds the cell is non-blank then return the value in B10.



Any help would be appreciated.
Tony
Reply With Quote
  #2  
Old 05-16-2011, 06:00 PM
macropod's Avatar
macropod macropod is offline Backwards Searching Using a Formula Windows 7 32bit Backwards Searching Using a Formula Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Tony,

To find the row with the last alphanumeric value in column A, you could use:
=MAX(IF(ISERROR(MATCH("*",A:A,-1)),0,MATCH("*",A:A,-1)),IF(ISERROR(MATCH(1E+306,A:A,1)),0,MATCH(1E+306 ,A:A,1)))
So, to find the row with the last last alphanumeric value in column A before the current row, you could use:
MAX(IF(ISERROR(MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1)),0,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1)),IF(ISERROR(MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1)),0,MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1)))
and to find the corresponding value in column B, you could use:
=INDEX(B:B,MAX(IF(ISERROR(MATCH("*",A1:OFFSET(A1,R OW()-1,0),-1)),0,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1)),IF(ISERROR(MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1)),0,MATCH(1E+306,A1:OFFSET(A1,ROW()-1,0),1))))

The formulae could be made much simpler if the tests were for either numbers or strings, rather than considering both.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-17-2011, 12:33 AM
OTPM OTPM is offline Backwards Searching Using a Formula Windows 7 32bit Backwards Searching Using a Formula Office 2010 32bit
Expert
Backwards Searching Using a Formula
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Paul

Many thanks for your prompt response. The search value will always be a string and the returning value will always be a number.

Thanks

Tony
Reply With Quote
  #4  
Old 05-17-2011, 01:33 AM
macropod's Avatar
macropod macropod is offline Backwards Searching Using a Formula Windows 7 32bit Backwards Searching Using a Formula Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

In that case:
=INDEX(B:B,MATCH("*",A1:OFFSET(A1,ROW()-1,0),-1))
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-17-2011, 07:58 AM
OTPM OTPM is offline Backwards Searching Using a Formula Windows 7 32bit Backwards Searching Using a Formula Office 2010 32bit
Expert
Backwards Searching Using a Formula
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Paul
Your formula finds the first cell that is non blank as I wanted but how do I return the number immediately to the right of that cell?
Thanks
Tony
Reply With Quote
  #6  
Old 05-17-2011, 09:17 AM
OTPM OTPM is offline Backwards Searching Using a Formula Windows 7 32bit Backwards Searching Using a Formula Office 2010 32bit
Expert
Backwards Searching Using a Formula
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by OTPM View Post
Hi Paul
Your formula finds the first cell that is non blank as I wanted but how do I return the number immediately to the right of that cell?
Thanks
Tony
Hi Paul
My mistake. It works perfectly.
Many thanks for your help.
Tony
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Backwards Searching Using a Formula effective searching mar999 Office 1 05-04-2011 04:51 AM
Backwards Searching Using a Formula Searching dates to create lists brianh Excel 2 01-18-2010 01:58 PM
Searching Outlook folders not working cltaylor Outlook 0 08-31-2009 10:28 AM
Backwards Searching Using a Formula Searching another persons contacts Hendo313 Outlook 1 07-10-2009 09:03 AM
Visio 2002 backwards compatible with 2000 franklyorange Visio 0 11-17-2008 11:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft