Picking up rows that match certain criteria

[Passi]

Hi,
I need to fix some payroll error which has generated thousands of rows. What I need is for every employee who has an attached Cost Centre TK-D9206 I need find data wherever he has other cost centres attached. I don't need to find for an employee if he is not in TK-D9206. I am concerned only for an employee who is sitting in TK-D9206. Could you please provide a solution. I below give data and solution that I need.

PAYROLL TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Data to work on									Output required
2	COST CNT	EMP NO	EMP NAME	PAY PRD	FOR PAY PRD	COST DESCR	$	ACC CODE		COST CNT	EMP NO	EMP NAME	PAY PRD	FOR PAY PRD	COST DESCR	$	ACC CODE
3	TK-D9206	4430	DUP GEN	7	3	Base Sal	120.2	20660		TK-D9206	4430	DUP GEN	7	3	Base Sal	120.2	20660
4	TK-D9206	4430	DUP GEN	7	3	Emp Allowance	22	20610		TK-D9206	4430	DUP GEN	7	3	Emp Allowance	22	20610
5	TK-D9206	4430	DUP GEN	7	3	Meal Allowance	10	20612		TK-D9206	4430	DUP GEN	7	3	Meal Allowance	10	20612
6	TK-T1012	4430	DUP GEN	7	3	Base Sal	-117.2	20660		TK-T1012	4430	DUP GEN	7	3	Base Sal	-117.2	20660
7	TK-T1012	4430	DUP GEN	7	3	Emp Allowance	-12	20610		TK-T1012	4430	DUP GEN	7	3	Emp Allowance	-12	20610
8	TK-T1012	4430	DUP GEN	7	3	Meal Allowance	-3	20612		TK-T1012	4430	DUP GEN	7	3	Meal Allowance	-3	20612
9	TK-X1122	4430	DUP GEN	7	3	Base Sal	-3	20660		TK-X1122	4430	DUP GEN	7	3	Base Sal	-3	20660
10	TK-X1122	4430	DUP GEN	7	3	Emp Allowance	-1	20610		TK-X1122	4430	DUP GEN	7	3	Emp Allowance	-1	20610
11	TK-D9206	4360	HUST TIK	7	3	Base Sal	20	20660		TK-D9206	4360	HUST TIK	7	3	Base Sal	20	20660
12	TK-D9206	4360	HUST TIK	7	4	Emp Allowance	3	20610		TK-D9206	4360	HUST TIK	7	4	Emp Allowance	3	20610
13	TK-H1212	4360	HUST TIK	7	3	Base Sal	-18	20660		TK-H1212	4360	HUST TIK	7	3	Base Sal	-18	20660
14	TK-S2525	4360	HUST TIK	7	4	Emp Allowance	-2	20610		TK-S2525	4360	HUST TIK	7	4	Emp Allowance	-2	20610
15	TK-D9206	1225	STAN TAS	7	6	Base Sal	36	20660		TK-D9206	1225	STAN TAS	7	6	Base Sal	36	20660
16	TK-L9206	1225	STAN TAS	7	5	Base Sal	-5	20660		TK-L9206	1225	STAN TAS	7	5	Base Sal	-5	20660
17	TK-D8216	1225	STAN TAS	7	5	Base Sal	-24	20660		TK-D8216	1225	STAN TAS	7	5	Base Sal	-24	20660
18	TK-N1111	3232	TAK TIK	7	4	Base Sal	14	20660
19	TK-N4121	3232	TAK TIK	7	4	Base Sal	-9	20660
20	TK-N4122	3232	TAK TIK	7	4	Base Sal	-5	20660
Sheet1

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Data to work on									Output required
2	COST CNT	EMP NO	EMP NAME	PAY PRD	FOR PAY PRD	COST DESCR	$	ACC CODE		COST CNT	EMP NO	EMP NAME	PAY PRD	FOR PAY PRD	COST DESCR	$	ACC CODE
3	TK-D9206	4430	DUP GEN	7	3	Base Sal	120.2	20660		TK-D9206	4430	DUP GEN	7	3	Base Sal	120.2	20660
4	TK-D9206	4430	DUP GEN	7	3	Emp Allowance	22	20610		TK-D9206	4430	DUP GEN	7	3	Emp Allowance	22	20610
5	TK-D9206	4430	DUP GEN	7	3	Meal Allowance	10	20612		TK-D9206	4430	DUP GEN	7	3	Meal Allowance	10	20612
6	TK-T1012	4430	DUP GEN	7	3	Base Sal	-117.2	20660		TK-T1012	4430	DUP GEN	7	3	Base Sal	-117.2	20660
7	TK-T1012	4430	DUP GEN	7	3	Emp Allowance	-12	20610		TK-T1012	4430	DUP GEN	7	3	Emp Allowance	-12	20610
8	TK-T1012	4430	DUP GEN	7	3	Meal Allowance	-3	20612		TK-T1012	4430	DUP GEN	7	3	Meal Allowance	-3	20612
9	TK-X1122	4430	DUP GEN	7	3	Base Sal	-3	20660		TK-X1122	4430	DUP GEN	7	3	Base Sal	-3	20660
10	TK-X1122	4430	DUP GEN	7	3	Emp Allowance	-1	20610		TK-X1122	4430	DUP GEN	7	3	Emp Allowance	-1	20610
11	TK-D9206	4360	HUST TIK	7	3	Base Sal	20	20660		TK-D9206	4360	HUST TIK	7	3	Base Sal	20	20660
12	TK-D9206	4360	HUST TIK	7	4	Emp Allowance	3	20610		TK-D9206	4360	HUST TIK	7	4	Emp Allowance	3	20610
13	TK-H1212	4360	HUST TIK	7	3	Base Sal	-18	20660		TK-H1212	4360	HUST TIK	7	3	Base Sal	-18	20660
14	TK-S2525	4360	HUST TIK	7	4	Emp Allowance	-2	20610		TK-S2525	4360	HUST TIK	7	4	Emp Allowance	-2	20610
15	TK-D9206	1225	STAN TAS	7	6	Base Sal	36	20660		TK-D9206	1225	STAN TAS	7	6	Base Sal	36	20660
16	TK-L9206	1225	STAN TAS	7	5	Base Sal	-5	20660		TK-L9206	1225	STAN TAS	7	5	Base Sal	-5	20660
17	TK-D8216	1225	STAN TAS	7	5	Base Sal	-24	20660		TK-D8216	1225	STAN TAS	7	5	Base Sal	-24	20660
18	TK-N1111	3232	TAK TIK	7	4	Base Sal	14	20660
19	TK-N4121	3232	TAK TIK	7	4	Base Sal	-9	20660
20	TK-N4122	3232	TAK TIK	7	4	Base Sal	-5	20660
Camps

Cell Formulas
Range	Formula
J3:Q20	J3	=IFERROR(INDEX(A$3:A$20,AGGREGATE(15,6,(ROW($A$3:$A$20)-ROW($A$3)+1)/(COUNTIFS($B$3:$B$20,$B$3:$B$20,$A$3:$A$20,"TK-D9206")>0),ROWS(J$3:J3))),"")