# return unique data from a list

C

#### 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

Hi Casey
Insert a header at Row 1 entitled whatever you wish

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.

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...

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