이름 기준으로 중복 데이터 제거 및 데이터 병합하기

1. 문제

1번과 같이 원시 데이터는 이름, 번호, 주소가 중복되는데, 품목과 수량을 건별로 입력되어 있습니다.

 

2. 해법

이름 기준 같은 데이터 병합.xlsx

0.01MB

 

 

엑셀 버전에 따라 안될 수도 있습니다.

 

 

가. 구문

Unique 함수 구문 : =UNIQUE(array,[by_col],[exactly_once])

Unique 함수 구문 : =SEQUENCE(rows,[columns],[start],[step])

TextJoin 함수 구문 : TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

 

나. 적용 예

B23셀 : =UNIQUE(TRIM(B3:D9))

=> 결과 : 이름, 전화번호, 주소의 유일한 값만 구해줍니다.

Trim함수는 텍스트의 좌, 우 공백을 제거하는 함수입니다.

위 데이터에서 주소를 보면 아파트명 뒤에 공백이 있는 것이 있어서 Trim을 사용해야 합니다.

 

E23셀 : =TEXTJOIN(CHAR(10),,INDEX(E$3:E$9,SEQUENCE(COUNTIF($B$3:$B$9,$B23),,MATCH($B23,$B$3:$B$9,0))))

이름이 같은 것의 품목을 줄 바꿈 문자[Char(10)]로 결합해서 연결된 값을 구해야 합니다.

그리고, 줄바꿈 문자로 결합되더라도 줄을 바꾸려면 홈 탭에서 '자동 줄 바꿈'에 체크해야 합니다.

 

=INDEX(E3:E9,{1,2,3})하면 E3셀에서 3개의 값을 가져오므로, 이를 응용해서 가져오면 되는데,

 

시작 위치는 Match함수로 이름이 나오는 첫 번째 위치를 찾으면 되고,

 

3개는 CountIf함수로 이름의 개수를 구하면 되며

 

연속적인 숫자는 Sequence 함수를 이용하는데,

1부터 시작할 때는 어떻게 하든 문제없는데,

김선영과 같이 4번째부터 1개를 가져올 때는

=sequence(1,,4)라고 첫 번째 값에 개수를 넣고, 세 번째 인수에 시작값을 넣어야 합니다.

 

F23셀 : E23셀 수식 지정할 때 오른쪽으로 복사하더라도 셀 주소가 자동으로 바뀌도록 혼합참조 형식으로 지정했으므로 E23셀의 수식을 F23셀로 끌면 됩니다.

 

그러면 수식은 =TEXTJOIN(CHAR(10),,INDEX(F$3:F$9,SEQUENCE(COUNTIF($B$3:$B$9,$B23),,MATCH($B23,$B$3:$B$9,0))))

가 됩니다.

 

이제 완성되었습니다.