Solved

Identify the lowest (highest row number) non-numerical entry in a range in Excel

Posted on 2012-04-04
6
428 Views
Last Modified: 2012-04-21
Hello,

Suppose that in an Excel spreadsheet, you have one cell containing a formula ("formula cell"), another "reference cell" which needs to be referenced in the formula cell, and any number of additional cells containing values (value cells).  Furthermore, suppose that the reference cell cannot be defined by either a relative or absolute cell address.

In a recent thread:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27608986.html

a solution was provided for how to specify the reference cell when:
• it's column is known
• it lies within a known range within the column
• it is known to be further down (have a greater row number) than any other value cells in the range
• its value is a number

For the range B1:B15, the solution given in that thread is as follows:

=LOOKUP(9.99E+307,B1:B15)  (to find the value of the reference cell)
=MATCH(9.99E+307,B1:B15)  (to find the relative position of the reference cell)

Now instead of a number, suppose the value in the reference cell is a text entry. Is there a way to determine the same information (i.e. the entry and/or relative position of the reference cell) so that it can be included as part of the formula?

For example, suppose the formula cell is B16 and the range B1:B15 includes a combination of numbers, text entries and blank cells as shown in Fig. 1.In this case, the two formulas return the following results:

=LOOKUP(9.99E+307,B1:B15)  Result =  3
=MATCH(9.99E+307,B1:B15)  Result = 9

indicating that cell B12, containing the word "cat," was ignored (which is  not surprising since the formulas use a number for the lookup/match value).

As mentioned in the previous thread, "it's kind of like the formula is looking or scanning up the column to identify the first or closest cell with a value and then basing the result on that value."

Is there a way to do that when the value is not a number?

Thanks
0
Comment
Question by:Steve_Brady
  • 3
  • 2
6 Comments
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 37807619
Yes, you can do that with this formula, just change the $B:$B to the column being examined:

=MAX(ROW($B:$B)*ISNUMBER($B:$B))


for error checking in Excel 2003, use:

=MAX(ROW($B:$B)*IF(ISERROR(ISNUMBER($B:$B)),0,ISNUMBER($B:$B)))

for Excel 2007, use:

=MAX(ROW($B:$B)*IFERROR(ISNUMBER($B:$B),0))

These are all array-entered, use CTRL-ALT-SHIFT to confirm.

Dave
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 37808242
Hello Steve,

For the last number the lookup value used was "bignum", i.e. 9.99E+307, for text you can use "bigtext", a string of 255 "z"s, i.e.

=LOOKUP(REPT("z",255),B1:B15)

and you can do the same with MATCH, i.e.

=MATCH(REPT("z",255),B1:B15)

This ignores numbers, if you want the last non-blank entry of any type you can use this version

=LOOKUP(2,1/(B1:B15<>""),B1:B15)

or with MATCH

=MATCH(2,1/(B1:B15<>""))

the last one, only, needs "array entry" or avoid that with this version

=MATCH(2,INDEX(1/(B1:B15<>""),0))

regards, barry
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37808543
Steve, sorry, for some reason I read the question incorrectly - my posted was based on the last numeric.

Here's the same approach for non-numeric:

Yes, you can do that with this formula, just change the $B:$B to the column being examined:

=MAX(ROW($B:$B)*ISTEXT($B:$B))

The approach is also array-entered, use CTRL-ALT-SHIFT to confirm.

While its an array formula, I find it easier to remember than other methods, though all are valid and probably the non-array version barry provided is more efficient, somehow - I'm sure he'll tell us.

You can use COLUMN(1:1) ' for row1 - to do the same test to find the max column so there are variations on that as well.

Cheers,

Dave
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Steve_Brady
ID: 37817331
Dave & Barry,

Thanks for the great solutions and comments.  FYI, I opted for Barry's (non-array) =LOOKUP() formula:

        =LOOKUP(2,1/(B1:B15<>""),B1:B15)

because using =MATCH() would add additional steps.

