Create value list from range

  • Thread starter Thread starter mallets123
  • Start date Start date
M

mallets123

I've got a range of cells containing state abbrev. that I want to list
in another range excluding all duplicated states abbrev's.

Is there a way to list the non-duplicated values?

Thanks for your help...
 
Try this:

Make sure you have a heading for your list. I'll assume your range is
A1:A100 and A1 is STATE.

In a blank cell off to the side on the same sheet, enter your column
heading.
I'll use Z1: STATE

Data>Filter>Advanced Filter:
Check: Copy to another location
Check: Unique records only
List Range: $A$1:$A$100
Copy to: $Z$1
Click the [OK] button

You should now have a list of unique state abbreviations under cell
Z1.

Does that help?

Regards,
Ron
 
First make sure there is a label cell on top of your column, select the data
(including the label) and then pull-down:

Data > Filter > Advanced Filter... and check unique records
 
Thanks for your help on this, is there a way to have this done
automatically rather than having to manually do it each time?
 
OK...try this:

With your list of state abbreviations in A1:A20, with A1: State

D1: State
D2
=IF(SUM(IF($A$1:$A$21<>"",1/COUNTIF($A$1:$A$21,$A$1:$A$21)))=COUNTIF($D$1:$D1,"??")+1,"",INDEX($A$2:$A$21,MATCH(TRUE,ISERROR(MATCH($A$2:$A$21,$D$1:$D1,0)),0),1))

Note: Commit that array formula by holding down the [Ctrl][Shift] key
and press [Enter].

Copy D2 and paste it into D3 through D52.

Each time you enter a new state abbreviation that is not alread
listed, it will display in the list in D1:D52.

Change range references to suit your situation.

Does that help?

Regards,
Ro
 
Back
Top