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. How about using the ‘Remove Duplicates’ option from the ribbon under ‘Data’ ?

4. 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

5. 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.

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

7. 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
   

8. 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
   

9. I’m still using this http://www.dailydoseofexcel.com/archives/2008/12/11/create-unique-list-from-selected-cells/ which has served me well. I’ll be I use this a dozen times per week. Even if I want a quick count of uniques, I’ll create this list rather than right the 1/countif array formula.

10. 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.

11. 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.

12. 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.

13. 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

14. 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

15. 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!

16. 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? :-)

17. Pingback: #Excel Short and Sweep Tip #13 (KeyBoard DFAROT Unique Technique) by David Hager | Excel For You

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.