Barry, your solutions commonly include these inverse arguments (e.g 1/B1:B15<>"") which leave me totally befuddled — but they always work.  Do you come up with them on your own or are they written down somewhere?  Can I go on Amazon.com and buy a copy of, "Inverse Arguments for Dummies"?  Whenever possible, I much prefer to understand what is going on in a formula rather than just inserting it — but then, the latter still gets the job done.  The other part I still have trouble getting my head around is how a range (versus an individual cell) can be equal to (=) or not equal to (<>) something.  There's no need to address those points here as I already have the solution I asked for in this thread and also because I just started two new threads to ask about each of them:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27665631.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27665633.html

However, I thought the two of you might be interested in seeing the following application of formulas which your solution enabled me to just complete. It's extremely useful for some things I am working on and might have use in other scenarios as well.

Suppose you have an Excel file containing many rows of text for which chapters (Ch) and verses (Vs) are specified as shown here (Fig. 1):1I wanted a way to insert an additional level of rows for various notes or topics of notes which refer to specific portions of the text as shown in Fig. 2.2I also wanted the ability to insert a second similar level (called sub-notes say) which refer to specific notes in the first level (Fig. 3).3With your help, I was able to create formulas which automatically create the headings for these two levels of notes.  In the first (notes) level, the formula adds increments of .01 to the Ch & Vs heading to which it refers, thus creating the capacity to handle up to 99 separate and uniquely-labeled notes.  The second (sub-notes) level formula adds a single letter (from "a" up to "z") to the first level heading it follows and thereby makes available, an additional 26 sub-notes that can be included for each first level note.  In other words, a single verse can have up to 2574 (99 x 26) inserted pieces of uniquely identified information (comments, references, etc.).

The best part about it to me though is that at either level, the headings self-adjust any time rows are inserted, deleted or moved to a new location (i.e. you do not get incorrect cell references or #REF! results in those cases).

Fig. 4 shows were Barry's formula (using offset functions rather than direct cell references to define the ranges) fits in.4Offset functions are used because unlike absolute and relative cell references, offset functions do not carry any cell reference "memory" when moved to a new location.

The file is attached. To observe its function:

        •copy row 31 and insert it anywhere or
        •copy row 32 and insert it under any note or sub-note or
        •move notes and/or sub-notes to a new location
        •delete any note or sub-note.

In all cases, notice how other notes and sub-notes headings change.

Thanks again for your very valuable input.

EE-2012.04-06.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37817925
Steve - there is a good tutorial Contextures did late 2010 called "30 Excel Functions in 30 days" : http://blog.contextures.com/archives/2011/01/02/30-excel-functions-in-30-days-01-exact/

Most of the tricks of the trade can be found there, and they provide great building blocks for developing your own techniques.  Each of us have our "Favorites" - e.g., I almost always use INDEX/MATCH to find the last row or set up a dynamic range, though most tips out there work with Offset.  Since I learned the ANCHOR:INDEX(MATCH) function for a dynamic range as my first lesson in this (actually from matthewspatrick), it stuck with me, and is also not a volatile function (as the most often touted Offset actually is).  I've never found an implementation using Offset that I couldn't do ANCHOR:INDEX/MATCH or INDEX/MATCH:INDEX/MATCH to create a dynamic range with and I can actually remember the formula without having to think.

e.g.,

=OFFSET($A$1,,,1,COUNTA($A:$A)) would be the same as
=$A$1:INDEX($A:$A,MATCH(rept("z",20),$A:$A)

but Offset is a volatile function, so performance may vary depending on how much you use it.  Its your equivalent of me doing array functions because some of them come more quickly to mind...

That's the same with me as opposed to using the LOOKUP(1, 1/whatever) approach.  I haven't used it enough for it to be natural, and its so easy for me to remember the alternatives even if they are array functions - which are NOT awful formulas at all, and depending on what you're doing, efficiency of either may not even be noticeable.  So, use the functions that you can remember, and if the performance is not there, then opt for other approaches.

Nice library of functions you're building there.  I think you should think seriously about chalking up an article on what you've learned so others can benefit!  How about something in the next 90 days?  

;)


Cheers,

Dave
0
 

Author Closing Comment

by:Steve_Brady
ID: 37876551
Thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now