Results 1 to 8 of 8

20030822, 13:51 #1
 Join Date
 Jun 2003
 Location
 Detroit, Michigan, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
last column with text copy to next column (2000)
I have huge spreadsheet extracted from MS Project, with multiple columns of data with Task Names. Let's say Columns A,B,C and D contain task info. Some of the columns contain blanks, always starting from D, back to C, to B to A.
I want to populate column E with the last column with data, not blanks, for each row. For example.
If column D contains text, I want column E to equal D.
If D is blank and C contains text, I want column E to equal C.
If D is blank, C is blank and B contains text, I want E to equal B.
If D, C and B is blank and A contains text, I want E to equal A.
Any quick ideas how to accomplish this. I am struggling to write an if statement to accomplish this. Once I get a sample, I can make it work for 'n' number of columns.
Thanks.

20030822, 13:57 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: last column with text copy to next column (2000)
Multiple if statements will ONLY work for 7 columns. This will work for any number:
in E1 enter the ARRAY formula (confirm with ctrlshift enter):
=INDEX(A11,MAX(IF(ISBLANK(A11),0,COLUMN(A11))))
copy it down the rows.
Expand the column ranges as neccessary.
Steve

20030822, 14:17 #3
 Join Date
 Jun 2003
 Location
 Detroit, Michigan, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: last column with text copy to next column (2000)
Thanks for the reply Steve, but I am getting a REF error when I enter the formula.
Maybe I missed something on the (confirm with ctrlshift enter) part of your help. Can you explain a bit more.
Thanks again.

20030822, 14:37 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: last column with text copy to next column (2000)
go to the cell with the formula
Press <F2> [you are in edit mode}
DO NOT PRESS the <enter> key
While simultaneously pressing the <control> and the <shift> keys, press <enter>
Your formula should now have curly brackets ({}) around it.
Steve

20030822, 14:41 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: last column with text copy to next column (2000)
I just thought of something else. Did you set up your code so the 1st columns was NOT column A? This only works starting in A.
A more general one is:
<pre>=INDEX(C1:F1,MAX(IF(ISBLANK(C1:F1),0,COLUMN(C 1:F1)))COLUMN(C1)+1)</pre>
Again confirm with ctrlshiftenter

20030824, 17:34 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: last column with text copy to next column (2000)
The arrayformula Steve suggests capture any last value, not just text.
The following ordinary formula would capture last text value:
=LOOKUP(REPT("z",255),A22)Microsoft MVP  Excel

20030825, 02:28 #7
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,004
 Thanks
 2
 Thanked 405 Times in 334 Posts
Re: last column with text copy to next column (2000)
Hi Aladin,
That works fine if at least one of the columns contains text. If not you get a #N/A error. So, to avoid that:
=IF(ISERROR(LOOKUP(REPT("z",255),A11)),"",LOOKUP(REPT("z",255),A11))
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030825, 07:54 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: last column with text copy to next column (2000)
That would more or less double the formula's execution time. There are other possibilties:
[1]
Accept #N/A as result.
[2]
In E1 enter: =IF(ISNA(F1),"",F1)
In F1 enter: =LOOKUP(REPT("z",255),A11)
[3]
Use Longre's free morefunc.xll addin...
=IF(ISNA(SETV(LOOKUP(REPT("z",255),A11))),"",GETV())
[4]
If you can't use morefunc for some reason, add the following code to your workbook as a module:
Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 20020912
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function
and the formula becomes...
=IF(ISNA(V(LOOKUP(REPT("z",255),A11))),"",V())
All of the above executes faster than the usual
=IF(ISERROR(LOOKUP(...),"",LOOKUP())
=IF(ISNA(LOOKUP(...),"",LOOKUP())
This holds of course for all lookup functions.Microsoft MVP  Excel