같은 내용을 묶어서 합치기2(배열)

안녕하세요.

질문을 해결하기 위해 여러 가지 방법으로 처리를 해 봤습니다.

피벗 테이블을 이용하거나 MATCH 함수와 TEXTJOIN 함수를 이용해 처리해 봤습니다.

 

같은 내용을 묶어서 합치기(피벗 테이블, MATCH, TEXTJOIN)

처리된 결과 내용이 원하는 형태와 완전히 같지는 않고, 좀 더 작업을 해야 하는 중간 단계로 나와서 또 다른 방법을 시도해 봤습니다.

VBA를 이용해 처리하는 방법이었습니다.

같은 내용을 묶어서 합치기(VBA)

이 방법도 앞의 방법과 같이 완전하지 않았는데, 출판사별로 묶어 분야를 나열하는데, 같은 분야가 여러 번 나타나는 문제가 있었습니다.

 

그래서 출판사별로 묶는 작업을 하기 전에 출판사의 분야가 같은 경우는 출력 내용에서 제외하도록 만들겠습니다.

이 작업을 위해 배열을 만들어 중복되지 않는 작업만 저장하는 사전 작업을 하겠습니다.

 

실습 파일 다운로드

같은 내용을 묶어서 합치기2(배열)_실습.xlsm

0.02MB

완성 파일 다운로드

같은 내용을 묶어서 합치기2(배열)_완성.xlsm

0.02MB

 

실습 파일에 먼저 작업을 한 내용이 남아 있어 필요 없는 부분을 지우겠습니다.

E열과 F열을 선택해 삭제 합니다.

단축키 <Alt + F11>키를 눌러 VBE를 실행합니다.

입력된 코드 대부분을 다시 사용할 수는 있겠지만 수정해야 할 부분이 많으니 지우고 새로 쓰는게 헷갈리지 않는 방법입니다.

다만 첫번째와 두번째 줄인 [E1] 셀과 [F1] 셀에 '출판사'와 '분야'를 출력하는 부분은 남겨두겠습니다.

Public Sub 분야요약만들기()
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    
End Sub

출판사와 분야를 저장할 배열 자료를 선언합니다.

처리해야 할 자료가 얼마나 많을지 알 수 없어 가장 많을 수 있는 수로 100을 지정했습니다.

더 많을 수 있다면 더 크게 정해야 합니다.

Public Sub 분야요약만들기()
    Dim 자료(100, 2)
    
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    
End Sub

변수 선언은 눈에 바로 띌 수 있도록 프로시저 제일 첫 위치에 합니다.

꼭 그렇게 해야 하는 것은 아니지만 규칙을 지키면 앞으로 할 뻔한 실수를 미리 예방할 수 있는 좋은 습관이 됩니다.

 

[A2] 셀부터 차례대로 훑어갈 변수 i에 2를 대입합니다.

Do~Loop문으로 반복해 등록하도록 코드를 작성합니다.

Public Sub 분야요약만들기()
    Dim 자료(100, 2)
    
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    
    Do
    
        i = i + 1
    Loop While Range("A" & i).Value <> ""
    
End Sub

배열에 넣을 위치를 정하는 변수 j에 1을 대입합니다.

Do~Loop문 안에서 배열에 값을 저장하는 코드를 작성합니다.

셀에 있는 값을 모두 배열에 저장하는 형태로 일단 작성합니다.

Public Sub 분야요약만들기()
    Dim 자료(100, 2)
    
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    j = 1
    
    Do
        자료(j, 1) = Range("A" & i).Value
        자료(j, 2) = Range("C" & i).Value
                
        i = i + 1
        j = j + 1
    Loop While Range("A" & i).Value <> ""
    
End Sub

 

작성된 코드를 수정해야 합니다.

출판사와 분야 목록을 바로 저장하는 것이 아니라 이미 배열에 저장된 값을 확인해 새로운 값일 때만 저장하도록 작성합니다.

배열에 저장된 값을 반복해 처리하도록 For~Next문을 추가하고, If문으로 비교합니다.

같은 값이 있는 경우와 없는 경우를 구분하기 위해 변수 확인에 True를 대입합니다.

Public Sub 분야요약만들기()
    Dim 자료(100, 2)
    
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    j = 1
    확인 = True
    
    Do
        For k = 1 To j
            If Range("A" & i).Value = 자료(k, 1) And Range("C" & i).Value = 자료(k, 2) Then
                확인 = False
                
                Exit For
            End If
        Next k
        
        If 확인 Then
            자료(j, 1) = Range("A" & i).Value
            자료(j, 2) = Range("C" & i).Value
            
            j = j + 1
        End If
                
        확인 = True
                
        i = i + 1
    Loop While Range("A" & i).Value <> ""
    
