0

I am creating a template that users will input a case number in column A and that case number can be repeated. I have a formula which works great to pull a unique list of case numbers but for blank cells it returns a zero at the end of the list.

How can I make it not do that? 

{=IFERROR(INDEX($A$5:$A$30, MATCH(0,COUNTIF($G$37:G42, $A$5:$A$30), 0)),"")}
Improve this question
5
  • The problem, as you probably understand,  is that INDEX( array,  coord,  coord ) displays a 0 when the indexed value is blank.
    – Scott - Слава Україні
    Apr 20, 2018 at 22:04
  • 1
    Possible duplicate of Display Blank when Referencing Blank Cell in Excel
    – Scott - Слава Україні
    Apr 20, 2018 at 22:05
  • I found that string and tried adding several of the options to the formula but couldn't get it to take. I am not really the most advanced in this type of stuff. I can't turn off the zeros because the top part of the worksheet is accounting and does have zeros. I will try some of the options again and see if I can get them to work.
    – k. knight
    Apr 20, 2018 at 22:13
  • @k.knight, please write what you want to replace to Zero, Blank or any message?
    – Rajesh Sinha
    Apr 21, 2018 at 8:24
  • The ultimate cause of the problem is likely rooted in the issue in the suggested duplicate, but that, alone, isn't a solution to the problem here; just a hint. Posting only a formula, without the cell it is in or where the range started, makes this impossible to answer, we can only speculate. Varying the starting point changes the formula's behavior. We can't reliably replicate the problem without more information.
    – fixer1234
    Apr 24, 2018 at 17:48

1 Answer 1

Reset to default
-2

You can use this Formula in Cell B2, which will create a unique list of the case numbers and also ignore errors.

{=IFERROR(INDEX($A$2:$A$9, MATCH(0,COUNTIF($B$1:$B1, $A$2:$A$9), 0)),"")}

Enter this formula with Ctrl+Shift+Enter.

This Screen shots shows the results along with the formula been used:

N.B. The Formula has been filled from B2 to B9, note that Column B doesn't contains any zero wherever there is a blank Cell between A2 to A9.

enter image description here

Improve this answer
5
  • You have posted exactly the same formula as the OP, with only the ranges changed. That will definitely not solve his problem.
    – Bandersnatch
    Apr 22, 2018 at 13:12
  • I've executed the Formula & it's working exactly what OP demands,,, If OP write I include the Screen shot, the correction is Countif($B$1:$B1,, OP written COUNTIF($G$37:G42, which is the bug in the Formula.
    – Rajesh Sinha
    Apr 23, 2018 at 8:34
  • Now I've include the Screen Shot which comprises the same set of data what has been used by you, and you can find the Formula also I've used in B2 and its working,, & not producing any zero.
    – Rajesh Sinha
    Apr 24, 2018 at 6:04
  • 1
    @Bandersnatch, this is very unfortunate that you have included unexpected sentences to my answer while lastt editing. you know better such kind of editing is unethical as well as superuser doesn't allows to do so !!
    – Rajesh Sinha
    Apr 24, 2018 at 6:11
  • 1
    Changing $G$37:G42 to $B$1:$B1... isn't the solution to the OP's problem, that's a different problem; that, alone, doesn't explain anything. We can't tell from the OP's single formula what he did or where he started. If he started the results range reference in the starting cell, that could be the cause of the zeros. So it's a bad question that can't accurately be answered based on the information provided. The fact that you produced a similar looking problem that doesn't produce zeros isn't really an answer to the OP's problem since it doesn't tell the OP how to fix his own problem.
    – fixer1234
    Apr 24, 2018 at 17:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .