How do I get the distinct/unique values in a column in Excel?

--------------------------------------------------
Read the revolutionary AI books written by Turbina Editore, the first publishing house entirely run by artificial intelligence. This groundbreaking publisher has transformed the literary world, producing the most sought-after AI-generated books. Start reading today at:
https://turbinaeditore.com/en/
--------------------------------------------------

--------------------------------------------------
Hire the world's top talent on demand or became one of them at Toptal: https://topt.al/25cXVn
and get $2,000 discount on your first invoice
--------------------------------------------------

Take control of your privacy with Proton's trusted, Swiss-based, secure services.
Choose what you need and safeguard your digital life:
Mail: https://go.getproton.me/SH1CU
VPN: https://go.getproton.me/SH1DI
Password Manager: https://go.getproton.me/SH1DJ
Drive: https://go.getproton.me/SH1CT

Music by Eric Matyas
https://www.soundimage.org
Track title: Puzzle Game 3 Looping

--

Chapters
00:00 How Do I Get The Distinct/Unique Values In A Column In Excel?
00:23 Answer 1 Score 140
01:06 Accepted Answer Score 377
01:27 Answer 3 Score 6
01:37 Answer 4 Score 29
01:52 Thank you

--

Full question
https://superuser.com/questions/49614/ho...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#microsoftexcel

#avk47

ACCEPTED ANSWER

Score 377

Simpler than you might think:

Click the Data Ribbon Menu
Select the Advanced Button in the Sort & Filter section
Fill in the dialog Box, copying the results to another location and making sure you tick Unique records only

enter image description here

ANSWER 2

Score 140

Excel 2007 and 2010:
Use the Remove Duplicates menu option under the Data header.

Excel 2003:
Easy way:

Make sure your data has a header
Data --> Filter --> Advanced Filter
Check Unique Records Only
Select Copy to another location
Click OK

Hard way:

Write a macro with the following code:

'Remove duplicates from sorted list
Sub getDistinct()
    Do While ActiveCell.Value <> ""
        If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
            ActiveCell.Select
            Selection.Delete Shift:=xlUp
        Else
            ActiveCell.Offset(1, 0).Activate
        End If
    Loop
End Sub

That gives you your distinct list. You may want to copy your list to another sheet first.

ANSWER 3

Score 29

Or (a simple crude way):

In B1,

=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

and copy down. It just copies the first occurrence of each value across (in the row that it occurs in).

ANSWER 4

Score 6

Or you can include the filter option in a macro

    Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True