Ignore Blank Values While Using INDEX

[jokorey]

I have found several answers to this, but I haven't had much luck with most of the solutions I've seen, usually revolving around IFBLANK. I feel like I am probably just not getting it - sorry!

I have this formula:

Please Login or Register  to view this content.

The intent is for it to scan Q2:Q546 to create a unique list in U2:U547.

Q2:Q546 is a work column that pulls text values from other sheets in the workbook via simple cell references.

The problem is, in the reference cells (and thus Q2:Q546), there are a lot of blank cells, and the first blank always inserts a '0' or blank cell into the list, located at the place the first blank cell happens. It would be preferable to not have this blank cell (or, alternatively, just sort the blank cell to the bottom?)

Like I said, my fault, the answer is here on the forums, I am just not grokking it - any help is appreciated as always!

[Tony Valko]

Try this array formula**:

=INDEX($Q$2:$Q$546,MATCH(0,IF($Q$2:$Q$546<>"",COUNTIF($U$1:U1,$Q$2:$Q$546)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[jokorey]

Thank you Tony - that doesn't do what I am looking for, but I think it did help me figure out the question I need to ask!

I've attached an example if anyone would like to see, but I can also explain it much clearer now I think

Since Column Q is made up of referenced values, when there is a blank in the source data, column Q returns a 0 value when it is referenced itself, and it is this 0 value I would like to not show up in the indexed list.

Jokorey EG.xlsx

[Pepe Le Mokko]

The following array formula extracts distinct values and not blanks

Please Login or Register  to view this content.

pS replace the semi-colons with commas if necessary

[jokorey]

Thank you Pepe.

Unfortunately, this didn't work either - example in work attached.

Jokorey EG 2.xlsx

I would be curious if anyone has a solution to this problem for the future, but I am going to mark this as solved, because I just realized the KISS solution in my current situation...put a "0" value in cell U1, which the index checks but isn't in use in any other way. /smackself.

[Tony Valko]

Which sample file more accurately demonstrates what you want to do?

Jokorey EG.xlsx

Jokorey EG 2.xlsx

[jokorey]

Both should be the same, but jokorey EG2 should be a little bit easier to check out.

[Tony Valko]

Ok, can you reference the original data rather than the referenced data?

Or, if using the referenced data, is 0 an otherwise valid result? Maybe we can just exclude 0 entries?

In the sample file there are #N/A errors when all uniques have been extracted. I assume you'll want to "hide" those?

[jokorey]

I can't reference the original data, sadly - it is too scattered through the workbook.

I may have gotten turned around - I thought I was excluding zeros, and it was an odd problem with referencing a null value. I really need to work on my comprehension.

In the actual sheet, I have the formula wrapped in IFERROR.

[AlKey]

Just replace <>"" to <>0 in Tony's formula

[Tony Valko]

Ok, this gets rid of the 0 result:

=IFERROR(INDEX($Q$2:$Q$546,MATCH(0,IF($Q$2:$Q$546<>0,COUNTIF($U$1:U1,$Q$2:$Q$546)),0)),"")

Array entered.