Select number from leftmost column that has one
in [General]
|
Prev: how to combine several files, all with same columns, into one
Next: Shortcut to Excel file doesn't 'work' any more...
From: widman on 28 Apr 2010 18:07 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.
From: Don Guillett on 28 Apr 2010 18:24 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 dguillett(a)gmail.com "widman" <widman(a)discussions.microsoft.com> wrote in message news:82E8CEC8-55D1-4605-B569-6C0D25CB756E(a)microsoft.com... > 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. > >
From: T. Valko on 28 Apr 2010 18:25 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" <widman(a)discussions.microsoft.com> wrote in message news:82E8CEC8-55D1-4605-B569-6C0D25CB756E(a)microsoft.com... > 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. > >
From: widman on 28 Apr 2010 19:54 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" <widman(a)discussions.microsoft.com> wrote in message > news:82E8CEC8-55D1-4605-B569-6C0D25CB756E(a)microsoft.com... > > 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. > > > > > > > . >
From: T. Valko on 28 Apr 2010 21:05
You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "widman" <widman(a)discussions.microsoft.com> wrote in message news:C9E5557C-9E2E-40DD-95C0-F76A8CEF1172(a)microsoft.com... > 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" <widman(a)discussions.microsoft.com> wrote in message >> news:82E8CEC8-55D1-4605-B569-6C0D25CB756E(a)microsoft.com... >> > 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. >> > >> > >> >> >> . >> |