엑셀에서 중복 행을 열로 변환하는 4가지 쉬운 방법

엑셀에서 중복된 행을 열로 변환하는 가장 쉽고 효과적인 방법을 찾고 계신가요? 그렇다면 잘 찾아오셨습니다. 이 글에서는 중복 행을 빠르게 열로 변환할 수 있는 다양한 절차를 하나씩 자세히 살펴보겠습니다.

실습 파일 다운로드

아래 예제를 직접 따라 해보시려면 실습용 워크북을 내려받아 활용하시기 바랍니다.

엑셀에서 중복 행을 열로 변환하는 4가지 방법

여기서 사용할 데이터셋은 여러 제품의 판매 기록을 담고 있으며, 일부 행이 반복되어 있는 것을 확인할 수 있습니다. 아래 소개하는 방법들을 활용하면 이러한 중복 행을 손쉽게 열 형태로 변환할 수 있습니다.

이 글은 Microsoft Excel 365 버전을 기준으로 작성되었지만, 사용 중인 다른 버전에서도 동일하게 적용할 수 있습니다.

방법 1: 조건부 서식으로 중복 행 강조 후 열로 변환

첫 번째 방법은 조건부 서식을 활용해 중복 행을 먼저 시각적으로 강조하는 것입니다. 중복 항목이 어디에 있는지 한눈에 파악할 수 있으므로, 이후 Column1과 Column2 영역으로 해당 행을 옮겨 붙여넣기가 훨씬 수월해집니다.

단계별 진행:
➤ 데이터 범위를 선택한 뒤 홈 탭 >> 스타일 그룹 >> 조건부 서식 드롭다운 >> 셀 강조 규칙 >> 중복 값 옵션으로 이동합니다.

그러면 중복 값 대화 상자가 나타납니다.
➤ 포맷할 셀이 있는 위치 입력란에서 중복과 진한 빨간색 텍스트가 있는 밝은 빨간색 채우기 옵션을 선택합니다.
➤ 확인 버튼을 누릅니다.

이제 Walnut과 Apple 제품의 중복 행이 붉은색으로 강조됩니다. 덕분에 각 중복 그룹의 첫 번째 행을 어디로 옮길지 쉽게 판단할 수 있습니다.

➤ Ctrl 키를 누른 상태에서 Walnut과 Apple의 행을 각각 선택하고, Ctrl+C로 복사합니다.

➤ 행을 열 형태로 붙여넣으려면 목적지 셀에서 마우스 오른쪽 버튼을 클릭한 뒤, 다양한 붙여넣기 옵션 중에서 전치 옵션을 선택합니다.

이것으로 중복 행이 성공적으로 열로 변환됩니다.

함께 읽으면 좋은 글: 엑셀에서 조건에 따라 전치하기(예제 2개)

방법 2: COUNTIF 함수 활용하기

두 번째 방법은 Extra1 열에서 &(앰퍼샌드) 연산자로 영업사원 이름과 판매 값을 하나로 결합한 뒤, Extra2 열에서 COUNTIF 함수로 각 행의 반복 횟수를 세는 방식입니다. 마지막으로 반복되는 행만 골라 전치 옵션으로 열에 붙여넣습니다.

단계별 진행:
➤ 셀 D4에 아래 수식을 입력하여 각 행의 모든 셀을 하나로 합칩니다.

여기서 B4는 영업사원 이름(SalesPerson), C4는 판매 금액(Sales)이며, & 연산자가 두 값을 연결해 줍니다.

➤ Enter 키를 누른 후 채우기 핸들을 아래로 끌어 수식을 복사합니다.

그러면 Extra1 열에 각 행의 결합 결과가 모두 표시됩니다.

➤ 이번에는 각 행의 반복 횟수를 세기 위해 셀 E4에 다음 수식을 입력합니다.

여기서 D4는 기준값, $D$4:D4는 범위입니다. 이 범위는 수식이 적용된 시작 행부터 현재 행까지 확장되며, 예를 들어 8행에서는 범위가 $D$4:D8이 됩니다.

