Copy Unique Values

One of the more frequent activities I perform is taking a list and extracting unique values as a seperate list.

The way to do this is long and stupid.
In Excel 2003 you select your range, go to the Data menu, Filter menu, select Advanced Filter. Select “Copy to another location”, tick “Unique records only”, then put a range in the “Copy To” box.
In Excel 2007 and above, it’s one less click. Go to the Data ribbon, from the Sort & Filter group click Advanced, then the same Advanced Filter window appears as Excel 2003.

Sick of this, a few years ago I wrote a macro to extract Unique Values, which worked so-so. My macro had minor issues that I could never really be bothered fixing, but it was miles better than trekking through those menus.
Fast forward to a couple of weeks ago and I lost my macro, somehow, which is disappointing, but it was buggy anyway, but I still wanted an easy way to extract unique values and I didn’t want to write the macro again.
It suddenly occurred to me that the same method of hitting shortcuts keys for paste values (alt, e, s, v) could be applied to copying unique values.

Here’s what I do:
Select the range I want to extract from
Hold down the Alt key
Press these keys in sequence: d, f, a, r, o, t
Release the Alt key
Select the range to paste the unique values to

It’s even possible to create a vba macro for this:
SendKeys "%(dfarot)"

Now, to train my muscle memory to type dfarot naturally…

