List of all unique values in a column

[Wes1961]

If a column of values that could be several hundred lines long. I need a list of all unique values in the column.

Example
A
C
B
A
D
A
B


I want to see at list that is only one occurence of each:

A
B
C
D

They do not have to be sorted but would be nice.

I would like to do this without Vb code if possible?

 

[AnotherHiggins]

Highlight the column that you want to filter on, then go to Data > Filter > Advanced Filter > Unique records only

If you want them sorted:
with the column still highlighted, go to Edit > Go to > Special > Visible cells only
Copy and paste somewhere else

-John
Sláinte!
May the road rise to meet you,
May the wind be always at your back,
The the sun shine warm upon your face,
The rain fall soft upon your fields,
And, until we meet again,
May God hold you in the palm of his hand.
-Traditional Irish Toast

 

[SilentAiche]

Also look at pivottables.

Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[almir]

Also try:

1. SubTotals

2. Conditional Sum/Count

Al

 

[byundt]

Wes1961,
Generating a list of unique values using formulas is normally a two step process (see

http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

), with one formula being used to extract the unique items and another to eliminate the blanks. As a result, an auxiliary column is needed for the extraction.

The following formula does the job all in one step (assuming the list contains no blanks). And it works for both numeric and text data:

=INDEX(List,SMALL(IF(MATCH(List,List,0)=ROW(List)-mmm,ROW(List)-mmm,65536),ROW()-nnn)) Array formula, so CTRL + Shift + Enter
List is the cells containing your data--either a named range or cell addresses. No blanks are allowed within this list, and it may not extend beyond your data.
mmm is one less than the first row in List
nnnn is one less than the first row receiving the formula

To use the formula, replace the mmm and nnn with fixed values, then copy the formula down. If your source list starts in row 1 and your unique values also start in row 1, you can use the simplified version:
=INDEX(List,SMALL(IF(MATCH(List,List,0)=ROW(List),ROW(List),65536),ROW()))

Note that the formula will return an error value when you run out of unique values.

Brad

 

[byundt]

Wes1961,
Here is a longer array formula that will tolerate blanks in the raw list and a list range that extends beyond your data. This formula may be copied down, and will return blank strings when the unique values have been exhausted.
="" & INDEX(List, SMALL(IF((List<>"")*(MATCH(IF(List="","",List),IF(List="","",List),0)=ROW(List)-mmm),ROW(List)-mmm,last-mmm),ROW()-nnn))
List is the cells containing your data--either a named range or cell addresses. List must extend beyond your data because it needs to include at least one blank.
mmm is one less than the first row in List
nnnn is one less than the first row receiving the formula
last is the row number of a blank element in List. It is most convenient if List is a fixed range that extends beyond any expected data--so last would be the row number of the blank at the end of that range.

Note: this formula will convert any numbers into text.
Brad