Re : Excel Experts E-letter (No. 19, Re-Visit) ; POWER FORMULA
TECHNIQUES

by Leo Heuser.

A. How can you get a list of unique entries in an n * m array by using
a worksheet formula?

B. Example:

C. Assuming data in B5:GR10, enter this array formula in e.g. G12. G11
must be empty or, if it has a value, this value must not occur in
B5:GR10.

D. =OFFSET($B$5,
MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,ROW($B$5:$ GR$10)-ROW($B$5))),
MOD(
MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,
ROW($B$5:$GR$10)-ROW($B$5)+(COLUMN($B$5:$GR$10)-COLUMN($B$5))/1000)),1)*1000)

E. Drag down until the value in G12 begins repeating.

1. Had attempted to replicate the above example.

2. Because the range is fairly large, the hands-on experience was
merely attempting to fill the range partially. And therefore, one of
the output cells (located below G12) showed a zero-value.

3. When the given formula was dragged down, several unique values
appeared until the value in G12 began repeating (at the lower portion
of the output column).

4. And now, how is it possible to modify the given formula such that
the ensuing wish list would be met ?

5. Wish to eliminate the zero-value that was displayed in the output
list (although arguably, the zero-value was pedantically evaluated as
one-of-a-feather amidst the blank cells within the range
partially-filled).

6. Wish to eliminate the repeating values (namely, the value in G12
recurred as many times superfluously as the given formulae were dragged
past the legitimate solution listing at the lower portion of the output
column).

7. Regular experience shows that after the legitimate solution listing,
the appending list (that follows suit as the prevalent formulae are
dragged yonder) would have been certain error-values such as #N/A,
#NUM! or #REF!. The debut of an error-value signals the completion of
legitimate solution listing thereof already.

8. Neutrally speaking, the repeating values appending the output column
should appeal (collectively) in order not to betray the solemn quest
intent upon displaying a unique listing in-situ.

9. Regards.