엑셀 VBA로 드롭다운 목록에서 고유 값만 추출하는 방법 (완벽 가이드)

이 글에서는 엑셀에서 VBA를 활용해 워크시트 드롭다운 목록의 중복 값을 제거하고 고유 값만 남기는 방법을 소개합니다. 최소 한 번 이상 나타나는 고유 값과 정확히 한 번만 나타나는 고유 값을 각각 추출하는 방법까지 모두 배우실 수 있습니다.

드롭다운 목록 고유 값 추출 매크로 (빠른 미리보기)

Sub Drop_Down_List_Unique_Values_At_Least_Once()

List_Location = "B3"

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Range(List_Location).Validation.Delete

Unique_Data = ""

Count = 0

For i = LBound(Data) To UBound(Data)
    Unique_Values = Split(Unique_Data, ",")
    For j = LBound(Unique_Values) To UBound(Unique_Values)
        If Data(i) = Unique_Values(j) Then
            Count = 1
            Exit For
        End If
    Next j
    If Count = 0 Then
        If Unique_Data = "" Then
            Unique_Data = Unique_Data + Data(i)
        Else
            Unique_Data = Unique_Data + "," + Data(i)
        End If
    End If
    Count = 0
Next i

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

엑셀 VBA로 드롭다운 목록에 고유 값 유지하기

아래 예시에서는 엑셀 워크시트의 B3 셀에 국가 이름이 담긴 드롭다운 목록이 있다고 가정합니다.

그런데 보시는 것처럼 목록에는 일부 이름이 반복되어 있습니다. 예를 들어 독일(Germany)은 세 번, 이탈리아(Italy)는 두 번 등장합니다.

오늘의 목표는 드롭다운 목록에서 중복 값을 제거하고 고유 값만 남기는 것입니다.

1. 최소 한 번 이상 나타나는 고유 값만 유지하는 매크로 만들기

먼저 드롭다운 목록에서 최소 한 번 이상 나타나는 모든 고유 값을 유지하는 매크로를 만들어 보겠습니다.

예를 들어 위 목록에 이 매크로를 적용하면 결과는 Germany, Italy, France, England가 됩니다.

이를 위한 VBA 코드는 다음과 같습니다.

⧭ VBA 코드:

Sub Drop_Down_List_Unique_Values_At_Least_Once()

List_Location = "B3"

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Range(List_Location).Validation.Delete

Unique_Data = ""

Count = 0

For i = LBound(Data) To UBound(Data)
    Unique_Values = Split(Unique_Data, ",")
    For j = LBound(Unique_Values) To UBound(Unique_Values)
        If Data(i) = Unique_Values(j) Then
            Count = 1
            Exit For
        End If
    Next j
    If Count = 0 Then
        If Unique_Data = "" Then
            Unique_Data = Unique_Data + Data(i)
        Else
            Unique_Data = Unique_Data + "," + Data(i)
        End If
    End If
    Count = 0
Next i

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

⧭ 실행 결과:

코드를 실행하면 활성 워크시트의 B3 셀 드롭다운 목록에서 중복 값이 제거되고, 최소 한 번 이상 나타난 값만 남게 됩니다.

⧭ 참고 사항:

코드를 실행하기 전에 드롭다운 목록이 있는 워크시트를 반드시 활성화하세요. 또한 목록 위치의 셀 참조(B3)를 상황에 맞게 수정한 후 실행해야 합니다.

추가 학습: 엑셀에서 고유 값으로 드롭다운 목록 만들기 (4가지 방법)

2. 정확히 한 번만 나타나는 고유 값만 유지하는 매크로 만들기

이번에는 드롭다운 목록에서 정확히 한 번만 나타나는 값만 유지하는 매크로를 만들어 보겠습니다.

예를 들어 위 목록에 이 매크로를 적용하면 결과는 France, England가 됩니다. 독일과 이탈리아처럼 중복된 항목은 제외됩니다.

이를 위한 VBA 코드는 다음과 같습니다.

⧭ VBA 코드:

Sub Drop_Down_List_Unique_Values_Exactly_Once()

List_Location = "B3"

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Unique_Data = ""

Range(List_Location).Validation.Delete

Count = 0

For i = LBound(Data) To UBound(Data)
    For j = LBound(Data) To UBound(Data)
        If j <> i And Data(i) = Data(j) Then
            Count = 1
            Exit For
        End If
    Next j
    If Count = 0 Then
        If Unique_Data = "" Then
            Unique_Data = Unique_Data + Data(i)
        Else
            Unique_Data = Unique_Data + "," + Data(i)
        End If
    End If
    Count = 0
Next i

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

⧭ 실행 결과:

코드를 실행하면 활성 워크시트의 B3 셀 드롭다운 목록에서 중복이 있는 값들이 제거되고, 정확히 한 번만 나타나는 값만 남게 됩니다.

⧭ 참고 사항:

마찬가지로 코드를 실행하기 전에 드롭다운 목록이 있는 워크시트를 활성화하고, 목록 위치의 셀 참조를 필요에 따라 변경하세요.

관련 콘텐츠: 엑셀 드롭다운 목록에서 다중 선택하기 (3가지 방법)

함께 읽으면 좋은 글:

• 엑셀에서 선택 기반으로 데이터를 추출하는 드롭다운 필터 만들기
• 색상이 적용된 엑셀 드롭다운 목록 만들기 (2가지 방법)
• 엑셀 드롭다운 목록이 작동하지 않을 때 (8가지 문제와 해결책)
• 엑셀 드롭다운 목록 자동 업데이트 (3가지 방법)
• VBA로 드롭다운 목록 값 선택하기 (2가지 방법)

