• 최초 작성일: 2023-09-04
  • 최종 수정일: 2023-09-04
  • 조회수: 2,071 회
  • 작성자: 권현욱 (엑셀러)
  • 강의 제목: VB0264 ㅡ 여러 열에 있는 데이터 중복 제거와 정렬 동시에 하기
엑셀러 권현욱

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

아마 예닐곱 살 쯤 되었을 것이다. 아버지와 함께 동네 이발소에 처음으로 갔던 나이는. 팔걸이가 달린 크고 투박한 의자가 3개 있었고, 한켠에는 머리를 감을 수 있는 세면대가 2개 있었다. 두 공간은 빨랫줄에 걸린 타월로 구분되었다. 이발 후 본인이 직접 머리를 감으면 몇십 원 저렴했다.

어른들처럼 의자에 두 팔을 팔걸이에 척 올리고 뽀대(?)나게 이발하고 싶었지만, 이발사 아저씨는 빨래판처럼 생긴 나무판때기를 팔걸이에 가로로 걸쳐주셨고, 난 그 위에 앉아야만 했다. 널빤지 위에만 앉으면 희안하게도 잠이 쏟아졌다. 이발사 아저씨는 가위질 하는 틈틈이 내 머리통을 일으켜 세우기에 바빴다. 푸른 면도날이 사각거리는 소리를 내며 하는 손면도와, 손바닥 그득 스킨로션을 덜어 멋지게 포말을 일으키며 얼굴을 마사지하는 동작은 아직 언감생심이었다.

'이발소'라는 단어는 내게 아버지 냄새(미스쾌남 셰이브 로션), 휴일 오후의 노곤함, 짙푸른 셀로판 필름을 투과해 순해진 빛으로 각인되어 있다.

20년 가까이 같은 이발소를 애용한다. 간판, 진열장, 의자, 정면 거울 위 액자와 사진들도 예전 모습 그대로다. 이발사 아저씨는 말할 것도 없다. 아주 가끔 헤어샵을 찾기도 했지만 어색하고 불편했다. 회귀 본능에 충실한 연어마냥 다음 발걸음은 자연스레 이발소로 향했다.

이발소에서는 굳이 말을 지어내려 애쓰지 않는다. 드나들 때 하는 간단한 인사말 정도만 나누는 것이 대화의 전부인 경우도 드물지 않다. 머리 모양을 어떻게 할 지, 면도를 할 것인지, 머리를 감을 지 등에 대해서는 말하지 않아도 안다. 침묵이 주는 부자연스러움은 찾아볼 수 없다.

P와는 중학교와 대학교 시절을 함께 했고 비슷한 시기에 서울 생활을 시작했다. 시대정신을 공유하며 비슷한 삶의 궤적을 가진 35년지기 친구다. 또 다른 35년지기 친구 R과 함께 최근까지도 수시로 만나 회포를 풀곤 했다. 그랬던 P가 제때 연락이 닿지 않는 경우가 잦아지더니 급기야 잠수를 탔다. R과 나는 불안한 어림짐작에 추측을 쌓아갔다.

서랍 정리를 하다가 P로부터 받은 1990년대 초반 언저리 무렵의 편지를 무더기로 찾았다. P는 달필이 아닌 걸로 정평(?)이 나 있었는데, 또박또박 쓰려고 노력한 흔적이 고스란히 느껴져 공연히 웃음이 났다.

로딩 중...

(이미지 출처: 아이엑셀러 닷컴)

P를 다시 만난 건 5개월 만이었다. 모처에서 새로운 인생의 막을 열고 있었다. 호텔방에서 새벽까지 왁자하게 회포를 풀며 억측과 짐작, 약간의 서운함 따위를 비워냈다.

뭔가를 새로 시작하는 것이 날로 녹록지 않은 시기요 나이다. 새로운 발걸음을 내디딘 P를 응원한다.

Table Of Contents

  • 멤버십 회원에게는 예제 파일을 보내드립니다 ( 멤버십 보러 가기).

  • VBA에 익숙하지 않은 분들은 전반적인 흐름을 이해하는 데 주안점을 두고 보시기 바랍니다.

완성 예

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

로딩 중...

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

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

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

  1. 고유 값을 추출할 데이터를 복사해서 빈 영역(여기서는 K1 셀)에 붙여넣기 합니다.

  2. 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거] 명령을 클릭합니다.

    3. 로딩 중...

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

  3. [중복 값 제거] 대화상자에서 중복 값을 제거할 열을 선택하고 [확인] 버튼을 클릭하면 중복 값이 제거된 고유 값들만 화면에 표시됩니다.

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

    5. 로딩 중...

방법 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(워크시트 함수) 등이 대표적입니다. 매우 중요한 함수들이므로 눈도장을 확실히 찍어두시기 바랍니다.