How do I select & use contents of first non-blank cell in row?
 

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.

How do I select & use contents of first non-blank cell in row?
 

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.

How do I select & use contents of first non-blank cell in row?
 

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.

How do I select & use contents of first non-blank cell in row?
 

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.

How do I select & use contents of first non-blank cell in row?
 

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.

How do I select & use contents of first non-blank cell in row?
 

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.

How do I select & use contents of first non-blank cell in row?
 

Hurrah - we have lift off! Thanks Roger.

Just to note though, that this only works if the data starts in Column A but
its easy enough to reorder my columns.

Thanks again.
Pam

"Roger Govier" wrote:

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.

How do I select & use contents of first non-blank cell in row?
 

Hi Pam

You can alter the range A2:L2 to whatever you want, but make sure that you
make both ranges the same. I made it that range, because I thought your data
was in columns A to L.
Try making it the whole row
=INDEX($1:$1,MIN(IF(2:2<"",COLUMN(2:2),"")))

Note I have made the Index row absolute with the $ signs, so you can copy
down to find the relevant month for each row of data.

Regards

Roger Govier


plf100 wrote:
Hurrah - we have lift off! Thanks Roger.

Just to note though, that this only works if the data starts in Column A but
its easy enough to reorder my columns.

Thanks again.
Pam

"Roger Govier" wrote:


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.

How do I select & use contents of first non-blank cell in row?
 

Hi Pam

Forget making the range equal to the whole of row 2, row 3 etc., unless the
formula is going below the range you are concerned with.
I did my testing below, the used range, but of course if you put the
formulae to the right of your used range, then using whole rows will give
you a Circular Reference error.

Make the range whatever you wish, not necessarily starting at column A, but
ending before the column in which you place the formula.

Regards

Roger Govier


Roger Govier wrote:
Hi Pam

You can alter the range A2:L2 to whatever you want, but make sure that
you make both ranges the same. I made it that range, because I thought
your data was in columns A to L.
Try making it the whole row
=INDEX($1:$1,MIN(IF(2:2<"",COLUMN(2:2),"")))

Note I have made the Index row absolute with the $ signs, so you can
copy down to find the relevant month for each row of data.

Regards

Roger Govier


plf100 wrote:

Hurrah - we have lift off! Thanks Roger.

Just to note though, that this only works if the data starts in Column
A but its easy enough to reorder my columns.

Thanks again.
Pam

"Roger Govier" wrote:


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.

How do I select & use contents of first non-blank cell in row?
 

Many thanks Roger.

Although I'm now getting my head around such formulae, could you help me
with one final request?

Can I adapt this formula to search for the first non-blank cell containing a
specific phrase?

For example: Can I search for first cell in Row 1 that contains the text
"Gate 1" and return the value "APR"?

Col A Col B Col C
Row 1 APR MAY JUN
Row 2 Gate 1 Gate 3
Row 3 Gate 1

Thanks in advance,
Pam


"Roger Govier" wrote:

Hi Pam

Forget making the range equal to the whole of row 2, row 3 etc., unless the
formula is going below the range you are concerned with.
I did my testing below, the used range, but of course if you put the
formulae to the right of your used range, then using whole rows will give
you a Circular Reference error.

Make the range whatever you wish, not necessarily starting at column A, but
ending before the column in which you place the formula.

Regards

Roger Govier


Roger Govier wrote:
Hi Pam

You can alter the range A2:L2 to whatever you want, but make sure that
you make both ranges the same. I made it that range, because I thought
your data was in columns A to L.
Try making it the whole row
=INDEX($1:$1,MIN(IF(2:2<"",COLUMN(2:2),"")))

Note I have made the Index row absolute with the $ signs, so you can
copy down to find the relevant month for each row of data.

Regards

Roger Govier


plf100 wrote:

Hurrah - we have lift off! Thanks Roger.

Just to note though, that this only works if the data starts in Column
A but its easy enough to reorder my columns.

Thanks again.
Pam

"Roger Govier" wrote:


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.