Extract unique values in a column from a table

[gandreso]

I got a datafile with about 50000 rows of data where in column A there is a ID number and in column B there is a dateformat as the following, yyyyww, for example 201705 which is week 5 in the year 2017. In column A there will the same ID number appear in several rows but I only want to have one row per ID number. Therefore I need to extract all unique values in column A with each corresponding column. When a ID number has a duplicate then I only want to see the ID number with the earliest date in column B. Its possible to sort column B from the first date to the last if that helps. Anyone know how I can solve this?

[Richard Buttrey]

Hi,

Make sure the data is sorted in date order ascending then use a helper column. Add the following formula to the first row

=COUNTIF($A1:A1,A1)

Copy this down and then filter on the helper column for all the '1' values. Select the rows and copy and paste them.

[kasan]

Hi,
Please attach slamm sample file with dummy data, but make sure the structure of file is the same as your original file.

[gandreso]

I tried to use a code from one of my previous projects which I think someone in here helped me with. I changed the code a bit and it looks like its working as intented but then I found out that it apparently still is a couple duplicates in column 5, did I miss something when I changed my code?

Dim Rng As Range, Dn As Range, n As Long, Ray As Variant, Ac As Long, c As Long, cell As Range

Ray = ActiveSheet.UsedRange
ReDim nRay(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For n = 1 To UBound(Ray, 1)
    If Not .Exists(Ray(n, 5)) Then
        c = c + 1
        For Ac = 1 To UBound(Ray, 2)
            nRay(c, Ac) = Ray(n, Ac)
        Next Ac
        .Add Ray(n, 5), c
    End If
Next n
Range("A:A").Resize(, UBound(Ray, 2)).ClearContents
Range("A1").Resize(.Count, UBound(Ray, 2)).Value = nRay
End With