3. UserForm을 활용해 고유 값을 넣는 드롭다운 목록 만들기

마지막으로 UserForm(사용자 폼)을 만들어 VBA로 드롭다운 목록의 중복 값을 제거하고 고유 값만 유지하는 방법을 알아보겠습니다.

⧪ 1단계: UserForm 열기

VBA 편집기에서 삽입 > 사용자 폼(UserForm) 메뉴로 이동하여 새 UserForm을 엽니다. UserForm1이라는 새 폼이 생성됩니다.

⧪ 2단계: 도구 상자에서 컨트롤 끌어오기

UserForm 옆에는 도구 상자(Toolbox)가 있습니다. 도구 상자에서 레이블(Label) 3개, 리스트박스(ListBox) 2개(Label1과 Label3 아래에 배치), 텍스트박스(TextBox) 1개(Label2 아래에 배치)를 그림과 같이 배치합니다.

마지막으로 명령 단추(CommandButton) 하나를 오른쪽 하단으로 끌어다 놓습니다.

⧪ 3단계: ListBox1 코드 작성

ListBox1을 더블클릭하면 ListBox1_Click이라는 Private Sub 프로시저가 열립니다. 거기에 아래 코드를 입력합니다.

Private Sub ListBox1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Activate
        Exit For
    End If
Next i

End Sub

⧪ 4단계: TextBox1 코드 작성

다음으로 TextBox1을 더블클릭하면 TextBox1_Change라는 Private Sub 프로시저가 열립니다. 거기에 아래 코드를 입력합니다.

Private Sub TextBox1_Change()

On Error GoTo TB1:

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
    x = 21

End Sub

⧪ 5단계: CommandButton1 코드 작성

마지막으로 CommandButton1을 더블클릭하면 CommandButton1_Click이라는 Private Sub 프로시저가 열립니다. 거기에 아래 코드를 입력합니다.

Private Sub CommandButton1_Click()

List_Location = UserForm1.TextBox1.Text

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Range(List_Location).Validation.Delete

Unique_Data = ""

Count = 0

If UserForm1.ListBox2.Selected(0) = True Then
    For i = LBound(Data) To UBound(Data)
        Unique_Values = Split(Unique_Data, ",")
        For j = LBound(Unique_Values) To UBound(Unique_Values)
            If Data(i) = Unique_Values(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i

ElseIf UserForm1.ListBox2.Selected(1) = True Then
    For i = LBound(Data) To UBound(Data)
        For j = LBound(Data) To UBound(Data)
            If j <> i And Data(i) = Data(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i
Else
    MsgBox "Select Either At Least Once or Exactly Once.", vbExclamation
End If

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

⧪ 6단계: UserForm 실행용 코드 작성

VBA 도구 모음에서 새 모듈(Module)을 삽입하고 다음 코드를 입력합니다.

Sub Run_UserForm()

UserForm1.Caption = "Keep Unique Values in Drop-Down List"

UserForm1.Label1.Caption = "Worksheet: "
UserForm1.Label2.Caption = "List Location: "
UserForm1.Label3.Caption = "Keep Unique Values that Appear: "

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Sheets(i).Name
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
        UserForm1.ListBox1.Selected(i) = True
        Exit For
    End If
Next i

UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.ListStyle = fmListStyleOption

UserForm1.ListBox2.AddItem "At Least Once"
UserForm1.ListBox2.AddItem "Exactly Once"

UserForm1.CommandButton1.Caption = "OK"

Load UserForm1
UserForm1.Show

End Sub

⧪ 7단계: UserForm 실행 (최종 결과)

이제 UserForm을 사용할 준비가 되었습니다. Run_UserForm이라는 매크로를 실행하세요.

워크시트에 UserForm이 로드됩니다.

먼저 드롭다운 목록이 있는 워크시트를 선택합니다. 여기서는 Sheet3입니다.

그다음 목록이 있는 셀 참조를 입력합니다. 여기서는 B3입니다.

마지막으로 At Least Once(최소 한 번 이상) 또는 Exactly Once(정확히 한 번) 중 하나를 선택합니다. 여기서는 At Least Once를 선택했습니다.

완성된 UserForm은 다음과 같습니다.

이제 OK를 클릭하면 선택한 기준에 따라 입력한 위치의 드롭다운 목록에서 중복 값이 제거됩니다.

추가 학습: 수식 기반 드롭다운 목록 만들기 (4가지 방법)

주의할 점

• 이 글에서는 드롭다운 목록의 중복 값 제거에 초점을 맞췄습니다. 드롭다운 목록을 처음부터 만드는 방법이나 목록 값 정렬 방법이 궁금하시다면 관련 아티클을 함께 참고하시기 바랍니다.

마무리

지금까지 엑셀 VBA를 활용해 드롭다운 목록에서 중복 값을 제거하고 고유 값만 남기는 다양한 방법을 살펴봤습니다. 궁금한 점이 있다면 언제든지 질문해 주세요. 더 많은 팁과 업데이트는 저희 사이트 ExcelDemy에서 확인하실 수 있습니다.

관련 아티클

• 엑셀에서 셀 값과 드롭다운 목록 연결하기 (5가지 방법)
• 엑셀 조건부 드롭다운 목록 (생성, 정렬, 활용)
• 엑셀에서 동적 종속 드롭다운 목록 만들기
• IF 함수로 엑셀 드롭다운 목록 만들기
• 엑셀에서 VLOOKUP과 드롭다운 목록 함께 사용하기