extracting unique data

[rcahayag1925]

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 NO

Ranking

Names of Bureaus/ Offices/Attached Agencies/ Delivery Units

Rate of Accomplishment of Targets (in %)

Names of Employee

Salary Grade (and Step Increment)

Monthly Basic Salary

Months in service in 2018

901463	2.1 BEST (10%)	ASPAC	110	ENSIGN, MARYBELLE	30-1	143,534.00​	6
951632				TIGER, LUCILA	29-2	130,392.00​	6
980876				CAYLOR, CAYMER	28-2	116,704.00​	5
971117				LOFTON, MINDA	28-2	116,704.00​	7
002320				HOAGLIN, THERESA	28-2	116,704.00​	5
820962		GENEVA PM	115	HOSEA, JACK	30-3	147,869.00​	8
970910				PARNELL, JEFFIE	29-1	128,467.00​	7
124594				METZER, TERRELL	25-1	82,439.00​	5
870814				POPLAR, KACY	20-3	48,457.00​	5
032595				FUSELIER, VERA	20-1	47,037.00​	12
	TOTAL BEST
781891	2.2 BETTER (25%)	ANKARA PE	147	ENSIGN, MARYBELLE	30-4	150,085.00	6
002263				TIGER, LUCILA	29-2	130,392.00​	3
052810				CAYLOR, CAYMER	28-2	116,704.00​	5
146490				LOFTON, MINDA	28-2	116,704.00​	5
830278		ASEAN	113	HOAGLIN, THERESA	28-2	116,704.00​	7
911315				HOSEA, JACK	30-3	147,869.00​	4
961652				PARNELL, JEFFIE	29-1	128,467.00​	5
962074				METZER, TERRELL	25-1	82,439.00​	7
801217		BERNE PE	114	COOK, JERICO	28-2	116,704.00	12
971797				ASTRID, REIN	28-2	116,704.00	4
991202				HUNT, ANNE	27-1	102,910.00	12
072855				COLLINS, JOEL	28-2	116,704.00	2
	TOTAL BETTER
762210	2.3 GOOD (65%)	ABU DHABI PE	143	POPLAR, KACY	20-3	48,457.00​	7
931720				TIGER, LUCILA	29-2	130,392.00​	3
104210				CAYLOR, CAYMER	28-2	116,704.00​	2
781625				ASTRID, REIN	27-1	102,910.00	8
146570				COLLINS, JOEL	24-4	76,644.00​	8
174913		ABUJA PE	126	HOSEA, JACK	30-4	150,085.00	4
841592				RUSSEL, JACK	28-2	116,704.00	12
042763				HAMMOND, JILL	27-1	102,910.00	12
921896				CONLONM JAMES	24-4	76,644.00​	12
	TOTAL GOOD

Result, wanted

"Commission" Sheet

BEST
SEQ #	EMPLOYEE NO.	NAME				SALARY GRADE	STEP	SALARY	MONTHS IN SERVICE CY2018	OFFICE/ POST (PBB PURPOSES)	GRADING (PBB PURPOSES)	SEPARATION TYPE	SEPARATION DATE
1	951632	TIGER, LUCILA				29	2	130,392.00​	6	ASPAC	BEST
2	980876	CAYLOR, CAYMER				28	2	116,704.00​	5	ASPAC	BEST
3	971117	LOFTON, MINDA				28	2	116,704.00​	7	ASPAC	BEST
4	820962	HOSEA, JACK				30	4	150,085.00	8	GENEVA PM	BEST
5	970910	PARNELL, JEFFIE				29	1	128,467.00​	7	GENEVA PM	BEST
6	032595	FUSELIER, VERA				20	1	47,037.00​	12	GENEVA PM	BEST
BETTER
SEQ #	EMPLOYEE NO.	NAME				SALARY GRADE	STEP	SALARY	MONTHS IN SERVICE CY2018	OFFICE/ POST (PBB PURPOSES)	GRADING (PBB PURPOSES)	SEPARATION TYPE	SEPARATION DATE
	781891	ENSIGN, MARYBELLE				30	4	150,085.00	6	ANKARA PE	BETTER
	830278	HOAGLIN, THERESA				28	2	116,704.00​	7	ASEAN	BETTER
	962074	METZER, TERRELL				25	1	82,439.00​	7	ASEAN	BETTER
	801217	COOK, JERICO				28	2	116,704.00	12	BERNE PE	BETTER
GOOD
SEQ #	EMPLOYEE NO.	NAME				SALARY GRADE	STEP	SALARY	MONTHS IN SERVICE CY2018	OFFICE/ POST (PBB PURPOSES)	GRADING (PBB PURPOSES)	SEPARATION TYPE	SEPARATION DATE
	781625	ASTRID, REIN				28	2.00	116,704.00	8	ABU DHABI PE	GOOD
	146570	COLLINS, JOEL				28	2.00	116,704.00	8	ABU DHABI PE	GOOD
	841592	RUSSEL, JACK				28	2	116,704.00	12	ABUJA PE	GOOD
	042763	HAMMOND, JILL				27	1	102,910.00	12	ABUJA PE	GOOD
	921896	CONLONM JAMES				24	4	76,644.00​	12	ABUJA PE	GOOD

Also posted here Extract unique data

 

[rcahayag1925]

Thank you, its working already. Can I ask, what does ZZZZZZ and 100 represents? Or can any letters will do for that formula? Thanks

 

[CA_Punit]

"ZZZZZ" is typically the Largest Word that Look-up recognises as per your data set.
and 100 is also the same it is the largest number that excel recognises as per your data set.

change 100 to 100000000 so that if you have data set containing more than 100 it will provide incorrect value. and i suppose ZZZZZ is fine

 

[rcahayag1925]

Thank you for the info.