Compare two columns and remove duplicate data without leaving a blank space

[Selinor]

Hi all-

I am trying to make a custom work week calendar that removes weekends and custom holidays. I was able to get rid of weekends easy enough, but I am thus far unable to remove the custom holiday list from the work weeks.

I ONLY want a list of non-holiday, non-weekends.

I have tried two formulae to remove the custom holidays listed in column d.

The MOST successful formulae is:
=INDEX($E$3:$E$328,AGGREGATE(15,3,($E$3:$E$328<>$D3)/($E$3:$E$328<>$D3)*(ROW($E$3:$E$328)),ROWS(H3:$H$3)))

This ^^ formula made the list in column F of the example spreadsheet. I have highlight all the values in column F that SHOULD have been removed from this list, as they are in the list of holidays.

=IFERROR(LOOKUP(2,1/(COUNTIF(F3:$F$3,$E$328)=0),$E$328),LOOKUP(2,1/(COUNTIF(F3:$F$3,$D$3:$D$30)=0),$D$3:$D$30))

This formula is in cell G3 of the example spreadsheet and you can see that it is right out to lunch.

Thanks!

[AliGW]

08 January 2020 is NOT listed as a holiday, though - 08 January 2021 is.

Anyway, try this in J3 drag copied down:

=WORKDAY.INTL($A$3,ROW(J3)-2,1,$D$3:$D$30)

Format the cell as a date, obviously.

[Selinor]

That worked perfectly! Thanks!

Can I ask for a bit more explanation so I can replicate this formula in other sheets later if I need it?

Specifically for the " number of days" argument the Workday formula requires? You have a blank cell selected for row, -2. It's the minus 2 I am curious about as to why it is necessary.

Thanks!

[AliGW]

So, this bit:

ROW(J3)-2

looks at the row that J3 is on and returns 3, then takes away 2 - this leaves 1. This means that the formula will find the first day after the start date in A3 that is a working day.

As you drag copy down, this number increments one at a time, so each row returns the next working day and then the next and the next and so on.

Hope this helps.

=WORKDAY.INTL($A$3,ROW(J3)-2,1,$D$3:$D$30)

Sets weekend as Saturday and Sunday.

=WORKDAY.INTL($A$3,ROW(J3)-2,1,$D$3:$D$30)

References holiday dates.

I presume you know about the Evaluate Formula feature on the Formulas ribbon?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.