➤ Enter 키를 누르고 채우기 핸들을 아래로 드래그합니다.

이제 Extra2 열에 반복 횟수가 표시됩니다. Lara와 Howard가 2로 표시된 것은 판매 금액까지 포함해 정확히 두 번 등장했다는 의미입니다. 이제 해당 행들을 열로 변환해 보겠습니다.

➤ Ctrl 키를 누른 상태에서 Lara와 Howard의 행을 선택하고 Ctrl+C로 복사합니다.

➤ 목적지 셀에서 마우스 오른쪽 버튼을 클릭하고 붙여넣기 옵션 중 전치를 선택합니다.

완료하면 Lara와 Howard의 중복 행이 열 형태로 깔끔하게 변환됩니다.

함께 읽으면 좋은 글: 엑셀에서 행을 열로 변환하는 유용한 방법 5가지

관련 추천 자료

• 엑셀에서 n개 행씩 묶어 열로 변환하는 방법(쉬운 방법 2가지)
• 엑셀 파워 쿼리: 행을 열로 변환하기(단계별 가이드)
• 엑셀 VBA: 그룹별 여러 행을 열로 변환하기
• 엑셀에서 여러 열을 하나의 열로 합치는 방법(유용한 방법 3가지)

방법 3: IF 함수와 COUNTIFS 함수 조합 활용하기

세 번째 방법은 IF 함수와 COUNTIFS 함수를 조합하여 해당 행이 중복인지 자동으로 판별하는 것입니다. 이 방식을 사용하면 중복 행을 일일이 눈으로 찾을 필요 없이 자동으로 식별한 뒤 열로 변환할 수 있습니다.

단계별 진행:
➤ 셀 E4에 다음 수식을 입력합니다.

여기서 $B$4:$B$13, $C$4:$C$13, $D$4:$D$13은 첫 번째, 두 번째, 세 번째 검색 범위이고, $B4, $C4, $D4는 각각 첫 번째, 두 번째, 세 번째 기준값입니다.

• COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4) → 해당 행의 반복 횟수를 숫자로 반환합니다.
  결과 → 2
• COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1 은
  2>1 → 조건을 충족하면 TRUE, 아니면 FALSE를 반환합니다.
  결과 → TRUE
• IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1, "Repeated Row", "") 은
  IF(TRUE, "Repeated Row", "") → TRUE일 때 문자열 "Repeated Row"를, 그렇지 않으면 공백을 반환합니다.
  결과 → Repeated Row

➤ Enter 키를 누른 후 채우기 핸들을 아래로 드래그합니다.

그 결과 중복된 행에만 Repeated Row(반복 행)라는 텍스트가 표시됩니다.

➤ Ctrl 키를 누른 상태에서 Walnut과 Apple의 행을 선택하고 Ctrl+C로 복사합니다.

➤ 목적지 셀에서 마우스 오른쪽 버튼을 클릭하고 붙여넣기 옵션 중 전치를 선택합니다.

최종적으로 아래 그림처럼 중복 행이 열로 변환된 것을 확인할 수 있습니다.

함께 읽으면 좋은 글: 엑셀에서 조건에 따라 행을 열로 변환하는 방법(2가지)

방법 4: VBA 코드로 중복 행을 열로 변환하기

마지막 방법은 VBA 코드를 활용하는 것입니다. 이 코드를 실행하면 Walnut과 Apple 제품의 중복 행을 자동으로 열 형태로 변환할 수 있어, 데이터가 많을 때 특히 유용합니다.

단계별 진행:
➤ 개발 도구 탭 >> Visual Basic 옵션으로 이동합니다.

그러면 VBA 편집기(Visual Basic Editor)가 열립니다.
➤ 삽입 탭 >> 모듈 옵션을 선택합니다.

그러면 새로운 모듈(Module)이 생성됩니다.

