Ignore Blank Values While Using INDEX

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!

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.

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

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

Jokorey EG.xlsx

Jokorey EG 2.xlsx

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?

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

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.