I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

Help please.

Hi

One way (as you say the data in row 2 is numeric or blank)
=INDEX(1:1,MATCH(9.99999999999999E+302,2:2))

Regards

Roger Govier


plf100 wrote:
I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

Help please.

Thanks Roger, its exactly what I'm looking for. However, it seems to be
returning the value from the last non-blank cell in the row rather than the
first. Should I be changing the 9.99... value?

Here's an example:

Col A Col B Col C Col D
Row 1 APR MAY JUN
Row 2 1 3 MAY
Row 3 7 JUN
Row 4 34 APR

Kind regards,
Pam


"Roger Govier" wrote:

Hi

One way (as you say the data in row 2 is numeric or blank)
=INDEX(1:1,MATCH(9.99999999999999E+302,2:2))

Regards

Roger Govier


plf100 wrote:
I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

Help please.

Hi Pam

Sorry for the delay, I had to go out for a while.
I didn't read your post properly, and, as you found I gave you the column
for last non-blank cell.

I have been tinkering since, and there may be better solutions, but the
following array formula seems to work for me. Commit with Ctrl+Shift+Enter,
for the initial entry and any subsequent editing, not just Enter. Excel will
insert the curly braces { } around the formula, don't input them yourself.

(=INDEX(1:1,MIN(IF(A2:L2="",COLUMN(A2:L2),"")))}

Regards

Roger Govier


plf100 wrote:
Thanks Roger, its exactly what I'm looking for. However, it seems to be
returning the value from the last non-blank cell in the row rather than the
first. Should I be changing the 9.99... value?

Here's an example:

Col A Col B Col C Col D
Row 1 APR MAY JUN
Row 2 1 3 MAY
Row 3 7 JUN
Row 4 34 APR

Kind regards,
Pam


"Roger Govier" wrote:


Hi

One way (as you say the data in row 2 is numeric or blank)
=INDEX(1:1,MATCH(9.99999999999999E+302,2:2))

Regards

Roger Govier


plf100 wrote:

I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

Help please.

I really appreciate your help Roger and I dont want to be a pest but that
formula seems to identify the first blank cell rather than the first
non-blank cell. Sure its a combination of both that you've provided but
think I'm now too close to it to figure out the right combination!

Many thanks,
Pam


"Roger Govier" wrote:

Hi Pam

Sorry for the delay, I had to go out for a while.
I didn't read your post properly, and, as you found I gave you the column
for last non-blank cell.

I have been tinkering since, and there may be better solutions, but the
following array formula seems to work for me. Commit with Ctrl+Shift+Enter,
for the initial entry and any subsequent editing, not just Enter. Excel will
insert the curly braces { } around the formula, don't input them yourself.

(=INDEX(1:1,MIN(IF(A2:L2="",COLUMN(A2:L2),"")))}

Regards

Roger Govier


plf100 wrote:
Thanks Roger, its exactly what I'm looking for. However, it seems to be
returning the value from the last non-blank cell in the row rather than the
first. Should I be changing the 9.99... value?

Here's an example:

Col A Col B Col C Col D
Row 1 APR MAY JUN
Row 2 1 3 MAY
Row 3 7 JUN
Row 4 34 APR

Kind regards,
Pam


"Roger Govier" wrote:


Hi

One way (as you say the data in row 2 is numeric or blank)
=INDEX(1:1,MATCH(9.99999999999999E+302,2:2))

Regards

Roger Govier


plf100 wrote:

I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

Help please.

Hi Pam

We'll get there in the end!!!
Just change from = to <
(=INDEX(1:1,MIN(IF(A2:L2<"",COLUMN(A2:L2),"")))}
again array entered initially and if edited.


Regards

Roger Govier


plf100 wrote:
I really appreciate your help Roger and I dont want to be a pest but that
formula seems to identify the first blank cell rather than the first
non-blank cell. Sure its a combination of both that you've provided but
think I'm now too close to it to figure out the right combination!

Many thanks,
Pam


"Roger Govier" wrote:


Hi Pam

Sorry for the delay, I had to go out for a while.
I didn't read your post properly, and, as you found I gave you the column
for last non-blank cell.

I have been tinkering since, and there may be better solutions, but the
following array formula seems to work for me. Commit with Ctrl+Shift+Enter,
for the initial entry and any subsequent editing, not just Enter. Excel will
insert the curly braces { } around the formula, don't input them yourself.

(=INDEX(1:1,MIN(IF(A2:L2="",COLUMN(A2:L2),"")) )}

Regards

Roger Govier


plf100 wrote:

Thanks Roger, its exactly what I'm looking for. However, it seems to be
returning the value from the last non-blank cell in the row rather than the
first. Should I be changing the 9.99... value?

Here's an example:

Col A Col B Col C Col D
Row 1 APR MAY JUN
Row 2 1 3 MAY
Row 3 7 JUN
Row 4 34 APR

Kind regards,
Pam


"Roger Govier" wrote:



Hi

One way (as you say the data in row 2 is numeric or blank)
=INDEX(1:1,MATCH(9.99999999999999E+302,2:2))

Regards

Roger Govier


plf100 wrote:


I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

Help please.