extracting unique data

R

rcahayag1925

New Member
Joined
Sep 24, 2018
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Hi Community,

Can anyone help me please on my excel file sheets.

I have this data on sheet "Form 1.0 Regular". What I wanted is to transfer this data to "Commission" Sheet

The first thing I wanted is to extract unique names. Names should appear only once in the Commission Sheet.
Then find the corresponding highest number of months stayed by the personnel in the specific department/unit and the equivalent ratings for the highest months stayed on that department. Then return the highest salary grade and salary of the personnel.

"Form 1.0 Regular" Sheet
EMPLOYEE NORankingNames of Bureaus/ Offices/Attached Agencies/ Delivery UnitsRate of Accomplishment of Targets (in %)Names of EmployeeSalary Grade (and Step Increment)Monthly Basic SalaryMonths in service in 2018
9014632.1 BEST (10%)ASPAC110ENSIGN, MARYBELLE30-1
143,534.00​
6
951632TIGER, LUCILA29-2
130,392.00​
6
980876CAYLOR, CAYMER28-2
116,704.00​
5
971117LOFTON, MINDA28-2
116,704.00​
7
002320HOAGLIN, THERESA28-2
116,704.00​
5
820962GENEVA PM115HOSEA, JACK30-3
147,869.00​
8
970910PARNELL, JEFFIE29-1
128,467.00​
7
124594METZER, TERRELL25-1
82,439.00​
5
870814POPLAR, KACY20-3
48,457.00​
5
032595FUSELIER, VERA20-1
47,037.00​
12
TOTAL BEST
7818912.2 BETTER (25%)ANKARA PE147ENSIGN, MARYBELLE30-4150,085.006
002263TIGER, LUCILA29-2
130,392.00​
3
052810CAYLOR, CAYMER28-2
116,704.00​
5
146490LOFTON, MINDA28-2
116,704.00​
5
830278ASEAN113HOAGLIN, THERESA28-2
116,704.00​
7
911315HOSEA, JACK30-3
147,869.00​
4
961652PARNELL, JEFFIE29-1
128,467.00​
5
962074METZER, TERRELL25-1
82,439.00​
7
801217BERNE PE114COOK, JERICO28-2116,704.0012
971797ASTRID, REIN28-2116,704.004
991202HUNT, ANNE27-1102,910.0012
072855COLLINS, JOEL28-2116,704.002
TOTAL BETTER
7622102.3 GOOD (65%)ABU DHABI PE143POPLAR, KACY20-3
48,457.00​
7
931720TIGER, LUCILA29-2
130,392.00​
3
104210CAYLOR, CAYMER28-2
116,704.00​
2
781625ASTRID, REIN27-1102,910.008
146570COLLINS, JOEL24-4
76,644.00​
8
174913ABUJA PE126HOSEA, JACK30-4150,085.004
841592RUSSEL, JACK28-2116,704.0012
042763HAMMOND, JILL27-1102,910.0012
921896CONLONM JAMES24-4
76,644.00​
12
TOTAL GOOD




Result, wanted

"Commission" Sheet

BEST
SEQ #EMPLOYEE NO.NAMESALARY GRADESTEPSALARYMONTHS IN SERVICE CY2018OFFICE/ POST
(PBB PURPOSES)		GRADING
(PBB PURPOSES)		SEPARATION TYPESEPARATION DATE
1951632TIGER, LUCILA292
130,392.00​
6ASPACBEST
2980876CAYLOR, CAYMER282
116,704.00​
5ASPACBEST
3971117LOFTON, MINDA282
116,704.00​
7ASPACBEST
4820962HOSEA, JACK304150,085.008GENEVA PMBEST
5970910PARNELL, JEFFIE291
128,467.00​
7GENEVA PMBEST
6032595FUSELIER, VERA201
47,037.00​
12GENEVA PMBEST
BETTER
SEQ #EMPLOYEE NO.NAMESALARY GRADESTEPSALARYMONTHS IN SERVICE CY2018OFFICE/ POST
(PBB PURPOSES)		GRADING
(PBB PURPOSES)		SEPARATION TYPESEPARATION DATE
781891ENSIGN, MARYBELLE304150,085.006ANKARA PEBETTER
830278HOAGLIN, THERESA282
116,704.00​
7ASEANBETTER
962074METZER, TERRELL251
82,439.00​
7ASEANBETTER
801217COOK, JERICO282116,704.0012BERNE PEBETTER
GOOD
SEQ #EMPLOYEE NO.NAMESALARY GRADESTEPSALARYMONTHS IN SERVICE CY2018OFFICE/ POST
(PBB PURPOSES)		GRADING
(PBB PURPOSES)		SEPARATION TYPESEPARATION DATE
781625ASTRID, REIN282.00116,704.008ABU DHABI PEGOOD
146570COLLINS, JOEL282.00116,704.008ABU DHABI PEGOOD
841592RUSSEL, JACK282116,704.0012ABUJA PEGOOD
042763HAMMOND, JILL271102,910.0012ABUJA PEGOOD
921896CONLONM JAMES244
76,644.00​
12ABUJA PEGOOD

