데이터를 다루다보면 두 열을 비교해서 중복되지 않는 데이터를 추출해야하는 경우가 있습니다.

일반적을 자동으로 생성되는 데이터와 수동으로 입력하는 데이터를 다룰 때 발생하는데요. 예를 들어, 데이터에서 거래처를 추출해서 새롭게 등록을 해야하는 경우에 기존 거래처를 제외하고 추출을 해야하죠.

두 열을 비교해서 중복되지 않는 데이터만 추출하려면 FILTER 와 MATCH, 그리고 ISNA 함수를 사용합니다

예를 들어, 자동으로 생성된 거래처가 30개가 있는데 이 중 20개는 기존 거래처입니다. 10개의 신규 거래처만 추출해서 정리하고자 합니다.

답은 다음과 같습니다.

=FILTER(A2:A31,ISNA(MATCH(A2:A31,C2:C21,0)))

이제 왜 이런 결과가 나오는지 설명해보겠습니다.

목적은 A2:A31에서 C2:C21 에 들어있지 않은 값을 추출하는 것입니다. 목록에서 조건을 적용해서 데이터를 추출할 때는 FILTER 함수를 사용합니다.

=FILTER(A2:A31,필터링 조건)

이제 필터링 조건을 무엇으로 넣어야하는지가 중요합니다.

들어있지 않는 값이 포인트입니다. 않다라는 부정형이기 때문에 #N/A 값이 나오는지 체크해주는 ISNA 함수를 사용할 예정입니다.

이제 MATCH 함수를 생각해보시죠. 검색할 키를 범위에서 찾아서 상대적인 위치를 반환합니다.

=MATCH(검색할 키, 범위, 유형)

C2:C21에서 거래처1을 MATCH로 찾으면 가장 위에 있기 때문에 1 값이 리턴됩니다. 만약 거래처20을 찾으면 20이 리턴됩니다. 정확한 값을 찾고 있기 때문에 유형은 0으로 써줍니다.

이제 A열의 값을 C열의 범위에서 찾기 위해서는 다음과 같이 MATCH를 적용해볼 수 있겠지요. 그런데 키와 범위가 목록인데, MATCH 에서는 단일 값을 리턴하기 때문에 1이라는 숫자만 값으로 볼 수 있습니다.

=MATCH(A2:A31,C2:C21,0)

그래서, ARRAYFORMULA 를 적용해서 어떤 결과가 나오는지 보겠습니다.

=ARRAYFORMULA(MATCH(A2:A31,C2:C21,0))

기존 거래처 목록 C열에 없고, A열에 있는 항목은 #N/A로 표시됩니다. 이제 ISNA를 적용해볼까요?

거래처 21 ~ 30은 ISNA 값을 TRUE로 리턴합니다. 이 조건을 이제 FILTER 에 적용해보겠습니다. ARRAYFORMULA는 값을 보기 위해서 임시로 지정한 것이기 때문에 FILTER 에 적용할 때는 삭제해줍니다.

=FILTER(A2:A31,ISNA(MATCH(A2:A31,C2:C21,0)))

짜잔~ 중복되지 않는 데이터의 목록을 얻었습니다.

실무에서는 자동으로 생성되는 데이터를 복사-붙여넣기로 목록화 시킨 다음, 새로 추가되는 데이터만 복사-붙여넣기하는 데 사용합니다. 예를 들어서, 세금계산서 발행할 목록이 있고, 발행한 목록이 있는 경우에 미발행 항목만 추출할 수 있습니다.

샘플을 살펴보세요. PC에서 보셔야 함수를 보실 수 있습니다.