엑셀 VBA로 자료 처리 - 신청자, 회원목록 비교 3

아직 끝나지 않았습니다. ^^;

 

한동안 연락이 없어 다 끝난 줄 알았더니 갑작스럽게 연락이 왔습니다.

나머지 작업이 있다고.

 

한동안 연락이 없어 수작업으로 마무리 작업을 하나 싶었는데, 그동안 수작업을 하긴 했지만 아직 마무리된 것은 아니었다고 합니다.

 

두가지 작업을 요청했는데, 지급된 내역 중 중복된 항목을 걸러내 달라는 것과 지급 내역 중 전화번호가 없는 항목을 찾아 전화번호를 나타내 달라는 것이었습니다.

 

중복된 항목을 찾는 방법은 어렵지 않습니다.

 

COUNTIF 함수로 중복값을 찾는 수식을 작성한 후 고급 필터로 해당하는 레코드를 뽑아 별도 공간에 표시하면 됩니다.

 

이때 고려해야할 부분이 있는데, 이떤 값을 기준으로 찾는가 입니다.

 

이름은 중복신청된 경우가 있고, 또 동명이인도 있습니다.

신청한 계좌번호를 기준으로 찾을 수도 있는데, 다른 계좌로 신청을 한 경우도 있기 때문에 어느 기준으로 해도 완전하지 않습니다.

 

결국 타협을 해야 했고, 연락해서 계좌번호를 기준으로 중복 여부를 판단하기로 했습니다.

 

전화번호를 찾는 부분도 타협이 필요했는데, 그나마 오류가 적을 계좌번호를 기준으로 찾아 전화번호를 표시하기로 했습니다.

 

또 처리할 부분이 하나 있었는데, 계좌번호는 같지만 전화번호를 다르게 신청한 경우도 있기 때문에 한 항목에 전화번호가 꼭 하나가 아니라는 문제가 있었습니다.

여러 개의 전화번호가 있는 경우엔 한 셀에 여러 개의 전화번호를 모두 표시하도록 구현했습니다.

Public Sub 전화번호없는경우찾아나타내기()
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim strAddr As String
    Dim C As Range
    Dim i As Long
    
    Dim P(1 To 50) As String     ' 찾은 전화번호 저장
    Dim CHUL(1 To 50) As String  ' 중복검사해 출력 전화번호 저장
    Dim 확인 As Boolean
    Dim 출력 As String

    시트 = "원래"
    열 = "I"
    비교열 = 15  ' O열

    Set sht1 = ActiveSheet
    Set sht2 = Sheets(시트)

    시작 = 4
    끝 = sht1.Range("A" & 시작).End(xlDown).Row
    열번호 = Range(열 & "1").Column ' 열에 해당하는 숫자 구함 I : 9

    시작시간 = timer
    Application.ScreenUpdating = False

    For i = 시작 To 끝
        If sht1.Range("H" & i).Value = "" Then
            j = 1
            
            Set C = sht2.Columns(비교열).Find(sht1.Cells(i, 열번호).Value, LookAt:=xlWhole)

            If Not C Is Nothing Then
                strAddr = C.Address
                Do
                    If C.Offset(0, -13).Value = sht1.Range(열 & i).Offset(0, -7).Value Then
                        P(j) = C.Offset(0, 1).Value
                        j = j + 1
                    End If

                    Set C = sht2.Columns(비교열).FindNext(C)
                Loop While Not C Is Nothing And C.Address <> strAddr
            End If
            
            m = 1
            확인 = True
            
            For k = 1 To j - 1
                For l = 1 To m
                    If P(k) = CHUL(l) Then
                        확인 = False
                        Exit For
                    End If
                Next l
                
                If 확인 Then
                    CHUL(m) = P(k)
                    
                    m = m + 1
                End If
                
                확인 = True
            Next k
            
            For k = 1 To m - 1
                출력 = 출력 & CHUL(k) & ", "
            Next k
            
            If Len(출력) > 2 Then
                출력 = Left(출력, Len(출력) - 2)
            End If
            
            sht1.Range("H" & i).Interior.ColorIndex = 19
            sht1.Range("H" & i).Value = 출력
            
            출력 = ""
            
            For k = 1 To j - 1
                P(k) = ""
                CHUL(k) = ""
            Next k
        End If

        DoEvents
    Next i

    Application.ScreenUpdating = True
    Beep
    MsgBox "총 " & format(timer - 시작시간, "#,##0") & "초 소요"
End Sub

반복문 안에 다시 반복문을 여러 번 반복해서 사용하는 실행 속도가 염려되는 코드입니다.

당장 생각난 아이디어를 그대로 구현했습니다.

 

계좌번호를 기준으로 작업해 찾을 항목이 많지 않고, 찾은 전화번호에서 중복값을 제거해 나타낼 때도 전화번호가 몇 개 되지 않아서 실행속도는 문제가 없었습니다.

 

가장 긴 시간이 걸린 작업도 18초 안에 마무리 되었습니다.

 

요청에 대한 응답은 다 했지만, 처음 작업을 시작할 때 전체를 보고 작업하지 않은게 아쉬움으로 남습니다.

당장 요청한 작업을 처리해 넘기며 나중에 문제가 생길걸 예상했는데, 급하게 처리해야 한다는 이유로 당장 처리해야할 일에 급급했었죠.

 

다시 한다고 해도 잘할 수 있을지 확신할 수는 없지만 전체 업무를 미리 파악해 처리할 수 있는 경험이 되었단 것은 확실합니다.

 

재밌었습니다.

아직 다 끝난지 확실하지는 않지만 ...