Also posted here Extract unique data
 
Last edited by a moderator:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Can you post the data using xl2bb addin available in Right hand side of reply box.

Download it and install it and then you will find the Mr Excel Tab in Excel. Just select the data and click on capture data and paste here
 
Upvote 0
How about

Book1
ABCDEFGHIJKLMNOPQRSTU
1Regular SheetCommission Sheet
2EMPLOYEE NORankingNames of Bureaus/ Offices/Attached Agencies/ Delivery UnitsRate of Accomplishment of Targets (in %)Names of EmployeeSalary Grade (and Step Increment)Monthly Basic SalaryMonths in service in 2018EQ #EMPLOYEE NO.NAMESALARYMONTHS IN SERVICE CY2018OFFICE/ POSTGRADINGSEPARATION TYPESEPARATION DATEGRADING
39014632.1 BEST (10%)ASPAC110ENSIGN, MARYBELLE30-Jan1435346781891ENSIGN, MARYBELLE1500856ANKARA PEBETTER
4951632TIGER, LUCILA29-Feb1303926951632TIGER, LUCILA1303926ASPACBEST
5980876CAYLOR, CAYMER28-Feb1167045980876CAYLOR, CAYMER1167045ASPACBEST
6971117LOFTON, MINDA28-Feb1167047971117LOFTON, MINDA1167047GENEVA PMBEST
72320HOAGLIN, THERESA28-Feb1167045830278HOAGLIN, THERESA1167047BERNE PEBETTER
8820962GENEVA PM115HOSEA, JACK30-Mar1478698820962HOSEA, JACK1478698GENEVA PMBEST
9970910PARNELL, JEFFIE29-Jan1284677970910PARNELL, JEFFIE1284677ANKARA PEBETTER
10124594METZER, TERRELL25-Jan824395962074METZER, TERRELL824397ABU DHABI PEGOOD
11870814POPLAR, KACY20-Mar484575762210POPLAR, KACY484577ABUJA PEGOOD
1232595FUSELIER, VERA20-Jan470371232595FUSELIER, VERA4703712ASEANBETTER
13TOTAL BEST801217COOK, JERICO11670412ABUJA PEGOOD
147818912.2 BETTER (25%)ANKARA PE147ENSIGN, MARYBELLE30-Apr150,085.006781625ASTRID, REIN1029108ABUJA PEGOOD
152263TIGER, LUCILA29-Feb1303923991202HUNT, ANNE10291012ABUJA PEGOOD
1652810CAYLOR, CAYMER28-Feb1167045146570COLLINS, JOEL766448ABUJA PEGOOD
17146490LOFTON, MINDA28-Feb1167045841592RUSSEL, JACK11670412ABUJA PEGOOD
18830278ASEAN113HOAGLIN, THERESA28-Feb116704742763HAMMOND, JILL10291012ABUJA PEGOOD
19911315HOSEA, JACK30-Mar1478694921896CONLONM JAMES7664412ABUJA PEGOOD
20961652PARNELL, JEFFIE29-Jan1284675
21962074METZER, TERRELL25-Jan824397
22801217BERNE PE114COOK, JERICO28-Feb116,704.0012
23971797ASTRID, REIN28-Feb116,704.004
24991202HUNT, ANNE27-Jan102,910.0012
2572855COLLINS, JOEL28-Feb116,704.002
26TOTAL BETTER
277622102.3 GOOD (65%)ABU DHABI PE143POPLAR, KACY20-Mar484577
28931720TIGER, LUCILA29-Feb1303923
29104210CAYLOR, CAYMER28-Feb1167042
30781625ASTRID, REIN27-Jan102,910.008
31146570COLLINS, JOEL24-Apr766448
32174913ABUJA PE126HOSEA, JACK30-Apr150,085.004
33841592RUSSEL, JACK28-Feb116,704.0012
3442763HAMMOND, JILL27-Jan102,910.0012
35921896CONLONM JAMES24-Apr7664412
Sheet2
Cell Formulas
RangeFormula
N3:N19N3=FILTER(UNIQUE(E3:E35),UNIQUE(E3:E35)<>0)
O3:O19O3=MAX(IF($E$3:$E$35&$H$3:$H$35=N3&P3,$G$3:$G$35))
P3:P19P3=MAX(IF($E$3:$E$35=N3,$H$3:$H$35))
Q3:Q19Q3=LOOKUP("ZZZZZZ",$C$2:INDEX(C3:C35,MATCH(N3&O3&P3,$E$3:$E$35&$G$3:$G$35&$H$3:$H$35,0)))
R3:R19R3=LOOKUP(100,SEARCH({"BEST";"BETTER";"GOOD"},LOOKUP("ZZZZZZ",$B$2:INDEX(B3:B35,MATCH(N3&O3&P3,$E$3:$E$35&$G$3:$G$35&$H$3:$H$35,0)))),{"BEST";"BETTER";"GOOD"})
M3:M19M3=+VLOOKUP(N3&O3&P3,IF({1,0},$E$3:$E$35&$G$3:$G$35&$H$3:$H$35,$A$3:$A$35),2,0)
Dynamic array formulas.
 
