중복된 값은 제외하고 문자열 연결하기

1. 문제

B열부터 D열까지 고객, 쿠폰, 전화번호를 연결하는데

전화번호가 1개라면 한번씩만 연결하면 되는데,

6행에서 8행까지는 전화번호가 같으므로 한번 고객, 쿠폰과 전화번호를 연결한 다음부터는

쿠폰 번호만 연결하려고 하는 것입니다.

 

경우에 따라 결합을 달리해야 하기 때문에 문제가 되는 것입니다.

 

2. 해법 1 : TextJoin 함수 적용

엑셀 버전이 TestJoin 함수를 지원하지 않는다면(안된다면) 3번을 참고 바랍니다.

​

규칙이 고객, 쿠폰 번호, 전화번호를 연결해서 표시하는 것인데,

전화번호가 같으면 고객과 전화번호는 제외하고 쿠폰 번호만 연결하는 것입니다.

​

따라서, 전화번호의 개수를 계산한 후

1개면 고객부터 전화번호까지 모두 연결하면 되고,

​

1개를 초과하면 고객부터 전화번호까지를 한번 연결한 다음

그 다음부터는 쿠폰만 연결하면 되는데,

현재 위치 아래 셀부터 (전화번호 개수-1)만큼의 쿠폰을 표시하면 됩니다.

​

수식은 이해하기 쉽게 표현하면

COUNTIF($D$3:$D$9,D3)=1이면 B3셀부터 D3셀까지 연결하고,

 

아니면 TEXTJOIN(",",,B3:D3,OFFSET(C4,,,COUNTIF($D$3:$D$9,D3)-1))

B3에서 D3셀까지 더한 것에 C4셀부터 전화번호의 개수-1만큼 추가하는 것입니다.

 

따라서, 수식은 아래와 같습니다.

=IF(D3=D2,"",IF(COUNTIF($D$3:$D$9,D3)=1,TEXTJOIN(",",,B3:D3),TEXTJOIN(",",,B3:D3,OFFSET(C4,,,COUNTIF($D$3:$D$9,D3)-1))))

 

그런데 위 2개 수식에서 TEXTJOIN(",",,B3:D3)이 중복되므로 하나로 합치면

=IF(D3=D2,"",TEXTJOIN(",",,B3:D3,OFFSET(C4,,,COUNTIF($D$3:$D$9,D3)-1)))가 되는데,

전화번호가 1개일 경우 -1을 하면 0이 돼서

#REF! 에러가 발생하므로

 

 

에러일 때 처리하는 IfError 함수를 연결하고, 에러 일때 값으로 ""을 적용하면 아래 수식이 됩니다.

 

=IF(D3=D2,"",TEXTJOIN(",",,B3:D3,IFERROR(OFFSET(C4,,,COUNTIF($D$3:$D$9,D3)-1),"")))

 

위에서 설명하지 않은 사항에 대해 더 설명하면

 

전화번호가 같으면 공백으로 만들기 위해 앞에 IF(D3=D2,"",를 추가했고,

​

OFFSET(C4,,,COUNTIF($D$3:$D$9,D3)-1)가 좀 어려울 듯 한데,

Offset 함수의 인수는 이동할 행 수, 이동할 열 수, 높이, 너비입니다.

​

현재 셀이 C3셀이므로

그 아래 셀인 C4셀을 기준으로 하는데,

​

행 수와 열 수를 모두 입력하지 않으면 0이므로 행 또는 열 방향으로는 이동하지 않고,

​

높이만 전화번호의 개수를 COUNTIF($D$3:$D$9,D3)로 구한 다음

1을 빼는 것입니다.

왜냐하면 쿠폰을  1번은 포함했으니 1을 빼는 것입니다.

​

네번째 인수인 너비도 입력하지 않았는데, 생략하면 1입니다.

​

전화번호의 개수를 기준으로 연속된 행의 쿠폰번호를 가져오므로,

전화번호 순으로 정렬되어 있지 않으면 오류가 발생하니 주의바랍니다.

 

또한 중간의 공백은 어쩔 수 없으니 복사해서 값으로 붙여넣은 후

공백을 제거하기 바랍니다.

​

3. 해법 2 :  Concat 함수 적용

TextJoin 함수를 이용하면 범위만 지정해도 알아서 쉼표(,)가 들어가는데,

Concat 함수는 일일히 쉼표를 넣어줘야 하니 번거롭습니다.

예, B3&","&C3&","&D3

​

그렇지만 TextJoin 함수가 적용되지 않는 엑셀 버전이 있어서 만들었습니다.

​

Concat 함수를 이용한 수식은 아래와 같습니다.

​

=IF(D3=D2,"",SUBSTITUTE(SUBSTITUTE(CONCAT(B3&","&C3&","&D3&",",IFERROR(OFFSET(C4,,,COUNTIF($D$3:$D$9,D3)-1),"")&","),",","",COUNTIF($D$3:$D$9,D3)+2),",","",COUNTIF($D$3:$D$9,D3)+2))

​

Substitute 함수를 사용하지 않을 경우 맨 뒤에 쉼표(,)가 2개까지 있어서 Substitute 함수 두번 사용해야 합니다.

 

엑셀질문(textjoin)(답글).xlsx

0.01MB