End Sub

이제 배열 자료에 출판사와 분야가 중복되지 않는 항목만 저장되었습니다.

 

사전 처리 작업이 마무리 되었습니다.

이제 반복문 For~Next문을 이용해 같은 출판사의 분야를 합치면서 출력합니다.

새 변수를 사용할 수도 있지만 앞부분에서 사용하고 사용용도가 다 된 변수 i를 출력위치를 나타내는 용도로 재활용하기 위해 2를 대입합니다.

같은 출판사인지 여부를 확인하는 변수 확인에 True를 대입합니다.

배열 자료의 전체 크기는 100이지만 실제 자료가 몇 번째까지 들어 있는지 확인하는 변수는 j이며 j-1까지 자료가 들어 있습니다.

Public Sub 분야요약만들기()
    Dim 자료(100, 2)
    
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    j = 1
    확인 = True
    
    Do
        For k = 1 To j
            If Range("A" & i).Value = 자료(k, 1) And Range("C" & i).Value = 자료(k, 2) Then
                확인 = False
                
                Exit For
            End If
        Next k
        
        If 확인 Then
            자료(j, 1) = Range("A" & i).Value
            자료(j, 2) = Range("C" & i).Value
            
            j = j + 1
        End If
                
        확인 = True
                
        i = i + 1
    Loop While Range("A" & i).Value <> ""
    
    i = 2
    확인 = True
    
    For k = 1 To j - 1
        For l = 2 To i
            If 자료(k, 1) = Range("E" & l).Value Then
                Range("F" & l).Value = Range("F" & l).Value & 자료(k, 2) & ", "
                
                확인 = False
                Exit For
            End If
        Next l
        
        If 확인 Then
            Range("E" & i).Value = 자료(k, 1)
            Range("F" & i).Value = 자료(k, 2) & ", "
            
            i = i + 1
        End If
        
        확인 = True
    Next k
    
End Sub

 

출력된 내용 끝 부분의 ', '(쉼표와 빈칸)을 지우기 위한 코드를 추가합니다.

출력이 몇 번째까지 되어 있는지 확인하는 변수는 i이고, i-1 위치까지 출력되어 있습니다.

Public Sub 분야요약만들기()
    Dim 자료(100, 2)
    
    Range("E1").Value = "출판사"
    Range("F1").Value = "분야"
    
    i = 2
    j = 1
    확인 = True
    
    Do
        For k = 1 To j
            If Range("A" & i).Value = 자료(k, 1) And Range("C" & i).Value = 자료(k, 2) Then
                확인 = False
                
                Exit For
            End If
        Next k
        
        If 확인 Then
            자료(j, 1) = Range("A" & i).Value
            자료(j, 2) = Range("C" & i).Value
            
            j = j + 1
        End If
                
        확인 = True
                
        i = i + 1
    Loop While Range("A" & i).Value <> ""
    
    i = 2
    확인 = True
    
    For k = 1 To j - 1
        For l = 2 To i
            If 자료(k, 1) = Range("E" & l).Value Then
                Range("F" & l).Value = Range("F" & l).Value & 자료(k, 2) & ", "
                
                확인 = False
                Exit For
            End If
        Next l
        
        If 확인 Then
            Range("E" & i).Value = 자료(k, 1)
            Range("F" & i).Value = 자료(k, 2) & ", "
            
            i = i + 1
        End If
        
        확인 = True
    Next k
    
    For k = 2 To i - 1
        Range("F" & k).Value = Left(Range("F" & k).Value, Len(Range("F" & k).Value) - 2)
    Next k
End Sub

코딩은 마무리 되었습니다.

<F5>키를 눌러 실행하고, 엑셀 창으로 가서 결과를 확인합니다.

제대로 실행된 것을 확인했습니다.

코드는 두 부분으로 작성되어 있습니다.

첫번째는 출판사와 분야를 중복되지 않도록 배열에 저장하는 부분입니다.

두번째는 배열에 있는 값을 같은 출판사에 추가해 넣는 부분입니다.

코드가 반복문을 중첩해 두번이나 실행되도록 작성되어 있어 정리하려는 자료가 많으면 처리하는 시간이 오래 걸릴 수 있는 알고리즘입니다.

최대 100개 정도의 자료가 있다고 작성된 것이라 지금은 실행하는데 문제가 없습니다. 

 

수고하셨습니다.

 

2020.06.26 - 같은 내용을 묶어서 합치기3(중복된 항목 제거, 정렬, VBA)

같은 내용을 묶어서 합치기3(중복된 항목 제거, 정렬, VBA)