Upvote 0
Hi CA_Punit, thanks for the reply. I am currently using an old excel version which is 2013. Which I think UNIQUE is not available for that version. Imfiguring out how can I attached the file here
 
Upvote 0
Commission.xlsx
ABCDEFGHI
10EMPLOYEE NORankingNames of Bureaus/ Offices/Attached Agencies/ Delivery UnitsRate of Accomplishment of Targets (in %)Names of EmployeeSalary Grade (and Step Increment) Monthly Basic Salary Months in service in 2018
119014632.1 BEST (10%)ASPAC110ENSIGN, MARYBELLE30-1143,534.006
12951632TIGER, LUCILA29-2130,392.006
13980876CAYLOR, CAYMER28-2116,704.005
14971117LOFTON, MINDA28-2116,704.007
15002320HOAGLIN, THERESA28-2116,704.005
16820962GENEVA PM 115HOSEA, JACK30-3147,869.008
17970910PARNELL, JEFFIE29-1128,467.007
18124594METZER, TERRELL25-182,439.005
19870814POPLAR, KACY20-348,457.005
20032595FUSELIER, VERA20-147,037.0012
21TOTAL BEST
227818912.2 BETTER (25%)ANKARA PE147ENSIGN, MARYBELLE30-4 150,085.00 6
23002263TIGER, LUCILA29-2130,392.003
24052810CAYLOR, CAYMER28-2116,704.005
25146490LOFTON, MINDA28-2116,704.005
26830278ASEAN113HOAGLIN, THERESA28-2116,704.007
27911315HOSEA, JACK30-3147,869.004
28961652PARNELL, JEFFIE29-1128,467.005
29962074METZER, TERRELL25-182,439.007
30801217BERNE PE114COOK, JERICO28-2 116,704.00 12
31971797ASTRID, REIN28-2 116,704.00 4
32991202HUNT, ANNE27-1 102,910.00 12
33072855COLLINS, JOEL28-2 116,704.00 2
34TOTAL BETTER
357622102.3 GOOD (65%)ABU DHABI PE143POPLAR, KACY20-348,457.007
36931720TIGER, LUCILA29-2130,392.003
37104210CAYLOR, CAYMER28-2116,704.002
38781625ASTRID, REIN27-1 102,910.00 8
39146570COLLINS, JOEL24-476,644.008
40174913ABUJA PE126HOSEA, JACK30-4 150,085.00 4
41841592RUSSEL, JACK28-2 116,704.00 12
42042763HAMMOND, JILL27-1 102,910.00 12
43921896CONLONM JAMES24-476,644.0012
44TOTAL GOOD
FORM 1.0 REGULAR
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A30Cell ValueduplicatestextNO
A30Cell ValueduplicatestextNO
A26Cell ValueduplicatestextNO
A26Cell ValueduplicatestextNO
A26Cell ValueduplicatestextNO
A27Cell ValueduplicatestextNO
A28Cell ValueduplicatestextNO
A22Cell ValueduplicatestextNO
A16Cell ValueduplicatestextNO
A17Cell ValueduplicatestextNO
A21Cell ValueduplicatestextNO
A12:A13Cell ValueduplicatestextNO
A12:A13Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
A11Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
E10Cell ValueduplicatestextNO
A14:A15Cell ValueduplicatestextNO
A14:A15Cell ValueduplicatestextNO
A18:A20Cell ValueduplicatestextNO
A18:A20Cell ValueduplicatestextNO
A23:A25Cell ValueduplicatestextNO
A23:A25Cell ValueduplicatestextNO
A29Cell ValueduplicatestextNO
A34Cell ValueduplicatestextNO
A34Cell ValueduplicatestextNO
A35:A39Cell ValueduplicatestextNO
A35:A39Cell ValueduplicatestextNO
A44Cell ValueduplicatestextNO
A31:A33Cell ValueduplicatestextNO
A31:A33Cell ValueduplicatestextNO
A40:A43Cell ValueduplicatestextNO
A40:A43Cell ValueduplicatestextNO
 
