how to select distinct value from a column in excel automatically.

[hyugai]

Hi Everyone,

This is my first post. I'm not so good with excel macro or functions.

So i have one column with a list of name (Column A) and I want to get a distinct value of column A and produce Column B automatically. So that when I enter a value in column A, column B get updated automatically.

A B
----------------------------
1|Joe | Joe
2|Joe | John
3|John | Liz
4|John |
5|John |
6|Liz |
7|Liz |
8|Liz |


Please help
thank you in advance.

[mmmarks]

HI welcome to the board

=INDEX($B$1:$B$10,MATCH(0,COUNTIF($C$1:C1,$B$1:$B$10),0))

paste above formula in C2. Note: Be sure C1 shoudl be empty and hit CTRL+SHIFT+ENTER NOT JUST Enter and drag down

[Ace_XL]

I'd go with pivot tables..See attached

[mmmarks]

Hi,

there is another simple way.

Go Data --> Adanced filter--> in List range, Select the range
then -->Click on Copy to another location -->copy to "select a cell where you want output
--> Check on Unique records Only--> then OK

Thanks
A

[oeldere]

This recorded macro will do that for you.

See the example.

Sub Delete_duplicates()

'This recorded macro copies data to column M, removes Duplicates and paste the result in column B

    Columns("A:A").Select
    Selection.Copy
    Columns("M:M").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$M$1:$M$500").RemoveDuplicates Columns:=1, Header:=xlNo
    Range("M1:M500").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("M:M").ClearContents
    Range("A1").Select
End Sub