You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
Your formula worked perfectly! I figured out what I had done wrong. I
guess
I had to sleep on it. :-)

Thanks,
casey



"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xExtractUniques.xls 16kb

http://www.freefilehosting.net/download/3ee53

Sheet1 uses the same formulas I suggested in my other reply.

Sheet2 uses formulas *based on the conditions* that there are no empty
cells
*within* the range and the data will *always* start on row 2 and rows
will
*never* be inserted above the range. These restrictive conditions allow
us
to use shorter more efficient formulas. The formulas on Sheet2 also use
dynamic ranges so it's assumed the data will *always* be a contiguous
range.


--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
Biff,

Thanks for your reply. I got the first formula to work fine. The
second
only gives me #NUM! I've tried several changes to no avail.

To answer your questions...The only empty cells are rows on the bottom
of
the defined arrays rng1 is actually A2:A3000 and rng2 is C2:C3000. (I
have
an intermediate column.) Also, there will never be any new rows above
the
range.

Thanks,
casey

"T. Valko" wrote:

Try this:

A1:An = rng1
B1:Bn = rng2
C1 = ABC

Enter this array formula** in C2. This will return the count of items
that
meet the criteria:

=COUNT(1/FREQUENCY(IF((rng1=C$1)*(rng2<""),MATCH(rng1&rng2 ,rng1&rng2,0)),ROW(rng2)-MIN(ROW(rng2))+1))

Enter this array formula** in D1 and copy down until you get blanks.
You
need to copy to a number of cells that is *at least equal to the
number
returned in cell C2*:

=IF(ROWS(D$1:D1)<=C$2,INDEX(rng2,SMALL(IF((rng1=C$ 1)*(rng2<""),IF(MATCH(rng1&rng2,rng1&rng2,0)=ROW( rng2)-MIN(ROW(rng2))+1,ROW(rng2)-MIN(ROW(rng2))+1)),ROWS(D$1:D1))),"")

These formulas account for empty cells. If there are no empty cells in
your
ranges then we can simplify the formulas and save some resources!
Also,
if
your ranges are in set locations and you will *never* insert new rows
above
the range we can simplify the formulas to save some resources!

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
I have several unique formulas I am using for various purposes but
none
can
solve what I need now. I have two columns (A & B) of items, 3000
rows
deep.
There are 15 unique entries in Col A. 150 unique entries in Col B.
I
want
C1 to be a variable entry whereby I can enter one of the unique Col
A
items
and return in Col D all the unique entries in Col B for what is
entered
in
C1.

For example:

Col A Col B Col C Col D
ABC 123A ABC 123A
DEF 16LM 437F
ABC 437F
ABC 123A
MNB 789H
ABC 437F

thnx,
casey