+ Reply to Thread
Results 1 to 9 of 9

Making a second column return the values from column A but ignore 0's and blank spaces

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Making a second column return the values from column A but ignore 0's and blank spaces

    Hi guys,


    Trying to figure out if there's a formula involved or just a column setting.

    See the link - http://i.imgur.com/xmC5Tgz.jpg?1

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Hi,

    Any chance of an actual workbook instead of just a picture?

    Cheers
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Hi and we3lcome to the forum

    Please upload your sample workbook here. Not all members are able (company firewalls and stuff) or willing to download from file hosting sites
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Maybe something like this...


    Data Range
    A
    B
    1
    Header1
    Header2
    2
    5
    5
    3
    63
    5
    4
    0
    15
    5
    15
    15
    6
    0
    15
    7
    0
    25
    8
    0
    53
    9
    61
    61
    10
    70
    63
    11
    70
    12
    5
    81
    13
    25
    85
    14
    15
    85
    16
    53
    17
    0
    18
    81
    19
    15
    20
    15


    This formula entered in B2:

    =IFERROR(SMALL(A$2:A$20,COUNTIF(A$2:A$20,0)+ROWS(B$2:B2)),"")

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Maybe this is what you want. The list just eliminates the 0 and blank values.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    02-13-2014
    Location
    Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    0

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Quote Originally Posted by newdoverman View Post
    Maybe this is what you want. The list just eliminates the 0 and blank values.
    I think it's just the same as using "vlookup"?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-22-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Hi guys,

    Sorry I wasn't able to post the workbook. The link didn't work.

    I will try out your suggestions.

    Thanks!

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    @ ellednarb666

    Did you look at column A to see how the values were obtained? Those values were not manually inserted.

    The problem is more than just VLOOKUP.

  9. #9
    Registered User
    Join Date
    08-22-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    Hey Tony,

    This worked perfectly. Thank you!

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Making a second column return the values from column A but ignore 0's and blank spaces

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Making values in one column repeat every 12th row in a different column
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2013, 06:45 PM
  2. Cateorgise data - If word is in column H, return set value in blank column
    By cork_girl in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-06-2013, 10:38 AM
  3. Formula to Return non-blank values from a column
    By adamb22 in forum Excel General
    Replies: 4
    Last Post: 05-23-2012, 03:15 PM
  4. Replies: 6
    Last Post: 12-11-2008, 02:58 PM
  5. Replies: 3
    Last Post: 01-10-2006, 06:10 AM

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