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

[Guest]

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.

 

[Roger Govier]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Roger Govier]

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

 

[Guest]

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