Determine last blank cell in row?

[robotman]

I have a table that has randomly placed data in it. I'm trying to
figure out the last column that has data.

For example (* = blank cells):

Cols A B C D E F G H
RowA: 1 2 1 * * 5 * *
RowB: * * 2 4 * 1 1 *

In Row A, I would want to know Col F had the last datapoint.
In Row B, I would want to know Col G had the last datapoint.

I can't simply Countblanks because the numbers come and go.

Any suggestions?

Thanks.

John

 

[T. Valko]

One way: (assuming you enter the formula on the same row)

=ADDRESS(ROW(),LOOKUP(10^10,A$1:H$1,COLUMN(A1:H1)),4)

Copy down as needed

Biff

 

[T. Valko]

Ooops!

Need to make the row references relative:

=ADDRESS(ROW(),LOOKUP(10^10,A1:H1,COLUMN(A1:H1)),4)

I've been "ooopsing" a lot lately!

Biff

 

[robotman]

Excellent. Thank you.

Can you please explain the formula?

 

[T. Valko]

Can you please explain the formula?

Let's use your sample in this explanation:

Cols A B C D E F G H
RowA: 1 2 1 * * 5 * *

=ADDRESS(ROW(),LOOKUP(10^10,A1:H1,COLUMN(A1:H1)),4)

The ADDRESS function takes these arguments:

=ADDRESS(row_number,column_number,reference_number)

The row_number argument is the ROW() function. Since the ROW() function
doesn't contain any arguments it will reference the row that the formula is
entered on. So, if that formula was entered in cell J1 the row_number would
be 1. If that formula was entered in cell J100 the row_number would be 100.

The column_number argument is LOOKUP(10^10,A1:H1,COLUMN(A1:H1)).

This function has 3 arguments: lookup_value, lookup_vector and
result_vector.

What we need to do is find the last numeric value in the range A1:H1.

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

To ensure that the lookup_value is greater than all the values in the
lookup_vector we use an arbitrary number that is guaranteed to meet this
condition. In this case the lookup_value is 10^10 (10 to the 10th power) or
10,000,000,000.

The *last* value in the lookup_vector that is less than the lookup_value is
the number 5.

So, the function returns the value from the result_vector that corresponds
to the *last* value in the lookup_vector that is less than the lookup_value.
The values in the result_vector are the column numbers: COLUMN(A1:H1). It
would look like this:

....A..B..C...D...E..F..G..H
....1...2...3...4...5...6...7...8 (result_vector)
....1...2...1.............5......... (lookup_vector)

The result of the LOOKUP function is 6.

At this point here's what the ADDRESS formula looks like:

=ADDRESS(1,6,4)

The 4 means to return a relative reference.

So the final result is column 6 row 1 = F1 is the location of the last
number in that range.

Biff

 

[robotman]

Very nice. Thank you for taking the time to explain. I learned a
couple new tricks!

So, the function returns the value from the result_vector that corresponds
to the *last* value in the lookup_vector that is less than the lookup_value.
The values in the result_vector are the column numbers: COLUMN(A1:H1).

I did not know you could specify COLUMN (or non-ranges) for the
results vector. Or is Excel seeing the COLUMN as a "range"? Can you
also specify an array of numbers for the results vector?

I remember seeing VLOOKUP can be used as an array or vector format,
but now that I'm looking in the Excel help file, I'm only seeing
references to the array format. Where does Microsoft document the
vector format?

Thanks again!

 

[T. Valko]

I did not know you could specify COLUMN (or non-ranges) for the

results vector. Or is Excel seeing the COLUMN as a "range"? Can you
also specify an array of numbers for the results vector?

The COLUMN(A1:H1) function *is* evaluating as an *array* of numbers:
{1,2,3,4,5,6,7,8}

Where does Microsoft document the vector format?

Look in Help for the LOOKUP function.

Biff

 

[Ronald Dodge]

Me personally, I generally use the MATCH function instead of the LOOKUP
function for this sort of thing. The MATCH function has the following
arguments:

Lookup_Value (Same as syntax 1 of the LOOKUP function)

Lookup_Vector (Same as syntax 1 of the LOOKUP function)

Match_Type (-1 = smallest value that is greater than or equal to the
Lookup_Value [numbers are in descending order, but returns #N/A! error if no
number is larger or equal to the Lookup_Value], OR 1 = largest value that is
smaller than or equal to the Lookup_Value [numbers are in ascending order,
but returns #N/A! error if no number is smaller or equal to the
Lookup_Value], OR 0 = first item to equal to the Lookup_Value [no number
order necessary, but returns "#N/A!" error if no match is found])

In your case, you would use the value of 1 for the Match_Type argument with
T. Valko's example assuming you use the same lookup value as T. Valko
explained.

The MATCH function automatically returns the Nth number of the item found
within the list as opposed to the LOOKUP function requiring a Result_Vector,
which that also becomes handy in other situations.

I use MATCH, ISERROR (to trap the #N/A error within any of the lookup
functions, which requires the use of the IF function), ADDRESS and INDIRECT
quite a bit in these types of formulas. Sometimes, I'm even using at least
one of the following: ROW, COLUMN, CELL (to get the file name and/or sheet
name), SEARCH, FIND, LEFT, RIGHT, or MID
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[T. Valko]

You can use MATCH but keep in mind that MATCH returns the *relative*
position of the lookup_value in the lookup_array. If you want the absolute
address you can still use MATCH but then you have to calculate for any
offset.

Biff

Me personally, I generally use the MATCH function instead of the LOOKUP
function for this sort of thing. The MATCH function has the following
arguments:

Lookup_Value (Same as syntax 1 of the LOOKUP function)

Lookup_Vector (Same as syntax 1 of the LOOKUP function)

Match_Type (-1 = smallest value that is greater than or equal to the
Lookup_Value [numbers are in descending order, but returns #N/A! error if
no number is larger or equal to the Lookup_Value], OR 1 = largest value
that is smaller than or equal to the Lookup_Value [numbers are in
ascending order, but returns #N/A! error if no number is smaller or equal
to the Lookup_Value], OR 0 = first item to equal to the Lookup_Value [no
number order necessary, but returns "#N/A!" error if no match is found])

In your case, you would use the value of 1 for the Match_Type argument
with T. Valko's example assuming you use the same lookup value as T. Valko
explained.

The MATCH function automatically returns the Nth number of the item found
within the list as opposed to the LOOKUP function requiring a
Result_Vector, which that also becomes handy in other situations.

I use MATCH, ISERROR (to trap the #N/A error within any of the lookup
functions, which requires the use of the IF function), ADDRESS and
INDIRECT quite a bit in these types of formulas. Sometimes, I'm even
using at least one of the following: ROW, COLUMN, CELL (to get the file
name and/or sheet name), SEARCH, FIND, LEFT, RIGHT, or MID
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Very nice. Thank you for taking the time to explain. I learned a
couple new tricks!


I did not know you could specify COLUMN (or non-ranges) for the
results vector. Or is Excel seeing the COLUMN as a "range"? Can you
also specify an array of numbers for the results vector?

I remember seeing VLOOKUP can be used as an array or vector format,
but now that I'm looking in the Excel help file, I'm only seeing
references to the array format. Where does Microsoft document the
vector format?

Thanks again!