중복값 처리

중복값을 확인하는 이유는 너무도 많기 때문에, 어떻게 처리할 것인지에 따라, 다양한 방법이 있으리라. 아래와 같은 간단한 원본 데이터에 대하여, 자주 쓰는 몇가지 방법을 요약해 보았다

myimg



COUNTIF

아래 수식만으로도 중복값을 알아낼 수 있으며, 자기 자신을 배제해야하니, 결과값이 2이상인 경우 중복이라 볼 수 있다. 중복된 수량을 직관적으로 알 수 있으며, 정렬을 통해 손쉽게 후처리가 가능하다.

=COUNTIF($B$3:$B$11,B3)



아래와 같은 식으로 결과값을 조정할 수 있고, 조건부 서식을 병행하여 색으로 구분할 수도 있다.

=IF(COUNTIF($B$3:$B$11,B3)>1,COUNTIF($B$3:$B$11,B3)&“개 중복”,“유니크”)



단점은 다른 열을 하나 더 사용해야한다는 점이겠다.

myimg



중복된 항목 제거

중복된 항목만 남기고, 행을 지워버리는 기능으로, 데이터 탭의 중복된 항목제거 버튼이 있다.
값이 삭제되므로, 원본데이터에 사용하는 것은 별로이고, 주로, 값을 따서, 요약테이블 등을 새로 만든 후, 배열함수 등의 조건을 만들때 사용한다.

구체적인 예시를 하나 더 살펴 보자. 아래와 같이 표가 두개 있다고 하자..
그 중에 왼쪽 항목에서 중복되는 항목을 지워보자. 데이터 탭에 있는 중복된 항목 제거 버튼을 누른다.

myimg



모든 값이 C열에 들어있으므로 C열을 체크한다. 머리글이 없으므로 체크박스에는 체크를 하지 않는다.

myimg



확인을 누르면, 왼쪽 표에서 간단하게 중복되는 값(셀)이 사라졌다. 그렇다면, 오른쪽 표는 어떨까.
E,F,G 열이 있고 각각 들어가 있는 값이 다르다. 해당 영역을 선택하여, 중복된 항목 제거 버튼을 눌러준다.

myimg



버튼을 누르면, 아까와 다르게 열이 세개가 보인다. 모두 체크를 한다라는 의미는 E, F, G의 값이 모두 동일해야 중복으로 판단한다는 뜻이다.

myimg



딸기딸기감 과 사과딸기감 값이 중복되어 삭제되었다.

myimg



결과를 보자.

myimg



그렇다면, 앞의 2개의 값으로만 중복임을 판단하기 원한다면,,,,
간단하다.

myimg



2개 열만 체크하다.
근데.. 문제는 세번째 값의 우선순위는 어떻게 정해질까 라는 점이다.

myimg



간단하다. 위쪽 행이 살아남는다.

myimg



조건부 서식

색이나 서식을 지정하여 중복값을 처리할 떄 사용한다.
아는 사람은 알겠지만, 조건부 서식은 이름정의와 함께 여러명이 작업할 떄, 파일을 뒤죽박죽으로 만드는 원흉이 되곤한다.

myimg



자작 매크로

조건부 서식과 동일한 기능을 매크로로 지정하여 사용하는 방법이 있다.
아래 코드로, 중복이 발생하는 경우, 첫번째 값을 제외한 값부터 칠하거나, 모두 칠하는 것이 가능하다.

Private Sub checkDuplication()

Dim myrng As Range
Dim colorCheck As Boolean

On Error GoTo dd
'range 설정 : myrng
Set myrng = Application.InputBox("중복여부를 확인할 영역을 선택하세요", , , , , , , 8)
'중복체크방식 설정
If MsgBox("중복값 모두에 색칠을 하고 싶으면 yes, 첫번째 값에는 색을 칠하지 않으려면 no를 누르세요", vbYesNo) = vbYes Then
    colorCheck = True
Else
    colorCheck = False
End If

'range 갯수 세기 : myrangeCount
myrangeCount = myrng.Count

'cell check 그리고 색칠하기
For i = 1 To myrangeCount
    counter = 0
    For j = 1 To myrangeCount
        If myrng(i) = myrng(j) Then
            counter = counter + 1
            If counter >= 2 Then
                If colorCheck = True Then
                    myrng(i).Interior.ColorIndex = 4
                    myrng(j).Interior.ColorIndex = 4
                Else
                    myrng(j).Interior.ColorIndex = 4
                End If
            End If
        End If
    Next j
Next i

dd:

End Sub


응용 - 중복값 없애고, 텍스트 목록 가로로 이어붙이기

아래와 같은 테이블이 있을때, 우측의 테이블에 차종을 하나, 브랜드 이름을 현대/기아…. 이런식으로 나열을 하고 싶다. 차종은 추가될때 마다 자동으로 들어가게 하고 싶다. 당신의 선택은 무엇인가?

myimg



중복값 없애고 동적으로 값 등록하기

편하긴 한데, 잘 안쓰게 되는 방법이긴 하다. 원리는 간단한데, 이미 등록된 행들에 같은 값이 있으면, 스킵을 하고, 처음 나오는 값을 등록하는 것이다. COUNTIF 함수에서 찾는 대상을 동적으로 설정하는게 핵심이다. 표 사이에 새로운 값이 들어와도 처리가 가능하다.
IFERROR문은 사실 데코레이션인데, 더이상 새로운 값이 없으면, “-” 처리해준다. 실제로는 #N/A 에러가 뜰것이다.

{=IFERROR(INDEX($B$3:$B$15,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$15),0)),"-")}


myimg



조건에 따라 텍스트 이어주기

TEXTJOIN함수에서 TEXT부분에 IF문으로 조건을 걸어주고, 배열수식 처리를 해주면 된다.

{=TEXTJOIN("/",TRUE,IF($B$3:$B$15=K3,$C$3:$C$15,""))}


myimg



자주 쓰지 않아서, 기억은 가물가물하지만, 언젠가는 유용하게 써먹을 날이 오겠지.

끝.