Determine last blank cell in row?

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

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

> wrote in message
ups.com...
>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
>

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

"T. Valko" > wrote in message
...
> 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
>
> > wrote in message
> ups.com...
>>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
>>
>
>

Excellent. Thank you.

Can you please explain the formula?

> 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

> wrote in message
oups.com...
> Excellent. Thank you.
>
> Can you please explain the formula?
>
>

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!