How do I remove a duplicate value based on 2 column criteria?

LGarcia2022

I'm working on a vacation calendar for my team and also using the same sheet to list what tasks needs to be covered. If a person has more than 1 task to cover for a specific day, their name will show up multiple times for that day. I did an automation where if the Covering RA column and OOO Date column are not blank, it moves a copy to a new sheet. I wanted the new sheet to then delete any duplicates if the same person is listed more than once for that same date, but I'm having a hard time figuring out how to delete duplicates.

For example: If I have 2 tasks that need to be covered on 11/24, my name shows up twice on the calendar for 11/24.

I've tried using conditional formatting and IFS formula, but can't get it to work out quite the way I want it to. End goal is to remove the duplicate value from the new/second sheet and only keep one row. For instance, the 2nd row would automatically be deleted from the screenshot above.

Any suggestions on how to get this accomplished?

Paul Newcome

You can write the formula to dynamically reference the date cell.

Insert an auto-number column with no special formatting (called "auto" in this example) then use this formula in a checkbox type column:

=IF(COUNTIFS(Auto:Auto, @cell <= Auto@row, [OOO Date]:[OOO Date], @cell = [OOO Date]@row, [RA OOO]:[RA OOO], @cell = [RA OOO]@row) = 1, 1)

This will check off one box for each name/date combo.

LGarcia2022

Thank you, Paul!! It worked wonderfully! I appreciate your help! :)

Paul Newcome

Try using a helper checkbox column with a formula that will only check the box on one row for each name/date combo.

LGarcia2022

Thank you for the suggestion, but I don't think it would be ideal since 1 person could take off multiple dates, I would have to find a way to account for all the dates within a calendar year.

Paul Newcome

You can write the formula to dynamically reference the date cell.

Insert an auto-number column with no special formatting (called "auto" in this example) then use this formula in a checkbox type column:

=IF(COUNTIFS(Auto:Auto, @cell <= Auto@row, [OOO Date]:[OOO Date], @cell = [OOO Date]@row, [RA OOO]:[RA OOO], @cell = [RA OOO]@row) = 1, 1)

This will check off one box for each name/date combo.

LGarcia2022

Thank you, Paul!! It worked wonderfully! I appreciate your help! :)

Paul Newcome

Happy to help. 👍️