Picking up rows that match certain criteria

Passi

New Member
Joined
Jun 7, 2020
Messages
1
Office Version
2013
Platform
Windows
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
ABCDEFGHIJKLMNOPQ
1Data to work onOutput required
2COST CNTEMP NOEMP NAMEPAY PRDFOR PAY PRDCOST DESCR$ACC CODECOST CNTEMP NOEMP NAMEPAY PRDFOR PAY PRDCOST DESCR$ACC CODE
3TK-D92064430DUP GEN73Base Sal120.220660TK-D92064430DUP GEN73Base Sal120.220660
4TK-D92064430DUP GEN73Emp Allowance2220610TK-D92064430DUP GEN73Emp Allowance2220610
5TK-D92064430DUP GEN73Meal Allowance1020612TK-D92064430DUP GEN73Meal Allowance1020612
6TK-T10124430DUP GEN73Base Sal-117.220660TK-T10124430DUP GEN73Base Sal-117.220660
7TK-T10124430DUP GEN73Emp Allowance-1220610TK-T10124430DUP GEN73Emp Allowance-1220610
8TK-T10124430DUP GEN73Meal Allowance-320612TK-T10124430DUP GEN73Meal Allowance-320612
9TK-X11224430DUP GEN73Base Sal-320660TK-X11224430DUP GEN73Base Sal-320660
10TK-X11224430DUP GEN73Emp Allowance-120610TK-X11224430DUP GEN73Emp Allowance-120610
11TK-D92064360HUST TIK73Base Sal2020660TK-D92064360HUST TIK73Base Sal2020660
12TK-D92064360HUST TIK74Emp Allowance320610TK-D92064360HUST TIK74Emp Allowance320610
13TK-H12124360HUST TIK73Base Sal-1820660TK-H12124360HUST TIK73Base Sal-1820660
14TK-S25254360HUST TIK74Emp Allowance-220610TK-S25254360HUST TIK74Emp Allowance-220610
15TK-D92061225STAN TAS76Base Sal3620660TK-D92061225STAN TAS76Base Sal3620660
16TK-L92061225STAN TAS75Base Sal-520660TK-L92061225STAN TAS75Base Sal-520660
17TK-D82161225STAN TAS75Base Sal-2420660TK-D82161225STAN TAS75Base Sal-2420660
18TK-N11113232TAK TIK74Base Sal1420660
19TK-N41213232TAK TIK74Base Sal-920660
20TK-N41223232TAK TIK74Base Sal-520660
Sheet1

 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,603
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
ABCDEFGHIJKLMNOPQ
1Data to work onOutput required
2COST CNTEMP NOEMP NAMEPAY PRDFOR PAY PRDCOST DESCR$ACC CODECOST CNTEMP NOEMP NAMEPAY PRDFOR PAY PRDCOST DESCR$ACC CODE
3TK-D92064430DUP GEN73Base Sal120.220660
TK-D9206
4430
DUP GEN
7
3
Base Sal
120.2
20660
4TK-D92064430DUP GEN73Emp Allowance2220610
TK-D9206
4430
DUP GEN
7
3
Emp Allowance
22
20610
5TK-D92064430DUP GEN73Meal Allowance1020612
TK-D9206
4430
DUP GEN
7
3
Meal Allowance
10
20612
6TK-T10124430DUP GEN73Base Sal-117.220660
TK-T1012
4430
DUP GEN
7
3
Base Sal
-117.2
20660
7TK-T10124430DUP GEN73Emp Allowance-1220610
TK-T1012
4430
DUP GEN
7
3
Emp Allowance
-12
20610
8TK-T10124430DUP GEN73Meal Allowance-320612
TK-T1012
4430
DUP GEN
7
3
Meal Allowance
-3
20612
9TK-X11224430DUP GEN73Base Sal-320660
TK-X1122
4430
DUP GEN
7
3
Base Sal
-3
20660
10TK-X11224430DUP GEN73Emp Allowance-120610
TK-X1122
4430
DUP GEN
7
3
Emp Allowance
-1
20610
11TK-D92064360HUST TIK73Base Sal2020660
TK-D9206
4360
HUST TIK
7
3
Base Sal
20
20660
12TK-D92064360HUST TIK74Emp Allowance320610
TK-D9206
4360
HUST TIK
7
4
Emp Allowance
3
20610
13TK-H12124360HUST TIK73Base Sal-1820660
TK-H1212
4360
HUST TIK
7
3
Base Sal
-18
20660
14TK-S25254360HUST TIK74Emp Allowance-220610
TK-S2525
4360
HUST TIK
7
4
Emp Allowance
-2
20610
15TK-D92061225STAN TAS76Base Sal3620660
TK-D9206
1225
STAN TAS
7
6
Base Sal
36
20660
16TK-L92061225STAN TAS75Base Sal-520660
TK-L9206
1225
STAN TAS
7
5
Base Sal
-5
20660
17TK-D82161225STAN TAS75Base Sal-2420660
TK-D8216
1225
STAN TAS
7
5
Base Sal
-24
20660
18TK-N11113232TAK TIK74Base Sal1420660
 
 
 
 
 
 
 
 
19TK-N41213232TAK TIK74Base Sal-920660
 
 
 
 
 
 
 
 
20TK-N41223232TAK TIK74Base Sal-520660
 
 
 
 
 
 
 
 
Camps
Cell Formulas
RangeFormula
J3:Q20J3=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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,000
Messages
5,465,927
Members
406,454
Latest member
MarkB5432

This Week's Hot Topics

Top