[엑셀, VBA] 같은 조건의 문자열 찾아 합치기

다음 그림과 같은 작업을 하고 싶을 때가 있습니다.

왼쪽처럼 학생들의 신청과목을 한 열에 한 과목씩 일렬로 받아둔 것을 오른쪽 그림처럼 한 학생의 모든 신청과목을 한 줄에 입력하는 작업입니다. VLOOKUP함수를 써서 10101을 찾아 한 칸 오른쪽에 있는 값을 불러오면, 맨 위에 있는 값만 가져오기 때문에 "영어"만 출력됩니다. 앞으로 엑셀에서 VLOOKUP함수가 두 번째, 세번째 일치하는 값도 찾을 수 있도록 옵션을 제공한다면 간단히 해결되겠지만, 그 전에는 이런 작업을 하려면 VBA를 사용해야할 듯 합니다.

다음은 ConcatText라는 사용자정의 함수를 만드는 VBA입니다. Alt + F11 하셔서 삽입-모듈에서 다음을 붙여넣으세요.

Function ConcatText(ByVal 범위 As Range, 구분 As String) As String
 Dim strTemp() As String
 Dim rng As Range
 Dim i As Integer

For Each rng In 범위
     If rng = 구분 Then
          ReDim Preserve strTemp(i)
         strTemp(i) = rng.Next.Value
          i = i + 1
     End If
Next rng

 ConcatText = Join(strTemp, " / ")

End Function

E2셀에 들어가는 수식은 다음과 같습니다.

=ConcatText($A$2:$A$9,D2)

A2:A9의 범위중에서 D2의 값과 같은 값을 찾아, 그 한칸 오른쪽에 있는 문자열을 모두 합쳐줍니다.

출처는 여기입니다. 답변을 하신 분은 네이버에서 엑셀부문 1위하시는 분인데, 정말 대단하신 분입니다. 이 분이 답변하신 내용들만 훑어보아도 웬만한 문제는 다 해결되지 않을까 생각합니다.


p.s. VBA코드 중에서 ReDim Preserve 라는 것은 처음 봅니다. 좀 더 연구가 필요한 것 같습니다.
참고자료1

 Dim strTemp() As String <-- 이 부분에서 괄호()가 있다는 것은 strTemp라는 변수를 배열로 선언했다는 거군요. 그리고 괄호안에 숫자가 들어있지 않은 것은 위의 그림에서 10101이라는 학번이 몇 개 나올지 모르니 strTemp변수의 크기(갯수)를 정하지 않은 동적배열로 선언했다는 거구요.

그리고 ReDim Preserve strTemp(i) 이 부분에서 ReDim은 strTemp 배열의 크기를 i의 수만큼으로 재조정하며,
Preserve가 붙은 것은 그 이전단계에서 변수 strTemp에 넣어두었던 값은 계속유지한다는 말입니다.

' strTemp 배열의 크기(갯수) 조정 / 이전 값은 유지(처음에는 이전값이 없음)
          ReDim Preserve strTemp(i) 
' strTemp(1)의 값에 해당 셀의 오른쪽에 있는 값을 넣어줌
         strTemp(i) = rng.Next.Value
'다음 셀이 또 "구분"과 같을 때를 대비해 strTemp(2)로 만들어주기 위해 i값을 증가시킴
          i = i + 1

그 다음,  rng값이 "구분"과 같은 셀을 또 만나게 되면,
' strTemp 배열의 크기(갯수)를 2로 조정 / strTemp(1)에 들어있던 값은 유지
          ReDim Preserve strTemp(i) 
' strTemp(2)의 값에 해당 셀의 오른쪽에 있는 값을 넣어줌
         strTemp(i) = rng.Next.Value
'다음 셀이 또 "구분"과 같을 때를 대비해 strTemp(3)로 만들어주기 위해 i값을 증가시킴
          i = i + 1

이런식으로 반복되게 되므로, 주어진 범위 안에 "구분"과 같은 값이 나오는 횟수만큼 strTemp의 배열이 만들어지며, (예를 들어 100개가 "구분"과 같은 값이라면 strTemp(1)부터 strTemp(100)까지의 배열이 만들어짐) 각각의 배열안에는 "구분"과 같은 각각의 셀의 오른쪽에 들어있는 값이 들어있게 됩니다.

마지막으로, Join함수를 이용해 배열안에 있는 모든 문자열을 합치되, 문자열끼리 구분자는 " / "로 하여, ConcatText함수의 결과값으로 내보냅니다.
ConcatText = Join(strTemp, " / ")

휴~ 재미있기는 한데, 어렵네요 ^^