+ Reply to Thread
Results 1 to 5 of 5

Formula to return items from a list with a value greater than 0 and ignore blanks

  1. 03-26-2018, 10:15 AM #1
    ZEROCOOL UK
    ZEROCOOL UK is offline
    Registered User
    Join Date
    08-25-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    36

    Formula to return items from a list with a value greater than 0 and ignore blanks

    Hi People

    I need some help with what will no doubt be a simple fix, but its not coming to me.

    In the sheet I have uploaded, in column B there are a list of fictional names, and in Column C there is a list of values between 1 to 10.

    I need a formula that will look up the list, and return all values greater than 0 and ignore the blanks.

    I have manually written what is would ideally look like in column E & F.. In reality the list of names will be in the hundreds and used daily.

    I cannot apply a filter to the data and sort it that way as in reality they data is pulled using a formula and changing the order breaks the rest of the process.

    Thanks in Advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-26-2018, 10:21 AM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to return items from a list with a value greater than 0 and ignore blanks

    Enter array formula in E3 and copy across and down

    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    You will know the array is active when you see curly braces { } appear around your formula.
    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 03-26-2018, 10:21 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,426

    Re: Formula to return items from a list with a value greater than 0 and ignore blanks

    In E3:

    =IFERROR(INDEX(B:B,SMALL(IF($C$3:$C$15>0,ROW($B$3:$B$15)),ROWS($A$1:A1))),"")

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 03-26-2018, 10:35 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,426

    Re: Formula to return items from a list with a value greater than 0 and ignore blanks

    Thanks for the rep!
    Reply With QuoteRegister To Reply
  5. 03-26-2018, 11:13 AM #5
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,297

    Re: Formula to return items from a list with a value greater than 0 and ignore blanks

    For MS-Off Ver: 2016, this formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return last numeric value in a row (greater than zero; ignore errors)
    By Luis Ah-Hoy Jr. in forum Excel General
    Replies: 7
    Last Post: 07-02-2018, 03:52 PM
  2. Excel Formula To Return Values in List without Blanks
    By bjohnsman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2016, 02:22 PM
  3. Search Through a Column to Find Match, ignore blanks return Value
    By lmc2016 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2016, 11:39 AM
  4. Formula to return unique value and ignore blanks..
    By tangmere.milli in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2015, 10:45 AM
  5. How to skip blanks in col B to return list items from col A (Example Attached)
    By PGOIBOY in forum Excel General
    Replies: 2
    Last Post: 11-03-2012, 02:53 PM
  6. Ignore blanks when sorting a list
    By JamesT1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2010, 09:29 AM
  7. Validation List ignore blanks (formula)
    By portmontreal in forum Excel General
    Replies: 2
    Last Post: 07-16-2009, 02:52 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1