'공통항목'에 해당되는 글 1건

[엑셀] 두그룹 리스트 합치기 (공통항목 제외하기)

엑셀을 하시다가 두 그룹의 리스트를 합쳐야 하는 경우가 있습니다. 쉽게 하는 방법은 그냥 복사+붙여 넣기 하면 쉽게 합쳐집니다. 하지만, 여기서 문제는 A 그룹과 B 그룹에서 공통으로 들어간 항목이 있어 겹친다는 것입니다.

예를 들면 아래와 같습니다.

복사+ 붙여 넣기 하면 색칠한 것과 같이 저렇게 몇 개의 항목이 겹쳐지게 되는 문제점이 있습니다. 겹치는 항목을 제외하고 목록을 합치는 법을 설명하고자 합니다.

<전체적인 방법 설명>

방법은 위의 예시 표를 기준으로 설명하겠습니다. 우선 이과를 기준으로 문과에서 이과에 해당되는 과목이 있으면 이를 제외한 리스트를 만들어서 합칠 예정입니다.

1. 이과와 문과 공통인 과목 찾아내기!

이는 'VLOOKUP' 함수를 이용하여 실시했습니다. 

=IFERROR(IF(VLOOKUP(B2,$A$2:$A$9,1,FALSE)=B2,"-"),B2)

위와 같이 함수를 이용하여 같은 과목이면 "-"표시를 하게 했고, 다른 과목이면 그냥 그대로 표시되게 했습니다.

위의 표는 그 결과입니다. '문과' 과목 중에서 '이과'와 공통인 과목은 지워지고 '문과'에만 있는 과목들이 남았습니다. (문과-1 열)

2. 문과의 남은 과목 리스트 정리

문과의 남은 리스트를 지난번에 포스팅했던 것과 같이 위쪽으로 몰아서 정리를 해 주었습니다.

지난번 리스트 빈칸 없애고 정리하는 법을 포스팅했던 내용 링크입니다. 

2020/06/30 - [일상 이것저것] - [Excel] 리스트 중 비셀 없애고 정리하는 법

위의 링크대로 정리해주면 아래와 같이 됩니다. (문과-2 열)

=IFERROR(IF(VLOOKUP(B2,$A$2:$A$9,1,FALSE)=B2,"-"),B2)

3. 이과와 문과 합치기

이제 '이과'와 '문과-2'의 두열을 함수 "OFFSET"을 이용하여 합칠 예정입니다. '이과'를 전부 불러오고, '이과'에서 끝나게 되면 '문과-2'로 넘어가서 불러오라는 함수를 사용할 예정입니다. '이과'에서 다 불러오고 그다음 빈칸을 넘어오게 되면 "OFFSET" 함수가 '숫자 0'으로 불러오게 됩니다.

이를 이용하여 '숫자 0'이 나오면 '문과-2'로 넘어가라고 "IF" 함수를 이용하였습니다.

=IF(OFFSET($A$1,ROW(A1),0)=0,OFFSET($D$1,ROW(D1)-COUNTA(A:A)+1,0),OFFSET($A$1,ROW(A1),0))

그 결과 나온 표입니다. 기존에 복사+붙여 넣기 한 항목과 비교해 보면 공통된 과목이 제외된 것을 보실 수 있습니다.

두열을 합치는데 공통된 내용을 제외하고 합치는 것은 이제 하실 수 있을 거라 생각합니다. 손으로 하나하나씩 찾아가며 하기에는 시간도 걸리고 실수도 있을 수 있으니 이렇게 사용하면 편할 것 같습니다. 또한, 지금 예시는 짧은 목록이지만 목록이 몇천 개 이상이라면 당연히 위와 같은 방법이 유용하게 쓰일 것 같습니다.