엑셀 중복값 추출 쉽게 하는 방법
얼마 전 주문 명단을 엑셀로 받았는데, 같은 주문번호가 여러 줄에 섞여 있더라고요. 눈으로 찾기에는 행이 너무 많고, 중복 제거를 누르자니 어떤 행이 겹쳤는지 먼저 확인하고 싶었습니다. 이럴 때는 ‘중복된 값을 모두 볼지’, ‘두 번째로 나온 값부터 볼지’를 정하면 방법을 고르기 쉬워집니다.
중복값을 모두 표시하고 싶다면 COUNTIF
A열에 주문번호가 있고 데이터가 2행부터 100행까지 있다고 가정해 볼게요. B2 셀에 다음 수식을 넣고 마지막 행까지 채웁니다.
=IF(AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1),"중복","")
COUNTIF는 A2의 주문번호가 전체 범위에 몇 번 나오는지 셉니다. 두 번 이상 나오면 B열에 ‘중복’이라고 표시하죠. 예를 들어 1024번 주문이 7행과 38행에 있다면 두 행 모두 표시됩니다. 빈칸은 중복으로 잡히지 않도록 A2<>"" 조건을 넣었습니다.
이제 제목 행을 포함해 표를 선택하고 ‘데이터’ 탭에서 ‘필터’를 켜세요. B열 필터에서 ‘중복’만 선택하면 해당 행만 남습니다. 이름, 날짜, 금액처럼 옆 열의 정보까지 함께 확인할 수 있어서 실무에서는 이 방식이 편합니다. 결과를 따로 보관하려면 필터로 보이는 행을 복사해 새 시트에 붙여 넣으면 됩니다.
두 번째 등장부터 찾으려면 범위를 바꾸면 됩니다
중복된 항목 전체가 아니라 나중에 입력된 행만 필요한 때도 있죠. 같은 주문번호의 첫 행은 원본으로 두고, 그 뒤에 입력된 행만 확인하는 경우입니다. 이때는 B2에 아래 수식을 사용합니다.
=IF(AND(A2<>"",COUNTIF($A$2:A2,A2)>1),"재등장","")
앞쪽의 $A$2는 고정하고, 뒤쪽의 A2는 아래로 내려가며 A3, A4로 바뀝니다. 그래서 현재 행까지 같은 값이 몇 번 나왔는지 셀 수 있습니다. 1024번 주문이 7행과 38행에 있다면 7행은 비어 있고 38행에만 ‘재등장’이 표시됩니다. 같은 값이 세 번 나오면 두 번째와 세 번째 행이 모두 표시됩니다.
범위를 선택할 때는 실제 데이터의 마지막 행에 맞춰 100을 바꿔주세요. 새 행이 자주 추가되는 명단이라면 표로 만들어 관리하면 수식과 필터를 이어서 쓰기 수월합니다.
함수 한 개로 별도 목록을 뽑는 방법
Microsoft 365나 Excel 2021 이상을 쓴다면 FILTER 함수로 중복된 값만 다른 위치에 표시할 수 있습니다. A2:A100에 데이터가 있을 때 빈 셀에 다음 수식을 입력합니다.
=FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1),"중복 없음")
조건에 맞는 값이 아래로 펼쳐집니다. 같은 값이 두 번 있으면 결과에도 두 번 나타나므로, 어떤 값이 몇 건 겹쳤는지 확인하기 좋습니다. 중복된 항목의 이름을 한 번씩만 보고 싶다면 수식을 UNIQUE로 감싸면 됩니다.
=UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1),"중복 없음"))
다만 이 수식은 중복 항목이 없을 때 ‘중복 없음’이라는 문구도 하나의 결과로 보여줍니다. 또 결과가 펼쳐질 자리에는 다른 값이 없어야 합니다. FILTER 함수가 없는 엑셀 버전이라면 앞의 COUNTIF와 필터 조합을 쓰면 됩니다. 주문번호뿐 아니라 해당 행의 다른 열도 함께 뽑으려면 첫 번째 인수를 A2:C100처럼 넓히고, 중복을 판단할 기준 범위는 A2:A100으로 유지하세요.
색으로 확인할 때와 삭제할 때는 구분하세요
목록을 따로 만들 필요 없이 위치만 빨리 찾고 싶다면 A열 데이터를 선택한 뒤 ‘홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값’을 선택하면 됩니다. 겹치는 셀이 색으로 표시돼 원본 표를 훑기 좋습니다. 다만 색을 칠하는 기능이므로 중복 행을 별도 목록으로 추출해 주지는 않습니다.
‘데이터 → 중복된 항목 제거’는 이름 그대로 행을 제거하는 기능입니다. 예를 들어 주문번호가 같아도 주문 날짜가 다른 두 행이 있다면, 어떤 열을 기준으로 선택했는지에 따라 남는 결과가 달라집니다. 삭제하기 전에는 원본을 복사해 두고 비교 기준 열을 확인하는 편이 안전합니다.
중복처럼 보이는데 수식에 잡히지 않는다면 값 앞뒤의 공백부터 확인해 보세요. ‘김민수’와 ‘김민수 ’처럼 끝에 공백이 붙은 값은 서로 다르게 취급될 수 있습니다. 저는 먼저 COUNTIF로 겹치는 행을 표시하고, 실제로 불필요한 행인지 확인한 뒤에 손봅니다. 명단을 다룰 때는 그 한 번의 확인이 가장 시간을 아껴 주더라고요.
