Select number from leftmost column that has one
 

On a spreadsheet with 800 rows and 12 columns of data, how can I add a column
that is filled by the leftmost column that has data.
Some rows have data in only one of the 12 columns, some in 8, etc. No matter
how many there are, I only want the leftmost, no matter which column it is.

Select number from leftmost column that has one
 

Try this
sub glco()
dim i as long
for i=1 to Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
cells(i,13).value=cells(i,1).end(xltoright).value
next i
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"widman" wrote in message
...
On a spreadsheet with 800 rows and 12 columns of data, how can I add a
column
that is filled by the leftmost column that has data.
Some rows have data in only one of the 12 columns, some in 8, etc. No
matter
how many there are, I only want the leftmost, no matter which column it
is.

Select number from leftmost column that has one
 

Try this array formula** :

=INDEX(A1:L1,MATCH(TRUE,A1:L1<"",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.

--
Biff
Microsoft Excel MVP


"widman" wrote in message
...
On a spreadsheet with 800 rows and 12 columns of data, how can I add a
column
that is filled by the leftmost column that has data.
Some rows have data in only one of the 12 columns, some in 8, etc. No
matter
how many there are, I only want the leftmost, no matter which column it
is.

Select number from leftmost column that has one
 

Thanks. worked like a charm

"T. Valko" wrote:

Try this array formula** :

=INDEX(A1:L1,MATCH(TRUE,A1:L1<"",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.

--
Biff
Microsoft Excel MVP


"widman" wrote in message
...
On a spreadsheet with 800 rows and 12 columns of data, how can I add a
column
that is filled by the leftmost column that has data.
Some rows have data in only one of the 12 columns, some in 8, etc. No
matter
how many there are, I only want the leftmost, no matter which column it
is.




.

Select number from leftmost column that has one
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"widman" wrote in message
...
Thanks. worked like a charm

"T. Valko" wrote:

Try this array formula** :

=INDEX(A1:L1,MATCH(TRUE,A1:L1<"",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.

--
Biff
Microsoft Excel MVP


"widman" wrote in message
...
On a spreadsheet with 800 rows and 12 columns of data, how can I add a
column
that is filled by the leftmost column that has data.
Some rows have data in only one of the 12 columns, some in 8, etc. No
matter
how many there are, I only want the leftmost, no matter which column it
is.




.