같은 열 중복데이타 모두 나열 (VLOOKUP/MATCH 유동참조)

 http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=EunZH9ly4x0sNC8YYHexOy9nA2sY8oP3

 

이 질문은 아주 특수한 경우가 아니라면 고급필터가 답입니다. 아래는 함수로만으로 방법은 있되 어느 정도 복잡해지는지 제 나름대로 가늠해 보기 위한 예시입니다.

 

	B	C	D	E	F	G	H	I	J
2	이름	D1	D2		시작행	3
3	갑	1	10		끝행	9
4	을	2	11
5	갑	3	12		이름	D1	D2	잔여	행
6	을	4	20		갑	1	10	2	1
7	병	5	21		갑	3	12	1	3
8	갑	6	22		갑	6	22	0	7
9	을	7	23			###	#N/A	-1	#N/A

 

 

데이타가 B3:D9에 있을 때 찾을 데이타를 F6에 입력한 경우,

 

G2 =ROW(B3) ; 데이타 시작 행

G3 =ROW(B9) ; 데이타 끝 행 (수치로 입력해도 되지만 나중 행삽입/삭제해도 유효하게 하기 위함)

G6 =VLOOKUP($F6,$B$3:$D$9,COLUMN(B1),0)

I6 =COUNTIF(B3:B9,F6)-1 ; 제시된 F6을 앞으로 더 출력해야 하는 개수

J6 =MATCH(F6,B3:B9,0)

F7 =IF(I6>0,F6,"") ; 다 출력했으면 빈칸

G7 =VLOOKUP($F7,INDIRECT(ADDRESS($G$2+$J6,COLUMN($B2))):INDIRECT(ADDRESS($G$3,COLUMN($D2))),COLUMN(B2),0) ; G6의 수식을 유동참조로 바꿈

I7 =I6-1

J7 =MATCH($F7,INDIRECT(ADDRESS($G$2+$J6,COLUMN($B2))):INDIRECT(ADDRESS($G$3,COLUMN($B2))),0)+SUM(J$6:J6) ; J6의 수식을 유동참조로 바꿈

 

G6:G7 잡아 H6:H7에 복사, F7:J7 잡아 아래로 복사하면 표가 완성됩니다. 같은 함수라도 위 작업을 배열수식으로 처리하면 수식은 보다 간단해지지만 50행만 넘어가도 속도가 무쟈게 느려지는 단점이 있습니다. 따라서 수십 행이냐 수백 행 이상이냐에 따라서도 함수로 처리하는 방식 나름으로 장단점이 있을 겁니다.