Making list of unique items from two different columns

[casey]

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

 

[ryguy7272]

I think I understand, but not totally sure. Anyway, try this an post back if
it doesn't work...

ColumnA
ABC
DEF
ABC
ABC
MNB
ABC

ColumnB
123A
16LM
437F
123A
789H
437F


In E1 put ABC
In F1 paste this function:
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")
When you enter it, hit Ctrl + Shift + Enter (it is an array).

Watch the magic show!!

Regards,
Ryan---

 

[Gary''s Student]

Very nice! A formula worth keeping.

 

[casey]

Ryan,

Thnx for such a quick reply. This works almost to a "T", except that it
lists all of the entries in Col B including dupes, ie:

ABC 123A
123A
437F
437F

The return I need is:

ABC 123A
437F

Thnx again,
casey


.. Can this one be tweaked to only show the unique entries?

 

[Teethless mama]

Try this:

In D1:
=IF(ISERR(SMALL(IF(FREQUENCY(IF($A$1:$A$6=C$1,MATCH($B$1:$B$6,$B$1:$B$6,0)),MATCH($B$1:$B$6,$B$1:$B$6,0))>0,ROW(INDIRECT("1:"&ROWS($A$1:$A$6)))),ROWS($1:1))),"",INDEX($B$1:$B$6,SMALL(IF(FREQUENCY(IF($A$1:$A$6=C$1,MATCH($B$1:$B$6,$B$1:$B$6,0)),MATCH($B$1:$B$6,$B$1:$B$6,0))>0,ROW(INDIRECT("1:"&ROWS($A$1:$A$6)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[T. Valko]

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$11)<=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$11))),"")

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)

 

[casey]

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]

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.

 

[casey]

Your formula worked perfectly! I figured out what I had done wrong. I guess
I had to sleep on it.

Thanks,
casey

 

[casey]

Thanks to you, too, "Teethless mama" for your response. I had never used the
ISERR in a formula and was apprehensive to do so. Now that I have the
formula working from Biff, I'll try yours. I DO appreciate your reply to my
question.
casey

 

[T. Valko]

You're welcome. Thanks for the feedback!