Find the first or last populated column in a range

[Commish]

I have a range of data, from columns C through Z.
And I have about 100 rows of financial data. The rows represent
projects, and the columns represents periods where costs were
incurred. So, some rows have data from C-Z, and others have data from
H-M.

How can I write a formula in column a1, to tell me which was the first
column to have a value in row 1? And, likewise, I want to know which
column has the last value.

 

[Glenn]

I have a range of data, from columns C through Z.
And I have about 100 rows of financial data. The rows represent
projects, and the columns represents periods where costs were
incurred. So, some rows have data from C-Z, and others have data from
H-M.

How can I write a formula in column a1, to tell me which was the first
column to have a value in row 1? And, likewise, I want to know which
column has the last value.

Array formula in A1 (commit with CTRL+SHIFT+ENTER):

=MIN(IF($C1:V1="","",COLUMN($C1:V1)))

Use MAX() for B1.

 

[Glenn]

I have a range of data, from columns C through Z.
And I have about 100 rows of financial data. The rows represent
projects, and the columns represents periods where costs were
incurred. So, some rows have data from C-Z, and others have data from
H-M.

How can I write a formula in column a1, to tell me which was the first
column to have a value in row 1? And, likewise, I want to know which
column has the last value.

Array formula in A1 (commit with CTRL+SHIFT+ENTER):

=MIN(IF($C1:V1="","",COLUMN($C1:V1)))

Use MAX() for B1.

 

[T. Valko]

What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks?

Exactly what result do you want? The column letter?

 

[T. Valko]

What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks?

Exactly what result do you want? The column letter?

 

[Harlan Grove]

How can I write a formula in column a1, to tell me which was the first
column to have a value in row 1? And, likewise, I want to know which
column has the last value.

Most efficient ways

First column [array formula]: =MATCH(1,1-ISBLANK(C1:Z1),0)
Last column [array formula]: =MATCH(2,1/(1-ISBLANK(C1:Z1)))

If you want to treat cells evaluating to "" the same as blank (empty)
cells,

First column [array formula]: =MATCH(TRUE,C1:Z1<>"",0)
Last column [array formula]: =MATCH(2,1/(C1:Z1<>""))

 

[Harlan Grove]

How can I write a formula in column a1, to tell me which was the first
column to have a value in row 1? And, likewise, I want to know which
column has the last value.

Most efficient ways

First column [array formula]: =MATCH(1,1-ISBLANK(C1:Z1),0)
Last column [array formula]: =MATCH(2,1/(1-ISBLANK(C1:Z1)))

If you want to treat cells evaluating to "" the same as blank (empty)
cells,

First column [array formula]: =MATCH(TRUE,C1:Z1<>"",0)
Last column [array formula]: =MATCH(2,1/(C1:Z1<>""))

 

[Commish]

What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks?

Exactly what result do you want? The column letter?

Biff, the data is either numeric, or empty.

The column letter is fine, but ideally I'd want the value in a
specific row on the answer column.

 

[Commish]

What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks?

Exactly what result do you want? The column letter?

Biff, the data is either numeric, or empty.

The column letter is fine, but ideally I'd want the value in a
specific row on the answer column.

 

[T. Valko]

Ok, in other words, you want to return the corresponding column headers for
the first numeric entry and last numeric entry in a row.

Assume column headers are in the range C1:Z1
Numeric data in the range C2:Z2 (includes empty cells)

For the first numeric entry, array entered** :

=IF(COUNT(C2:Z2),INDEX(C$1:Z$1,MATCH(TRUE,ISNUMBER(C2:Z2),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last numeric entry:

=IF(COUNT(C2:Z2),INDEX(C$1:Z$1,MATCH(1E100,C2:Z2)),"")

If the column headers are dates, format the formula results as Date.

--
Biff
Microsoft Excel MVP

What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks?

Exactly what result do you want? The column letter?

Biff, the data is either numeric, or empty.

The column letter is fine, but ideally I'd want the value in a
specific row on the answer column.

 

[T. Valko]

Ok, in other words, you want to return the corresponding column headers for
the first numeric entry and last numeric entry in a row.

Assume column headers are in the range C1:Z1
Numeric data in the range C2:Z2 (includes empty cells)

For the first numeric entry, array entered** :

=IF(COUNT(C2:Z2),INDEX(C$1:Z$1,MATCH(TRUE,ISNUMBER(C2:Z2),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last numeric entry:

=IF(COUNT(C2:Z2),INDEX(C$1:Z$1,MATCH(1E100,C2:Z2)),"")

If the column headers are dates, format the formula results as Date.

--
Biff
Microsoft Excel MVP

What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks?

Exactly what result do you want? The column letter?

Biff, the data is either numeric, or empty.

The column letter is fine, but ideally I'd want the value in a
specific row on the answer column.

 

[Glenn]

Biff, the data is either numeric, or empty.

The column letter is fine, but ideally I'd want the value in a
specific row on the answer column.

Which row?

 

[Glenn]

Biff, the data is either numeric, or empty.

The column letter is fine, but ideally I'd want the value in a
specific row on the answer column.

Which row?