Find the first or last populated column in a range
 

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.

Find the first or last populated column in a range
 

Commish wrote:
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.

Find the first or last populated column in a range
 

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
Microsoft Excel MVP


"Commish" wrote in message
...
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.

Find the first or last populated column in a range
 

Commish wrote...
....
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<""))

Find the first or last populated column in a range
 

On May 13, 4:42*pm, "T. Valko" wrote:
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
Microsoft Excel MVP

"Commish" wrote in message

...

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.

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.

Find the first or last populated column in a range
 

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


"Commish" wrote in message
...
On May 13, 4:42 pm, "T. Valko" wrote:
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
Microsoft Excel MVP

"Commish" wrote in message

...

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.

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.

Find the first or last populated column in a range
 

"Commish" wrote in message

...

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.


On May 13, 4:42 pm, "T. Valko" wrote:
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
Microsoft Excel MVP


Commish wrote:
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?