Unique values

T

travis

I need help for returning unique values. I can count the unique values but I
need to show them. For each different ManDocNo, I want to return the unique
EPACodes. A simplified example below:

ManDocNo EPAUCode EPAPCode EPAD1Code
24155580
24155580 D009
24155580
24155580 U151 D009
AR1599445
AR1599445
AR1599445
AR1599445
AR1599445
AR1599445 D001
AR1599445 U019 D001
AR1599445 D002
AR1599445 P087
AR1599445 P105
AR1599445 U006 D001
AR1599445 U019 D018
AR1599445 U031
AR1599445 U044 D022
AR1599445 U053 D001
AR1599445 U057 D001
AR1599445 U092 D002
AR1599445 U103 D002

Thanks for your help.
 
B

Bob Phillips

In say M2, type =A2

In M3, enter

=IF(ISERROR(MATCH(0,COUNTIF(M$2:M2,$A$2:$A$200&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$200),"",$A$2:$A$200),MATCH(0,COUNTIF(M$2:M2,$A$2:$A
$200&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter. Copy M3 down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Domenic

Different interpretation...

Assumptions:

A1:D1 contains your headers/labels

A2:D23 contains your data

F2:F3 contains 24155580 and AR1599445

Formula:

G2, copied across and down:

=INDEX($B$2:$B$23,MATCH(0,IF(($A$2:$A$23=$F2)*($B$2:$B$23<>""),COUNTIF($F
2:F2,$B$2:$B$23)),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that you'll
get #N/A when no more unique values exist. You can use conditional
formatting to hide these errors. Also, to create a list of unique
ManDocNo's for Column F, you can use 'Advanced Filter' and check 'Unique
records only' and copy to your location, Column F.

Hope this helps!
 
Top