Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter out duplicate data
I'm trying to write a macro that will filter out duplicate entries in a
column so that I can use the entries in that column to populate a combobox. Here's an example of what I'm trying to do. A A B B B B C B A C C D D I want to filter the above data using code so that the end result is: A B C D This doesn't seem like it should be that diffucult, but I'm having problems. Maybe I just need a nap. Anyways, any help provided will be much appreciated. Thanks, Jason |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter out duplicate data
here's something i cobbled together with the help of others here. see if you can
adapt it. it uses a hard coded range A1:A!0, but you can code it for a variable number of rows. it adds each letter to a collection. the collection creates an error when a dupe is added, so there is an on error resume next statement. some don't like to rely on an error, but it has always worked for me. then it sorts the letters and adds them to combobox1 on userform1 when it is activated Private Sub UserForm_Activate() Dim NoDupes As Collection Dim rng As Range Dim cell As Range Dim i As Long Dim j As Long Dim Swap1, Swap2, Item Set NoDupes = New Collection Set rng = Range("A1:A10") On Error Resume Next For Each cell In rng NoDupes.Add cell.Value, CStr(cell.Value) Next cell ' Resume normal error handling On Error GoTo 0 ' Sort the collection (optional) For i = 1 To NoDupes.Count - 1 For j = i + 1 To NoDupes.Count If NoDupes(i) NoDupes(j) Then Swap1 = NoDupes(i) Swap2 = NoDupes(j) NoDupes.Add Swap1, befo=j NoDupes.Add Swap2, befo=i NoDupes.Remove i + 1 NoDupes.Remove j + 1 End If Next j Next i ' Add the sorted, non-duplicated items to a ListBox For Each Item In NoDupes UserForm1.ComboBox1.AddItem Item Next Item End Sub -- Gary "JayWes" wrote in message ... I'm trying to write a macro that will filter out duplicate entries in a column so that I can use the entries in that column to populate a combobox. Here's an example of what I'm trying to do. A A B B B B C B A C C D D I want to filter the above data using code so that the end result is: A B C D This doesn't seem like it should be that diffucult, but I'm having problems. Maybe I just need a nap. Anyways, any help provided will be much appreciated. Thanks, Jason |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter out duplicate data
If you use Advanced filter you can create a column of unique records. Record
a macro while doing this for the VB syntax. -- Regards, OssieMac "JayWes" wrote: I'm trying to write a macro that will filter out duplicate entries in a column so that I can use the entries in that column to populate a combobox. Here's an example of what I'm trying to do. A A B B B B C B A C C D D I want to filter the above data using code so that the end result is: A B C D This doesn't seem like it should be that diffucult, but I'm having problems. Maybe I just need a nap. Anyways, any help provided will be much appreciated. Thanks, Jason |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter out duplicate data
One more option. Needs xl2000 or later:
Private Sub UserForm_Initialize() ComboBox1.List = NoDupes(Application.Transpose(Range("A1:A10").Valu e)) End Sub Function NoDupes(arr As Variant) As Variant Dim i As Integer, txt As String txt = ";" For i = LBound(arr) To UBound(arr) If InStr(txt, ";" & arr(i) & ";") = 0 Then txt = txt & arr(i) & ";" End If Next txt = Mid$(txt, 2, Len(txt) - 2) NoDupes = Split(txt, ";") End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter to Duplicate Entries Only | Excel Discussion (Misc queries) | |||
Filter duplicate data in excel spreadsheet | Excel Programming | |||
TRYING TO SORT OR FILTER DUPLICATE DATA | Excel Discussion (Misc queries) | |||
How do I filter out duplicate values in Excel 2000? | Excel Worksheet Functions | |||
Is there an easy way to filter duplicate rows of data in excel? | Excel Discussion (Misc queries) |