none
How to get UNIQUE VALUES from a column in Excel RRS feed

  • Question

  • Hello,

    I have a column (A) of values in Excel. I would like to get/develop a VBA macro that will copy all the UNIQUE values in Col. A to another column (C).

    I have searched a lot on the web, but have not seen a single VBA example.

    It seems that there are 2 different problems:

    (a) Copy all UNIQUE DISTINCT values,

    (b) Copy all UNIQUE values,

    Unfortunately, the terms are not standardised, and people mix the two.

    So, let me explain what I want: UNIQUE values.

    ----------

    Col. A List                             UNIQUE DISTINCT values                    UNIQUE values

    1001                                                1001

    1001

    1002                                                1002                                                 1002

    1003                                                1003

    1003

    1004                                                1004                                                 1004

    1005                                                1005                        

    1005                                               

    Unique Distinct Values:

    Are values which occur ONCE, or more than once in the list

    Unique Values:

    Are values which occur ONLY ONCE in the list. In my example, there are only 2 Unique values.

    I learn that only Office 365 provides a function called UNIQUE which lists all Unique values. Unfortunately, I don't have Office 365.

    -------

    What I need is a VBA for UNIQUE values (I have not seen a single example on the web), and NOT a VBA for UNIQUE DISTINCT values (very many examples on the web).

    -----

    Note:

    There is an Excel function here:

    Data -> Sort & Filter -> Advanced -> Advanced Filter -> Copy to another location -> Unique Records Only.

    Unfortunately, it acts like UNIQUE DISTINCT, rather than UNIQUE.

    Any help will be much appreciated.

    Thanks

    Leon


    • Edited by Leon Lai Monday, January 27, 2020 12:26 PM
    Monday, January 27, 2020 12:23 PM

Answers

  • Here is a VBA procedure:

    Sub CopySingleOccurrenceValuesOnly(Source As Range, Target As Range)
        Dim c As Range
        Dim n As Long
        For Each c In Source
            If Application.CountIf(Source, c.Value) = 1 Then
                Target.Offset(n).Value = c.Value
                n = n + 1
            End If
        Next c
    End Sub

    Example of usage:

    Sub Test()
        CopySingleOccurrenceValuesOnly Range("A2:A9"), Range("K2")
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Leon Lai Tuesday, January 28, 2020 5:55 AM
    Monday, January 27, 2020 3:52 PM

All replies

  • Here is a VBA procedure:

    Sub CopySingleOccurrenceValuesOnly(Source As Range, Target As Range)
        Dim c As Range
        Dim n As Long
        For Each c In Source
            If Application.CountIf(Source, c.Value) = 1 Then
                Target.Offset(n).Value = c.Value
                n = n + 1
            End If
        Next c
    End Sub

    Example of usage:

    Sub Test()
        CopySingleOccurrenceValuesOnly Range("A2:A9"), Range("K2")
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Leon Lai Tuesday, January 28, 2020 5:55 AM
    Monday, January 27, 2020 3:52 PM
  • Excel 2010 to 2020 with Power Query (aka Get & Transform)
    Unique
    Also with VBA with newfangled Tables.
    http://www.mediafire.com/file/04zs66auq3oowlf/01_27_20.xlsm/file
    http://www.mediafire.com/file/ww571vmkpy25h75/01_27_20.pdf/file

    Monday, January 27, 2020 6:10 PM
  • Hi, Mr. Hans

    I have tried your VBA Code. It works great for me.

    Thanks a lot, and Best Regards

    Leon

    Tuesday, January 28, 2020 5:55 AM
  • Hi, Mr Herbert

    Thanks for your reply.

    Unfortunately, I could not open the files on my Office computer, as the administrator has blocked all downloads.

    I will test on my home computer tonight or tomorrow, and will let you know how it goes.

    Best Regards,

    Leon

    ----------

    Added 29/01/2020

    I checked the links on my home computer.

    They are about Power Query (completely new to me). I will have to take some time to study it.

    Thanks for sharing

    Leon

    • Edited by Leon Lai Wednesday, January 29, 2020 6:18 AM
    Tuesday, January 28, 2020 5:57 AM