Create one dropdown list from multiple columns removing duplicates

[LEWI_74]

Hi,

I have searched the forum for the past few days but can't quite find what I'm looking for so any assistance would be greatly appreciated.

The problem:
I have a list of names relating to donations made to a charity over a seven year period. These names are located in Columns B, D, F, H, J, L and N.

The maximum range in each of these columns is Row 200 (names would start in Row 2. The financial year, e.g. 2009/10 is in Row 1). Data may not fill all 200 rows in some years.

The names could appear only once or not at all in each unique column BUT they may appear in other columns over the years.

What I want to achieve:
I want to create one drop-down list which summarises all of the columns and removes duplicates so that over the seven year period I have a list of all of the names that have appeared but the list just has them once.

Many thanks

David

 

[jbeaucaire]

1) Copy all the columns of data into a single empty column somewhere, one set below the other.
2) Sort the combined column data
3) make sure the top cell is a label, like "Names", not part of the actual list
4) Highlight the column
5) Use the Data > Filter > Advanced Filter to copy to another location (use another empty column) and make sure [x] Unique values only is chosen

Now you have the unique list. Delete the first column you created, use this last column as the source of your new drop down.

 

[VoG]

1) Copy all the columns of data into a single empty column somewhere, one set below the other.
2) Sort the combined column data
3) make sure the top cell is a label, like "Names", not part of the actual list
4) Highlight the column
5) Use the Data > Filter > Advanced Filter to copy to another location (use another empty column) and make sure [x] Unique values only is chosen

Now you have the unique list. Delete the first column you created, use this last column as the source of your new drop down.

Hi Jerry

I was about to post pretty much the same but you beat me to it. But I was thinking, say the OP has a button that is meant to go to the combobox selection, what happens with dups? Perhaps we'll have to await a response.

 

[LEWI_74]

Thanks for the reply. Is there a way to do it through VBA code and automate the process?

The list may change as there are four years of forecast data (names) and therefore likely to change.

 

[VoG]

Maybe

Sub PopulateListWithUniqueItems()
Dim anyR As Range
Dim listCollection As New Collection
Dim cMember
Dim i As Integer
Dim j As Integer, lCount As Integer, cell As Range, tempS, myList()
Set anyR = Range("B1:B100, D1:B100, F1:F1:H100 H1:H100, J1:J100, L1:L100,N1:N100")
On Error Resume Next
For Each cell In anyR
    If Not IsEmpty(cell) Then
        listCollection.Add cell.Value, CStr(cell.Value)
    End If
Next
On Error GoTo 0
lCount = listCollection.Count
ReDim myList(1 To lCount)
For Each cMember In listCollection
    i = i + 1
    myList(i) = cMember
Next
For i = 1 To lCount - 1
    For j = i + 1 To lCount
        If myList(i) > myList(j) Then
            tempS = myList(i)
            myList(i) = myList(j)
            myList(j) = tempS
        End If
    Next
Next
UserForm1.ListBox1.List = myList
End Sub

 

[LEWI_74]

I will put this into action - thank you.

 

[jbeaucaire]

1) Add a sheet and name List
2) In the INSERT > NAME > DEFINE create a name range called Names and set the Refers To: =OFFSET(List!$A$2,,,COUNTA(List!$A:$A)-1,)

Now you have a dynamic list that will comprise all the values that end up in column A that you can use for data validation drop boxes.

3) Name your data sheet Data and run this macro:

Option Explicit

Sub MakeNameList()
Dim wsList As Worksheet
Dim wsData As Worksheet
Dim Col As Long, LR As Long, NR As Long

Set wsData = Sheets("Data")         'this sheet has the columns of names
Set wsList = Sheets("List")         'this sheet will have the drop down list

Application.ScreenUpdating = False  'speed up macro
wsList.Range("A:A").Clear           'clear existing list of names
wsList.Range("B1") = "Names"        'top of temp list

With wsData
    For Col = 2 To 14 Step 2        'create temp list of all names in wsList column B
        LR = .Cells(.Rows.Count, Col).End(xlUp).Row
        .Range(.Cells(2, Col), .Cells(LR, Col)).Copy _
            wsList.Range("B" & wsList.Rows.Count).End(xlUp).Offset(1)
    Next Col
End With

With wsList                         'sort, then extract unique names to column A
    .Columns(2).Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes
    .Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"), Unique:=True
    .Columns(2).Clear
    .Names("Extract").Delete
End With

Application.ScreenUpdating = True
End Sub

 

[LEWI_74]

Thank you so much Jerry, that works perfectly.