아이엑셀러 닷컴

완성 예

지금까지는 VBA로 중복된 항목을 제거할 때에는 컬렉션 개체^{Collection Object}를 주로 사용했습니다. 고유 아이템을 추출하고 끝나는 경우라면 크게 어려운 점은 없지만 데이터를 정렬하려면 꽤나 복잡한 과정을 거쳐야 했습니다. 오늘 살펴볼 이 방법(VBA 코딩)을 이용하면 간단하게 처리할 수 있습니다.

중복 제거와 정렬(내림차순)이 동시에 처리

방법 1: 엑셀 기본 기능으로 해결

먼저, 엑셀의 기본 기능을 이용해서 해결해 보겠습니다.

고유 값을 추출할 데이터를 복사해서 빈 영역(여기서는 K1 셀)에 붙여넣기 합니다.
데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거] 명령을 클릭합니다.

[중복된 항목 제거] 명령 사용

[중복 값 제거] 대화상자에서 중복 값을 제거할 열을 선택하고 [확인] 버튼을 클릭하면 중복 값이 제거된 고유 값들만 화면에 표시됩니다.
정렬을 하려면 데이터 내부의 셀을 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [정렬] 명령을 클릭합니다. [정렬] 대화상자에서 [기준 추가] 버튼을 이용하여 정렬할 기준을 추가한 다음 [확인]을 누르면 데이터가 정렬됩니다.

방법 2: ArrayList 개체 이용

VBA를 이용하여 '중복 항목 제거'와 '정렬'을 한꺼번에 처리해 보겠습니다. 지금까지 강의에서 주로 다루었던 '컬렉션 개체'가 아니라 ArrayList라는 개체를 사용합니다. 이 개체를 이용하면 정렬을 매우 간단하게 할 수 있습니다.

워크시트 상태에서 Alt + F11을 눌러 [Visual Basic Editor]를 호출합니다. [Visual Basic Editor]에서 [삽입] - [모듈] 메뉴를 선택하여 모듈을 삽입하고 코드를 작성합니다. 코드를 외우려 할 필요 없습니다. 진행되는 흐름만 놓치지 말고 따라 가세요.

Sub removeDuplicates()
  Dim shtX As Worksheet
  Dim rTbl As Range      ''' 작업 대상 영역
  Dim rWrite As Range    ''' 결과를 표시할 셀
  Dim rRow As Range      ''' 행 단위 순환 변수
  Dim oList As Object    ''' ArrayList 개체에 접근할 변수
  Dim sX As String       ''' 셀 내용을 담아둘 변수
  Dim iCol As Integer    ''' rTbl 영역의 열 수
  Dim i As Integer       ''' 순환 변수

  Set oList = CreateObject("System.Collections.ArrayList")
      ''' ArrayList 개체 생성

  Set shtX = Worksheets("Sample")
  Set rTbl = shtX.Range("A1").CurrentRegion
  Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)
      ''' 제목을 제외한 영역으로 범위 재지정

  iCol = rTbl.Columns.Count
  Set rWrite = rTbl.Cells(1).Offset(, iCol + 2)
      ''' 결과를 표시할 위치 지정

  rWrite.CurrentRegion.Clear
  
  For Each rRow In rTbl.Rows    ''' 행 단위로 접근하여 처리
      sX = Join(Application.Transpose(Application.Transpose(rRow)), ",")
        ''' 행 내의 각 셀 내용(문자열)을 콤마로 구분하여 연결
        ''' 열 방향 데이터를 행 방향 데이터로 변환하기 위해
        ''' Transpose 함수를 두 번 사용한 점에 유의

      If Not oList.contains(sX) Then oList.Add sX
        ''' oList에 sX가 없으면 oList에 목록 추가
  Next
  
  oList.Sort    ''' 오름차순 정렬
  If MsgBox("내림차순으로 정렬할까요?", vbYesNo) = vbYes Then
      oList.Reverse    ''' 내림차순 정렬
  End If        
  
  ''' 지정한 영역(rWrite)에 결과를 표시
  With rWrite
      .Offset(-1).Resize(, iCol) = Array("대분류", "중분류", "품목코드")
      For i = 0 To oList.Count - 1
          .Offset(i).Resize(, iCol) = Split(oList.Item(i), ",")
            ''' Split 함수를 이용하여 문자열 분리
            ''' 분리된 결과를 지정한 셀에 나누어 표시
      Next
  End With
End Sub

CODE

프로시저 내부에 커서를 두고 [실행] 버튼을 눌러 코드를 실행합니다. 내림차순으로 정렬할 지 여부를 묻는 메시지 상자에서 [아니오]를 선택하면 고유 값들만 오름차순으로 정렬됩니다.

ArrayList 개체 사용 결과

결과는 '방법 1'의 그것과 같습니다. VBA로 코딩을 하면 엑셀 메뉴를 이리저리 오가거나 범위를 일일이 지정하지 않고도 처리할 수 있습니다. 복잡하고 반복되는 업무들을 자동화할 수 있음은 두말 할 필요도 없죠.

이번 강의에서는 중요한 함수가 몇 개 사용되었습니다. 배열에 포함된 문자열을 연결할 때 사용하는 Join, 그 반대 역할을 하는 Split(이상 VBA 함수), 세로 범위를 가로로(혹은 가로 범위를 세로로) 변환하는 Transpose(워크시트 함수) 등이 대표적입니다. 매우 중요한 함수들이므로 눈도장을 확실히 찍어두시기 바랍니다.

#중복제거 #ArrayList #Transpose #Join #Split #Offset #Resize

들어가기 전에 ㅡ 이발소와 편지

완성 예

방법 1: 엑셀 기본 기능으로 해결

방법 2: ArrayList 개체 이용