return unique data from a list

[casey]

I have 2 columns of data, several thousand rows and growing. Column B has
120 unique entries. Column C has 500 unique entries. I am using the
following array formula (very successfully thanks to Biff) to pull out the
unique entries from Column C.

{=IF(ROWS($1:1)<=SUM(1/COUNTIF(A$2:A$20,A$2:A$20)),INDEX(A$2:A$20,SMALL(IF(ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1=MATCH(A$2:A$20,A$2:A$20,0),ROW(A$2:A$20)-MIN(ROW(A$2:A$20))+1),ROWS($1:1))),"")}

Now i would like to pull the unique entries from Column C that are unique to
only each unique entry in Column B.

For example:

Col A Col B
ABC 123XYZ
DEF 123XYZ
ABC 456LMN
GHJ 123XYZ
ABC 789STV

I want to list in Cols E and F:

Col E Col F
ABC 123XYZ
ABC 456LMN
ABC 789STV

Thanks,
casey

 

[Roger Govier]

Hi Casey
Insert a header at Row 1 entitled whatever you wish
Highlight column B>Data>Filter>Advanced Filter>Unique values

 

[casey]

Thanks, Roger. I do understand how to use the advanced filter but I actually
want to "list" (for auto-updates) the results to a different area, worksheet,
workbook, etc.

 

[ryguy7272]

With letters in A1:A5, and letters/numbers in B1:B5, and ABC in E1, use
either of these functions:
=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))),"")

=IF(ROWS($1:1)>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)),ROWS($1:1))))

Both are CSE entered.

Regards,
Ryan---

PS, I did not create these; found them by reading through this DG over the
past year or so...

 

[casey]

Thanks, Ryan. Works like a charm. And thanks, also to Roger, as your
explanation worked as well 'cept that my first explanation wasn't as complete
as I intended.

casey