I have 20 columns of data named week 1, week 2, week 3 etc for 12,000 rows
however not every week has a value although each row has at least 1 value in
one of the 20 columns.

I want to create a list in a single column, column 21, based on the most
recent column with a value for each row i.e. if week 20 has a value, I want
that value in my list but if it's blank I want to look in week 19 and take
that value but if it's also blank look in week 18 and take that value and so
on.

I don't know how to do this withoun lots of copying and pasting. I'd
appreciate if anyone can help.

Alan,

Assuming your weeks are in columns A to T put this u=in column U and drag down

=LOOKUP(6.022*10^23,A2:T2)

Mike

"Alan B" wrote:

I have 20 columns of data named week 1, week 2, week 3 etc for 12,000 rows
however not every week has a value although each row has at least 1 value in
one of the 20 columns.

I want to create a list in a single column, column 21, based on the most
recent column with a value for each row i.e. if week 20 has a value, I want
that value in my list but if it's blank I want to look in week 19 and take
that value but if it's also blank look in week 18 and take that value and so
on.

I don't know how to do this withoun lots of copying and pasting. I'd
appreciate if anyone can help.

I seem to have had a brainstorm that should rwad

Assuming your weeks are in columns A to T put this in column U and drag down

=LOOKUP(6.022*10^23,A2:T2)

Mike

"Mike H" wrote:

Alan,

Assuming your weeks are in columns A to T put this u=in column U and drag down

=LOOKUP(6.022*10^23,A2:T2)

Mike

"Alan B" wrote:

I have 20 columns of data named week 1, week 2, week 3 etc for 12,000 rows
however not every week has a value although each row has at least 1 value in
one of the 20 columns.

I want to create a list in a single column, column 21, based on the most
recent column with a value for each row i.e. if week 20 has a value, I want
that value in my list but if it's blank I want to look in week 19 and take
that value but if it's also blank look in week 18 and take that value and so
on.

I don't know how to do this withoun lots of copying and pasting. I'd
appreciate if anyone can help.

hi Alan

In T1
=LOOKUP(99^99,A1:T1)
Copy down as required

--
Regards
Roger Govier

"Alan B" <Alan wrote in message
...
I have 20 columns of data named week 1, week 2, week 3 etc for 12,000 rows
however not every week has a value although each row has at least 1 value
in
one of the 20 columns.

I want to create a list in a single column, column 21, based on the most
recent column with a value for each row i.e. if week 20 has a value, I
want
that value in my list but if it's blank I want to look in week 19 and take
that value but if it's also blank look in week 18 and take that value and
so
on.

I don't know how to do this withoun lots of copying and pasting. I'd
appreciate if anyone can help.