+ Reply to Thread
Results 1 to 8 of 8

Simplify: Copy Column Removing Non-Zero Values (formula approach)

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Simplify: Copy Column Removing Non-Zero Values (formula approach)

    I have a list of names (Col A) each associated with a data value (Col B). The data values are numbers and include both zeros and blanks. My ultimate goal is to use a formula to create a third column (Col D in my example file) that includes only those names with data that is NOT zero or blank.

    My example file offers one such solution, with the the bulk of the work being done by Col C which identifies the rows that contain data values that are non-zero.

    I would like to simplify my array formula, as well as eliminate the helper column (Col C with the identified row numbers).

    Anyone want to take a swing at it? Thanks!

    Note: the part of the array formula ROW(OFFSET($B$6:$B$16,-ROW($B$6)+1,0) is used to refer to the size of the array of Name(s)

    I leveraged my solution from: http://www.excelbanter.com/showthread.php?t=15239

    CopyList.xlsx

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    Array formulae can be expensive on larger lists, especially if they involve volatile functions such as OFFSET().

    Try this workbook, it offers a simpler non-array solution to your problem, but does involve a helper column.
    Helper columns with simple formulae are often more efficient than array solutions.

    P.S. To much beer tonight to get my head around an array solution ... ...
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    hi Filibuster, since we are on leveraging, i shall leverage on the formula u leveraged too. =)
    =INDEX($A$1:$A$16,ROW(INDEX($B$6:$B$16,SMALL(IF($B$6:$B$16<>0,ROW(OFFSET($B$6:$B$16,-ROW($B$6)+1,0))),ROW(1:1)))))

    then u would not need the helper column

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    Thanks for both solutions. Can either of you think of a way to make the formula more portable (e.g. copy/paste) and still have it work? The problem seems to be with references to A1 in Marcol solution, and the whole ROW(OFFEST) part of the array solution). Thanks.

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    which formulas are u usin & where do u wanna paste it to? we can then amend the formula accordingly

  6. #6
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    I am looking at Marcol's solution. If I copy the range A5:E16 to, say G20 - the formulas in the original columns D and E break. I would expect this behavior for the absolute references, so I guess I am wondering if the relative refs could be done in a way that refers to data within the data set range rather than outside of it. I am concerned mainly about long term maintenance of the file - someone may move things around. Its fairly obvious that the abs refs need fixing, but not so with the rel refs.

    Also, I am not quite sure how to put this in words, but the solution needs a reference to the top of a column to make relative moves within the data range. For example in =IF(ROW(A1)>SUMPRODUCT(($B$6:$B$16>0)+($B$6:$B$16<0)),"",INDEX(A:A,SMALL($C$6:$C$16,ROW(A1)))) these would be A1 - which is also one of the refs that breaks things when you copy/paste). I tried to get away from this with within my array formula by using ROW(OFFSET) and using absolute cells within the data set. It works, but I am wondering if there is a simpler way, and one that is more efficient from a spreadsheet calculation resources perspective.

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    Copying and pasting an incrementing formula can't easily be done.

    With the Results Table
    1/. You can drag the table to a new location.
    2/. Copy the table to the same row in a different column, (check that your column references are absolute), then drag it to its' new location.
    3/. Select the table, then use Find/Replace (Ctrl+H) > Replace "=" with something unique, say "|=", Press Replace All
    Copy the result to your new location, then use Find/Replace to replace "|=" with "="

    If you're moving the Source Table, then using Named Ranges in the formulae would be the preferred option.
    Last edited by Marcol; 05-25-2012 at 09:00 PM.

  8. #8
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Simplify: Copy Column Removing Non-Zero Values (formula approach)

    Yes, I see. Thanks again Marcol for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1