Point Deep

Mundeep's Tech Blog

Retrieving unique values from a column or list in Excel 2007

Posted by mundeep on February 10, 2009

To generate a list of unique values from a column/list in excel with repeating values it is possible to use the “Advanced Filtering” functionality. To demonstrate let us look at an example where you have some de-normalised data such as a list of countries and colours on their flag you can retrieve both a list of the countries and a list of unique colours used.

Here is a list of our initial data:

Sample Denormalised Data

Figure 1) Sample Denormalised Data

From this we want to return a list of (a) unique countries & (b) unique colours used.

1) In the menu click on Data and then in the Ribbon click on “Advanced” in the filter section.

Figure 2) Advanced Filter Button

Figure 2) Advanced Filter Button

2) If you get a warning about not being able to determine which row contains column labels click on OK (this will assume the first row in your data selection is the column header).

Figure 3) Column Label Warning

Figure 3) Column Label Warning

3) In the advanced filter dialog:

  • Select “Copy to another location”.
  • In the ‘List Range’ select a column of data (eg. the country column).
  • Leave criteria range blank.
  • In the ‘Copy To’ select a single cell in the same sheet to copy the data to.
  • Ensure the “Unique records only” checkbox is ticked.
  • Click OK
Figure 4) Advanced Filter Dialog

Figure 4) Advanced Filter Dialog

4) Repeat for the ‘Colour’ column.

Figure 5) Filter Colour Column

Figure 5) Filter Colour Column

5) You should now have a unique list of countries & colours as follows (note the column headers for the unique lists was changed).

Figure 6) Final Result

Figure 6) Final Result


10 Responses to “Retrieving unique values from a column or list in Excel 2007”

  1. Anonymous said


  2. Adit said

    nice and simple.. thanks

  3. JFG said

    Is there a way of doing this via a formula?

  4. Vikas Goyal said

    Is there a way to use the unique values and getting the count in a macro without copying them to another location?

  5. Asish said


  6. Micha said

    Considering that you want to select from a rage of data where A is the source column and B is the target column with unique elements, you can paste the following code in your macro:

    Range(“A1:A100”).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range(“B1”), Unique:=True

    However, sometimes the first and the last element in the column B are repeated. I haven’t figure it out how to fix that.

  7. vivi said

    I need a formula for the below situation any body here please help..
    I hav some fields like cust,name, cust.details date and transaction
    if i enter the month like May, June something like that i want all the transactions which are happened in that month..

    is there any formula available for the above criteria.?????
    (without using macros)

  8. Lucy said

    HOORAY! My colleague and I just had to high-five after successfully returning a unique list (how worrying). Many thanks.

  9. Very nice thank you. I was not aware of that.

    However it is not auto-refreshable.

    For folks looking for formula-based solution – http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/

  10. Marius said

    For turning filter autorefresh after any change:

    Right click on your sheet name, choose view code and paste the code below. This will enable auto-refresh. Do not forget to save the file in a format with macro support lie .xlsm.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.FilterMode = True Then
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    With ActiveWorkbook
    .CustomViews.Add ViewName:=”Mine”, RowColSettings:=True
    Me.AutoFilterMode = False
    End With

    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    End If

    End Sub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: