(A1, A2, A3) 형태로부터 A1 위치 찾아 내기

질문의 형태는 '번호2' 열에 들어있는 데이타를 (VBA든 함수를 이용해서) 모두 독립시킨 형태로 바꿔야 완벽히 잡아낼 수 있습니다. (그렇지 않고 아래 1을 FIND로 찾으면 11이나 121을 지시할 수 있음) 함수 방식으로는 아래와 같이 복잡해 보이는 절차로 해결할 수 있습니다. (사실은 하나씩 따라가 보면 별 것 아닙니다)

 

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	번호1	이름1		이름2	번호2		7	2			!		!!
2	A	갑		갑	A, 22	A,22	4	1	2		A	###	22
3	2	을		을	2, 11	2,11	4	1	2		2	###	11
4	3	정		병	111, 222	111,222	7	1	4		111	###	222
5	B	정		정	3, B, 5	3,B,5	5	2	2	4	3	B	5
6	5	정
7	11	을									A		22
8	22	갑									2		11
9	111	병									111		222
10	222	병									3	B	5

 

F2 =SUBSTITUTE(E2," ","") ; 문자열 중 공백을 없앰
G2 =LEN(F2) ; 문자열 길이
H2 =SUMPRODUCT(N(MID(F2,ROW(INDIRECT("1:"&$G$1)),1)=",")) ; 문자열 중 쉼표의 개수
G1 =MAX(G2:G5)
H1 =MAX(H2:H5)
I2 =IF($H2,FIND(",",$F2,1),0) ; 첫번 째 쉼표의 위치
J2 =IF(COLUMN(A$1)=$H2,"",FIND(",",$F2,I2+1)) ; 다음 쉼표 위치 (I열 이후 H1의 숫자 만큼 오른쪽으로 복사)
K2 =LEFT($F2,I2-1) ; 처음 데이타
L2 =MID(F2,I2+1,J2-I2-1) ; 중간 데이타 (!가 있는 열(K), !!가 있는 열(M)을 포함, 최대개수(F5의 3)에 맞춤
M2 =RIGHT(F2,G2-MAX(I2:J2)) ; 마지막 데이타
K7 =IF(OR(ISERROR(K2),COUNTIF(K$1:M1,K2)+COUNTIF(K$2:K2,K2)>1),"",K2) ; 데이타 재정렬

B2 =OFFSET(D$1,SUMPRODUCT(N(K$7:M$10=TEXT(A2,"0"))*(ROW(K$7:M$10)-ROW(K$6))),0)

 

F2:M2 잡아 아래로 데이타 행 만큼, K7 잡아 오른쪽, 아래로 채우기, B2 잡아 아래로 드래그 복사하면 표가 완성됩니다. 요약하면 K7:M10의 독립된 데이타로 재표시하고 B2에서 불러내면 됩니다. 원본 데이타의 개수 (예시의 E2:E5, 각 셀의 데이타 수)에 따라 그 범위는 달라질 것인 바, B2 수식에서 적절히 고쳐 쓰시면 됩니다. 위 표와 수식은 드래그 복사가 됩니다. 엑셀 시트에 붙여 넣어 찬찬히 이해한 후 응용해 보세요.