17 thoughts on “Copy Unique Values

  1. In Excel 2003, I need to insert two tab characters between the a and r in the sequence SendKeys “%(dfarot)”. This is because after the dfa portion, the list range is selected. After dfa, one tab brings me to criteria range, and another tab brings me to unique records only (r).

  2. Rob: I found myself doing the same thing a lot as well. I’ve got through a couple of methods of coding a solution. Figure I’d share them here.

    The first was to run through the range and add each item to a collection. Using the Key in teh collection (and turning off error handling) allowed me to isolate values as unique and then a simple application.inputbox allowed me to pick a target location and drop the collection into it.

    The only problem was that the code had to iterate through the entire range, and if the range was big, this could take a while.

    So then I converted it to reading the range into a recordset using ADO and SELECT DISTINCT. This works much faster, and makes selecting a combination of uniques across multiple columns easier than it was with a collection. But using the recordset comes with it’s own interesting side-effects. The most recent that happened to me was this one: http://support.microsoft.com/default.aspx?scid=kb;en-us;932349. It was easy enough to fix, but still annoying. If I moved off of using the reference, converted the code to late binding, it might help (and I still might), but that might be more effort than I’m interested in right now.

    The major thing I don’t like is that the SELECT DISTINCT automatically re-orders the list in ascending order. I figure I could work around this if I embedded the whole thing in a form, but truth be told I don’t want to go through that much work.

    I can post the code if you’re interested.

  3. Code I use, Excel 2003. Select range first, pastes to the RHS

    Sub UniqueItems()

    On Error GoTo ErrorHandler
    With Selection
    .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, Cells.Find(What:=”*”, After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 2), Unique:=True
    End With

    ResumeHere:
    Exit Sub

    ErrorHandler:
    MsgBox Prompt:=”Something went wrong !!”, Buttons:=vbCritical, Title:=”Error …”
    GoTo ResumeHere
    End Sub

  4. you could also try this sort of thing.
    I find it works very well
    obviously the range will need to be update for your workbook

    {=INDEX($P$1:$P$20,MATCH(0,COUNTIF($B$20:B20,$P$1:$P$20), 0))}

    where column )P contains the full list of duplicated values and column B contains the unique values list. its an array formula but it does work quite straight forward.

    DFAROT among others is ingrained in my muscle memory, so I had to think hard before I could remember what it was you were using it for.

  5. In Excel 2007 & 2010 you can just put the “remove duplicates” command on the QAT for easy-peasy 1-click access…

  6. Dick, I gotta say, that’s pretty clever. Here is a routine that I wrote years ago that does the same thing and has served me well. Just to show yet another alternate approach.

    On Error GoTo EndLine
    Set target = Application.InputBox(Prompt:="Select cell to start output:", _
      Title:="Selection", Default:=Selection.Address, Type:=8)
    
    Dim NoDupes As New Collection
    
    
    On Error Resume Next
    For Each cell In Selection
      NoDupes.Add cell.Value, CStr(cell.Value)
      'Note: the 2nd argument (key) for the Add method must be a string
    Next cell
    On Error GoTo 0
    
    n = NoDupes.count
    
    For i = 1 To n
      target.offset(i, 0).Value = NoDupes.Item(i)
    Next i
    
    EndLine: 'Do Nothing
    
    End Sub
    
  7. oops, the first few lines are missing. Let me try again:

    
    Sub ListUnique()
    'A routine to extract the unique values in a range
    'and write them to a column
    'modified from code at:
    ' http://www.j-walk.com/ss/excel/eee/eee004.txt
    Dim cell As Range
    Dim target As Range 'where you want the listing to go
    Dim i As Long, n As Long
    Dim vals(1 To 65536) As Variant
    
    On Error GoTo EndLine
    Set target = Application.InputBox(Prompt:="Select cell to start output:", _
      Title:="Selection", Default:=Selection.Address, Type:=8)
    
    Dim NoDupes As New Collection
    
    
    On Error Resume Next
    For Each cell In Selection
      NoDupes.Add cell.Value, CStr(cell.Value)
      'Note: the 2nd argument (key) for the Add method must be a string
    Next cell
    On Error GoTo 0
    
    n = NoDupes.count
    
    For i = 1 To n
      target.offset(i, 0).Value = NoDupes.Item(i)
    Next i
    
    EndLine: 'Do Nothing
    
    End Sub
    
  8. Like Scott, I used to have a SQL version that worked pretty well. But in 2007/10, I agree with Dave that Remove Duplicates is the way. Even for getting the results in a new sheet, it’s not many keystrokes, and you have complete control over which columns to use. And I just realized last week that you can use it in place and it leaves adjacent columns and areas intact.

  9. One could also create a pivot table with one column only but I use the technique using Collection object often enough – same as Dick describes. I think I met this approach first in John Walkenbach’s book..
    Thanks for the post anyway.

  10. Its amazing – using Sendkeys to mimic the Alt key is very similar to the good ol’ macro instructions in Lotus 123 – Deja vu – just had a throw back to 20 years ago working.

  11. Concerning the “Remove Duplicates” command: “Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell. For example, if you have the same date value in different cells, one formatted as “3/8/2006” and the other as “Mar 8, 2006″, the values are unique.” Source: http://office.microsoft.com/en-us/excel-help/filter-for-unique-values-or-remove-duplicate-values-HP010073943.aspx#BMlearn_more

  12. or

    Sub snb()
    Selection.Cells(1).Name = "snb_001"
    Selection.Name = "snb_002"

    Cells(1, 5).Resize(Selection.Count) = Application.Transpose(Filter([transpose(if(countif(offset(snb_001,,,row(snb_002)-row(snb_001)+1),snb_002)=1,snb_002,"~"))], "~", False))
    Columns(5).SpecialCells(2, 16).ClearContents
    End Sub

  13. Just wanted to say thank you for offering the keyboard shortcut above. This saves me a lot of time. I did notice however that it doesn’t quite work for my situation. The d-f-a-r-o-t key sequence automatically selects all of the surrounding data, and in this case i only wanted a single column included in the range, so it took several more clicks to modify, but the result was still what I was looking for. My initial quest was to find a fast way to generate vlookup entries from a large list of repeating values. This seems to do the trick. Thanks again!

  14. I added a column of =1 in my table next to the column of data. In my VBA code I then use Consolidate to get unique values. I have no idea of how efficient that is but it works ok for my 1100 rows.
    Speed comparison? :-)

Leave a Reply

Your email address will not be published. Required fields are marked *