➤ 아래 코드를 입력합니다.

Sub transform_same_rows()
Dim sht As Worksheet
Dim item, extra_column As Double
Dim storage_object As Object
Set storage_object = CreateObject("scripting.dictionary")
Set sht = Worksheets("VBA")
Total_column = 3
For increment = 4 To 13
item = Join(Application.Transpose(Application.Transpose( _
sht.Cells(increment, 2).Resize(1, Total_column))), "*")
If Not storage_object.exists(item) Then storage_object.Add item, New Collection
storage_object(item).Add sht.Cells(increment, Total_column + 1).Value
sht.Rows(increment).ClearContents
Next increment
increment = 4
For Each item In storage_object
sht.Cells(increment, 2).Resize(1, Total_column).Value = Split(item, "*")
extra_column = Total_column + 1
For Each str_value In storage_object(item)
sht.Cells(increment, extra_column) = str_value
extra_column = extra_column + 1
Next str_value
increment = increment + 1
Next item
For increment = 4 To 13
If sht.Cells(increment, 5).Value = sht.Cells(increment, 4).Value Then
sht.Range(Cells(increment, 2), Cells(increment, 4)).Copy
sht.Cells(increment, 6).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=True
End If
Next increment
End Sub

코드 설명: 먼저 sht를 Worksheet로, item과 extra_column을 Double로, storage_object를 Object로 선언한 뒤, sht를 VBA 워크시트로 설정하고 Total_column에 3을 할당했습니다.
storage_object에는 항목을 저장하는 데 유용한 Dictionary 개체가 지정됩니다.
이후 FOR 루프를 통해 4행부터 13행까지 순회하면서, TRANSPOSE 함수를 두 번 적용하고 JOIN 함수로 행의 모든 셀을 구분자 *로 연결해 item 변수에 저장합니다. 그런 다음 항목을 storage_object에 추가하고 원본 행을 모두 비웁니다.
다음으로 SPLIT 함수로 고유 행을 세 개의 열에 다시 입력하고, 중복 행의 판매 값들은 Sales 열 바로 옆 열에 차례대로 추가됩니다.
마지막으로 IF 문으로 판매 값과 인접 셀의 값을 비교하고, 조건이 충족되면 해당 행을 복사한 뒤 전치하여 붙여넣습니다.

➤ F5 키를 눌러 매크로를 실행합니다.
그러면 데이터셋에서 중복 행이 정리되면서 동시에 열 형태로 변환된 결과를 얻을 수 있습니다.

함께 읽으면 좋은 글: 엑셀 VBA로 행을 열로 변환하는 방법(예제 4가지)

연습 섹션

직접 실습해 보실 수 있도록 Practice라는 이름의 시트에 아래와 같은 연습 섹션을 마련해 두었습니다. 배운 내용을 스스로 적용해 보시기 바랍니다.

마무리

지금까지 엑셀에서 중복 행을 열로 변환하는 다양한 방법을 알아보았습니다. 조건부 서식, COUNTIF, IF+COUNTIFS 조합, VBA까지 상황에 맞는 방법을 선택해 활용해 보시기 바랍니다. 이 글이 도움이 되었기를 바라며, 궁금한 점이나 추가하고 싶은 내용이 있다면 댓글로 자유롭게 남겨주세요.

추천 학습 자료

• 엑셀에서 전치하는 쉬운 방법 5가지
• 엑셀에서 그룹별 여러 행을 열로 변환하기
• 엑셀에서 열을 행으로 변환하는 방법(6가지)
• 파워 쿼리로 엑셀 열을 행으로 변환하기
• 엑셀에서 셀 값에 따라 열을 행으로 변환하는 방법
• 엑셀 VBA로 여러 열을 행으로 변환하기(2가지 방법)
• 열 데이터를 작은따옴표가 포함된 쉼표 구분 목록으로 변환하는 방법
• 엑셀 VBA로 전치하는 방법(3가지)