extracting unique data

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:
thank you CA_Punit, yes I will just use your work in post 4. F Column in your sheet was converted into "Date" format for the salary grade column, example 29-2, 30-3, etc..

I want also to get the highest level of "Salary grade"for each personnel.

Thank you
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
all right, maybe I can find some alternative for that unique filter formula..don't know yet how to do it right now
 
Upvote 0
Best and Simple is to use Pivot to get the Unique and Filtered Data Set.

However it can be done using frequency and Match function and other functions. I will forward you all once i am free as i am busy in office work.
 
Upvote 0
tried using the formulas in P and Q but resulted to #VALUE! and #N/A respectively
 
Upvote 0
EMPLOYEE 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 SEQ #EMPLOYEE NO.NAMESALARYMONTHS IN SERVICE CY2018OFFICE/ POSTGRADING
9014632.1 BEST (10%)ASPAC110ENSIGN, MARYBELLE30-1143,534.006781891ENSIGN, MARYBELLE1500856#VALUE!#N/A
951632TIGER, LUCILA29-2130,392.0061
980876CAYLOR, CAYMER28-2116,704.0052
971117LOFTON, MINDA28-2116,704.0073
002320HOAGLIN, THERESA28-2116,704.0054
820962GENEVA PM 115HOSEA, JACK30-3147,869.0085
970910PARNELL, JEFFIE29-1128,467.0076
124594METZER, TERRELL25-182,439.005
870814POPLAR, KACY20-348,457.005
032595FUSELIER, VERA20-147,037.0012
TOTAL BEST
7818912.2 BETTER (25%)ANKARA PE147ENSIGN, MARYBELLE30-4 150,085.00 6
002263TIGER, LUCILA29-2130,392.003
052810CAYLOR, CAYMER28-2116,704.005
146490LOFTON, MINDA28-2116,704.005
830278ASEAN113HOAGLIN, THERESA28-2116,704.007
911315HOSEA, JACK30-3147,869.004
961652PARNELL, JEFFIE29-1128,467.005
962074METZER, TERRELL25-182,439.007
801217BERNE PE114COOK, JERICO28-2 116,704.00 12
971797ASTRID, REIN28-2 116,704.00 4
991202HUNT, ANNE27-1 102,910.00 12
072855COLLINS, JOEL28-2 116,704.00 2
TOTAL BETTER
7622102.3 GOOD (65%)ABU DHABI PE143POPLAR, KACY20-348,457.007
931720TIGER, LUCILA29-2130,392.003
104210CAYLOR, CAYMER28-2116,704.002
781625ASTRID, REIN27-1 102,910.00 8
146570COLLINS, JOEL24-476,644.008
174913ABUJA PE126HOSEA, JACK30-4 150,085.00 4
841592RUSSEL, JACK28-2 116,704.00 12
042763HAMMOND, JILL27-1 102,910.00 12
921896CONLONM JAMES24-476,644.0012
TOTAL GOOD
 
Upvote 0
I think I am already good with the rest of the formula you have provided, except for these two, which results to
#VALUE! = result for Q3#N/A = result for R3
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"})

thank you
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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