Results 1 to 8 of 8
  1. #1
    New Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift enter):
    =INDEX(A11,MAX(IF(ISBLANK(A11),0,COLUMN(A11))))
    copy it down the rows.
    Expand the column ranges as neccessary.

    Steve

  3. #3
    New Lounger
    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 ctrl-shift enter) part of your help. Can you explain a bit more.

    Thanks again.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter

  6. #6
    3 Star Lounger
    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 array-formula 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

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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))

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    3 Star Lounger
    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 add-in...

    =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
    ' 2002-09-12
    '
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •