+ Reply to Thread
Results 1 to 6 of 6

Getting array/index formula to ignore blank cells

  1. #1
    Registered User
    Join Date
    03-03-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    3

    Getting array/index formula to ignore blank cells

    Hi,

    I'm using this formula (entered as an array formula) to return unique values from a range in alphabetical order: =INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2, $B$3:$B$11)=0, COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), ""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)).

    The problem is that if there's a blank cell in the range, the formula goes crazy and displays the first (alphabetically speaking) value in all of the rows.

    The problem seems to potentially come from this part: COUNTIF($D$2:D2, $B$3:$B$11). When I use it by itself it returns 0 for all values not found in preceding rows of column D and 1 for all values that are found in precending rows (as it should), but when there's a blank cell in the range it returns values higher than 1 for the instances of the value that would come first in alphabetical order and 0 for all of the other values.

    I figure there's probably a way to add the ifblank function somewhere in there that would work, but I don't know where.

    Any help would be appreciated.

    Simon

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Getting array/index formula to ignore blank cells

    For NEXT time, please remember thta it is MUCH easier for us if you provide a sample sheet to to work with.

    This is the array formula that you need. You can adapt it to the ranges in your sheet.



    =IFERROR(INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,"<"&$A$1:$A$20)+10^10*($A$1:$A$20="")-SUM(COUNTIF($A$1:$A$20,D$1:D1)),0)),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    03-03-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    3

    Re: Getting array/index formula to ignore blank cells

    That works great. Thanks a lot!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Getting array/index formula to ignore blank cells

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    03-03-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    3

    Re: Getting array/index formula to ignore blank cells

    Done. Thanks!

  6. #6
    Registered User
    Join Date
    05-04-2019
    Location
    India
    MS-Off Ver
    OFFICE 365
    Posts
    19

    Re: Getting array/index formula to ignore blank cells

    Hi,Glenn,
    How to solve this with two list data.
    (Extract a unique distinct value with 2 list and sort alphabetically)
    Thank you for your reply.

+ 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. INDEX MATCH to ignore blank cells in array
    By Leaflock in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2019, 06:36 PM
  2. Ignore Blank Cells in an array formula
    By Brycicle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-26-2018, 02:28 AM
  3. INDEX MATCH formula, ignore blank cells and define time frames
    By ChildishAlbino in forum Excel General
    Replies: 2
    Last Post: 02-16-2016, 03:16 PM
  4. Ignore Blank Cells in MINIMUM array formula
    By taniwha in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-04-2013, 09:37 PM
  5. [SOLVED] Getting an array to ignore blank cells
    By Mike001 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-25-2013, 06:46 PM
  6. [SOLVED] Array formula to ignore blank cells
    By trickeyja in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-22-2012, 07:35 PM
  7. Array formula to ignore blank cells
    By bronsonb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2011, 09:14 AM

Tags for this Thread

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