Return Unique Records ... No Blanks

[Guest]

Excel2003 ... WS2, Col D ... contains Random (often repeating) text ...

Looking for Formula in WS1 Col C that will return "unique" Values only from
WS2 Col D ... (I want no spaces or empty cells) ... Desire results to be
exactly if I had used Advanced Filter ... & then copy/paste.

I desire to do this by Formula instead of Advanced Filter because my data in
WS2 Col D is constantly changing.

Thanks ... Kha

 

[Tim879]

this technically isn't using a formula, but you could use a pivot
table to find the unique values.

 

[Ragdyer]

Assume WS2 data starts in D1, and may extend down to D200
Assume your unique list to start in WS1, C1.

In WS1, C1 enter:

=WS2!D1

In C2, enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(C$1:C1,WS2!$D$1:$D$200&""),0)),"",INDEX(IF(ISBLANK(WS2!$D$1:$D$200),"",WS2!$D$1:$D$200),MATCH(0,COUNTIF(C$1:C1,WS2!$D$1:$D$200&""),0)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*AFTER* the CSE entry, copy down until you get an #N/A error.
This means that you've exhausted your uniques at this time.

As you add and/or change your data on WS2, those errors will change to
return updated unique entries.

 

[Guest]

Excellent ... I do not know how it works ... but it works. Sorry for delayed
"Reply", but I had a few Vaca days to burn & I am just getting back to this.

Very useful in what I do ... Thanks again ... Kha

 

[Ragdyer]

It appears that you have a rather extended vacation package.
Must be a good industry.<bg>

Appreciate the feed-back.