Upvote 0
Commission.xlsx
ABCDEFGHIJKLMNO
6SEQ #EMPLOYEE NO.NAMESALARY GRADESTEP SALARY MONTHS IN SERVICE CY2018OFFICE/ POST (PBB PURPOSES)GRADING (PBB PURPOSES)SEPARATION TYPESEPARATION DATEREMARKS
71951632TIGER, LUCILA292130,392.006ASPACBEST
82980876CAYLOR, CAYMER282116,704.005ASPACBEST
93971117LOFTON, MINDA282116,704.007ASPACBEST
104820962HOSEA, JACK304 150,085.00 8GENEVA PM BEST
115970910PARNELL, JEFFIE291128,467.007GENEVA PM BEST
126032595FUSELIER, VERA20147,037.0012GENEVA PM BEST
13
14BETTER
15SEQ #EMPLOYEE NO.NAMESALARY GRADESTEP SALARY MONTHS IN SERVICE CY2018OFFICE/ POST (PBB PURPOSES)GRADING (PBB PURPOSES)SEPARATION TYPESEPARATION DATEREMARKS
16781891ENSIGN, MARYBELLE30 4 150,085.00 6ANKARA PEBETTER
17830278HOAGLIN, THERESA28 2 116,704.007ASEANBETTER
18962074METZER, TERRELL25 1 82,439.007ASEANBETTER
19801217COOK, JERICO28 2 116,704.00 12BERNE PEBETTER
20
21GOOD
22SEQ #EMPLOYEE NO.NAMESALARY GRADESTEP SALARY MONTHS IN SERVICE CY2018OFFICE/ POST (PBB PURPOSES)GRADING (PBB PURPOSES)SEPARATION TYPESEPARATION DATEREMARKS
23781625ASTRID, REIN28 2.00 116,704.00 8ABU DHABI PEGOOD
24146570COLLINS, JOEL28 2.00 116,704.00 8ABU DHABI PEGOOD
25841592RUSSEL, JACK28 2 116,704.00 12ABUJA PEGOOD
26042763HAMMOND, JILL27 1 102,910.00 12ABUJA PEGOOD
27921896CONLONM JAMES24 4 76,644.0012ABUJA PEGOOD
COMMISSION
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B25:B27Cell ValueduplicatestextNO
B25:B27Cell ValueduplicatestextNO
B23:B24Cell ValueduplicatestextNO
B23:B24Cell ValueduplicatestextNO
B19Cell ValueduplicatestextNO
B19Cell ValueduplicatestextNO
B18Cell ValueduplicatestextNO
B17Cell ValueduplicatestextNO
B17Cell ValueduplicatestextNO
B17Cell ValueduplicatestextNO
B16Cell ValueduplicatestextNO
B12Cell ValueduplicatestextNO
B12Cell ValueduplicatestextNO
B10Cell ValueduplicatestextNO
B11Cell ValueduplicatestextNO
B8Cell ValueduplicatestextNO
B8Cell ValueduplicatestextNO
B9Cell ValueduplicatestextNO
B9Cell ValueduplicatestextNO
B7Cell ValueduplicatestextNO
B7Cell ValueduplicatestextNO
B21Cell ValueduplicatestextNO
B21Cell ValueduplicatestextNO
B22Cell ValueduplicatestextNO
B22Cell ValueduplicatestextNO
B13Cell ValueduplicatestextNO
B13Cell ValueduplicatestextNO
B14Cell ValueduplicatestextNO
B14Cell ValueduplicatestextNO
B15Cell ValueduplicatestextNO
B15Cell ValueduplicatestextNO
B28:B1048576,B6,B1:B4,B20Cell ValueduplicatestextNO
 
Upvote 0
the first sheet is the source data, and the second sheet is the output sheet
 
Upvote 0
The Final report layout as stated by you in post 7 is difficult and will require advanced excel formulas which may not be available in office 2013. So i recommend using the report layout stated by me in my post no 4.

Also Unique and filter option is not available in office 365. But what i see is that once we get the unique name then all formulas that i have used are available in office 2013. Getting unique names can be done using other excel formula

So first please confirm the reporting format as suggested by me in post 4
 
Upvote 0
rcahayag1925 said:
I am currently using an old excel version which is 2013.
Click to expand...
Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

1595914252579.png
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,155
Messages
6,123,332
Members
449,098
Latest member
thnirmitha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top