Need Help Deleteing 1 row from each group of Data

[TobyStuder]

I know my mind is blocked on this but i don't know where to even start. I have a dataset that is for employee travel that when this pay code is used you get paid for all days except the last one. i have been able to filter out the paycode and now have one line for each employee each day. My issue now is how do I remove 1 row. They may work this job 1 to 7 days. if they work it 1 day of course that is not removed but if they work 2 or more the last day is removed.

Example:

11ALLJEF	8/13/2018
11ALLJEF	8/14/2018
11ALLJEF	8/15/2018
11ALLJEF	8/16/2018
11ALLJEF	8/17/2018
11BECJAI	8/13/2018
11BECJAI	8/14/2018
11BECJAI	8/15/2018
11BECJAI	8/16/2018
11BECJAI	8/17/2018

<colgroup><col><col></colgroup><tbody>
</tbody>

for these two employees would both have the 8/17/2018 rows removed. So, i have 27 different employees to anaylze. Here is the whole dataset

Employee	Date	Job	Cost Code	Units	Pay ID
11ALLJEF	8/13/2018	11-17-7022	06-045	1.00	553
11ALLJEF	8/14/2018	11-17-7022	06-045	1.00	553
11ALLJEF	8/15/2018	11-17-7022	06-045	1.00	553
11ALLJEF	8/16/2018	11-17-7022	06-045	1.00	553
11ALLJEF	8/17/2018	11-17-7022	06-045	1.00	553
11BECJAI	8/13/2018	11-17-7064	06-045	1.00	553
11BECJAI	8/14/2018	11-17-7064	06-045	1.00	553
11BECJAI	8/15/2018	11-17-7064	06-045	1.00	553
11BECJAI	8/16/2018	11-17-7064	06-045	1.00	553
11BECJAI	8/17/2018	11-17-7064	06-045	1.00	553
11BECJOR	8/13/2018	11-17-7064	07-045	1.00	553
11BECJOR	8/14/2018	11-17-7064	07-045	1.00	553
11BECJOR	8/15/2018	11-17-7064	06-045	1.00	553
11BECJOR	8/16/2018	11-17-7064	06-045	1.00	553
11BECJOR	8/17/2018	11-17-7064	06-045	1.00	553
11BECJUA	8/13/2018	11-17-7064	06-045	1.00	553
11BECJUA	8/14/2018	11-17-7064	06-045	1.00	553
11BECJUA	8/15/2018	11-17-7064	06-045	1.00	553
11BECJUA	8/16/2018	11-17-7064	06-045	1.00	553
11BECJUA	8/17/2018	11-17-7064	06-045	1.00	553
11CANDAN	8/13/2018	11-17-7064	06-045	1.00	553
11CANDAN	8/14/2018	11-17-7064	06-045	1.00	553
11CANDAN	8/15/2018	11-17-7064	06-045	1.00	553
11CANDAN	8/16/2018	11-17-7064	06-045	1.00	553
11CANDAN	8/17/2018	11-17-7064	06-045	1.00	553
11CERJOE	8/13/2018	11-17-7053	05-045	1.00	553
11CERJOE	8/14/2018	11-17-7053	05-035	1.00	553
11CERJOE	8/15/2018	11-17-7053	05-035	1.00	553
11CERJOE	8/16/2018	11-17-7053	05-035	1.00	553
11CERJOE	8/17/2018	11-17-7053	05-035	1.00	553
11DIESAN	8/13/2018	11-17-7009	18-050	1.00	553
11DIESAN	8/14/2018	11-17-7009	18-802	1.00	553
11DIESAN	8/15/2018	11-17-7009	18-802	1.00	553
11DIESAN	8/16/2018	11-17-7009	18-802	1.00	553
11DIESAN	8/17/2018	11-17-7009	18-802	1.00	553
11DIZFRA	8/13/2018	11-17-7022	06-045	1.00	553
11DIZFRA	8/14/2018	11-17-7022	06-045	1.00	553
11DIZFRA	8/15/2018	11-17-7022	06-045	1.00	553
11DIZFRA	8/16/2018	11-17-7022	06-045	1.00	553
11DIZFRA	8/17/2018	11-17-7022	06-045	1.00	553
11FUNNAT	8/13/2018	11-17-7022	06-045	1.00	553
11FUNNAT	8/14/2018	11-17-7022	06-045	1.00	553
11FUNNAT	8/15/2018	11-17-7022	06-045	1.00	553
11FUNNAT	8/16/2018	11-17-7022	06-045	1.00	553
11FUNNAT	8/17/2018	11-17-7022	06-045	1.00	553
11GARFER	8/13/2018	11-17-7056	05-204	1.00	553
11GARFER	8/14/2018	11-17-7056	05-045	1.00	553
11GARFER	8/15/2018	11-17-7056	05-045	1.00	553
11GARFER	8/16/2018	11-17-7056	05-045	1.00	553
11GARFER	8/17/2018	11-17-7056	05-045	1.00	553
11GARFER	8/18/2018	11-17-7056	05-045	1.00	553
11GILJOH	8/13/2018	11-17-7064	06-204	1.00	553
11GILJOH	8/14/2018	12-14-4434	06-050	1.00	553
11GILJOH	8/15/2018	11-17-7064	06-045	1.00	553
11GILJOH	8/16/2018	11-17-7064	06-050	1.00	553
11GILJOH	8/17/2018	11-17-7064	06-045	1.00	553
11GUTEDU	8/13/2018	11-18-8018	17-050	1.00	553
11GUTEDU	8/14/2018	11-18-8018	05-045	1.00	553
11GUTEDU	8/15/2018	11-18-8018	05-045	1.00	553
11GUTEDU	8/16/2018	11-18-8018	05-045	1.00	553
11GUTEDU	8/17/2018	11-18-8018	05-045	1.00	553
11GUTPOR	8/13/2018	11-18-8018	05-214	1.00	553
11GUTPOR	8/14/2018	11-18-8018	05-045	1.00	553
11GUTPOR	8/15/2018	11-18-8018	05-045	1.00	553
11GUTPOR	8/16/2018	11-18-8018	05-045	1.00	553
11GUTPOR	8/17/2018	11-18-8018	05-045	1.00	553
11HOFDAN	8/13/2018	11-17-7022	06-045	1.00	553
11HOFDAN	8/14/2018	11-17-7022	06-045	1.00	553
11HOFDAN	8/15/2018	11-17-7022	06-045	1.00	553
11HOFDAN	8/16/2018	11-17-7022	06-045	1.00	553
11HOFDAN	8/17/2018	11-17-7022	06-045	1.00	553
11ITZEGA	8/13/2018	11-17-7009	17-804	1.00	553
11ITZEGA	8/14/2018	11-17-7009	18-802	1.00	553
11ITZEGA	8/15/2018	11-17-7009	18-802	1.00	553
11ITZEGA	8/16/2018	11-17-7009	18-802	1.00	553
11ITZEGA	8/17/2018	11-17-7009	18-802	1.00	553
11LOPEJO	8/13/2018	11-17-7053	05-045	1.00	553
11LOPEJO	8/14/2018	11-17-7053	05-035	1.00	553
11LOPEJO	8/15/2018	11-17-7053	05-035	1.00	553
11LOPEJO	8/16/2018	11-17-7053	05-035	1.00	553
11LOPEJO	8/17/2018	11-17-7053	05-035	1.00	553
11MARMJO	8/13/2018	11-17-7022	06-045	1.00	553
11MARMJO	8/14/2018	11-17-7022	06-045	1.00	553
11MARMJO	8/15/2018	11-17-7022	06-045	1.00	553
11MARMJO	8/16/2018	11-17-7022	06-045	1.00	553
11MARMJO	8/17/2018	11-17-7022	06-045	1.00	553
11MONALB	8/13/2018	11-17-7053	05-050	1.00	553
11MONALB	8/14/2018	11-17-7053	05-035	1.00	553
11MONALB	8/15/2018	11-17-7053	05-035	1.00	553
11MONALB	8/16/2018	11-17-7053	05-035	1.00	553
11MONALB	8/17/2018	11-17-7053	05-035	1.00	553
11NEGJON	8/13/2018	11-17-7053	05-035	1.00	553
11NEGJON	8/14/2018	11-17-7053	05-035	1.00	553
11NEGJON	8/15/2018	11-17-7053	05-035	1.00	553
11NEGJON	8/16/2018	11-17-7053	05-035	1.00	553
11NEGJON	8/17/2018	11-17-7053	05-035	1.00	553
11ORTJEN	8/13/2018	11-17-7056	05-045	1.00	553
11ORTJEN	8/14/2018	11-17-7056	05-045	1.00	553
11ORTJEN	8/15/2018	11-17-7056	05-045	1.00	553
11ORTJEN	8/16/2018	11-17-7056	05-045	1.00	553
11ORTJEN	8/17/2018	11-17-7056	05-045	1.00	553
11ORTJEN	8/18/2018	11-17-7056	05-045	1.00	553
11ROBJOS	8/13/2018	11-17-7056	05-045	1.00	553
11ROBJOS	8/14/2018	11-17-7056	05-045	1.00	553
11ROBJOS	8/15/2018	11-17-7056	05-045	1.00	553
11ROBJOS	8/16/2018	11-17-7056	05-045	1.00	553
11ROBJOS	8/17/2018	11-17-7056	05-045	1.00	553
11ROBJOS	8/18/2018	11-17-7056	05-045	1.00	553
11ROBSTE	8/13/2018	11-17-7064	06-050	1.00	553
11ROBSTE	8/14/2018	12-14-4434	06-050	1.00	553
11ROBSTE	8/15/2018	11-17-7064	06-045	1.00	553
11ROBSTE	8/16/2018	11-17-7064	06-045	1.00	553
11ROBSTE	8/17/2018	11-17-7064	06-045	1.00	553
11SALEDW	8/13/2018	11-18-8018	05-045	1.00	553
11SALEDW	8/14/2018	11-18-8018	05-045	1.00	553
11SALEDW	8/15/2018	11-18-8018	05-045	1.00	553
11SALEDW	8/16/2018	11-18-8018	05-045	1.00	553
11SALEDW	8/17/2018	11-18-8018	05-045	1.00	553
11SBRDAN	8/13/2018	11-17-7022	06-050	1.00	553
11SBRDAN	8/14/2018	11-17-7022	06-045	1.00	553
11SBRDAN	8/15/2018	11-17-7022	06-045	1.00	553
11SBRDAN	8/16/2018	11-17-7022	06-045	1.00	553
11SBRDAN	8/17/2018	11-17-7022	06-045	1.00	553
11VERCRU	8/13/2018	11-17-7009	05-045	1.00	553
11VERCRU	8/14/2018	11-17-7009	18-802	1.00	553
11VERCRU	8/15/2018	11-17-7009	18-802	1.00	553
11VERCRU	8/16/2018	11-17-7009	18-802	1.00	553
11VERCRU	8/17/2018	11-17-7009	18-802	1.00	553
11WRIJAS	8/13/2018	11-17-7022	06-045	1.00	553
11WRIJAS	8/14/2018	11-17-7022	06-045	1.00	553
11WRIJAS	8/15/2018	11-17-7022	06-045	1.00	553
11WRIJAS	8/16/2018	11-17-7022	06-045	1.00	553
11WRIJAS	8/17/2018	11-17-7022	06-045	1.00	553
11MEDAUG	8/13/2018	11-17-7056	05-045	1.00	553
11MEDAUG	8/14/2018	11-17-7056	05-045	1.00	553
11MEDAUG	8/15/2018	11-17-7056	05-045	1.00	553
11MEDAUG	8/16/2018	11-17-7056	05-045	1.00	553
11MEDAUG	8/17/2018	11-17-7056	05-045	1.00	553
11FERSER	8/13/2018	11-18-8018	05-045	1.00	553
11FERSER	8/14/2018	11-18-8018	05-045	1.00	553
11FERSER	8/15/2018	11-18-8018	05-045	1.00	553
11FERSER	8/16/2018	11-18-8018	05-045	1.00	553
11FERSER	8/17/2018	11-18-8018	05-045	1.00	553

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

any help or ideas would be awsome. I have looked all over the internet and haven't found anything helpful yet. I have never posted on one of these boards so please let me know what else is needed.

Regards,

 

[Yongle]

Welcome to the forum Toby

With your values in Columns A:F try this in G2 copy down

=IF(COUNTIF(A:A,A2)=1,"",IF(MAXIFS(B:B,A:A,A2)=B2,"XX",""))

 

[TobyStuder]

Thanks this formula works perfectly. I knew I was making it harder than is was.