Remove duplicate date excluding first and last in excel

Question

How to remove the all duplicate date excluding 1st and last date.

Please see the screenshot.

I would like to do the B column like D column.

Is it possible?

Thanks.

Answer 1

CHOOSE, ROW, MIN, MAX, DAY

If your Source Range is B2:B30 and your resulting data starts in D2, use the following array formula (in D2):

=CHOOSE(MOD((ROW()-ROW(D$2)),3)+1,IF(MIN(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))=0,"",MIN(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))),IF(MAX(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))=0,"",MAX(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))),"")

---

To input a formula as an array formula, you have to copy the formula to the formula bar and hold LEFT CTRL SHIFT and press ENTER.

The resulting formula will contain braces {} i.e. will look like this:

{=CHOOSE(MOD((ROW()-ROW(D$2)),3)+1,IF(MIN(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))=0,"",MIN(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))),IF(MAX(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))=0,"",MAX(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))),"")}

---

History

The formula is a combination of several formulas.

Array Formulas

=MIN(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))
=MAX(IF(DAY($B$2:$B$30)=INT((ROW()-ROW(D$2))/3)+1,$B$2:$B$30))

Normal Formulas

=INT((ROW()-ROW(D$2))/3)+1
=MOD((ROW()-ROW(D$2)),3)+1

And finally the CHOOSE formula

=CHOOSE(MOD((ROW()-ROW(D$2)),3)+1,1,2,3)

where instead of 1, 2 and 3 the various conditions are placed instead:

MIN, MAX and "".

Answer 2

Here's another variation (not explicitly array formula)

=IFERROR(
CHOOSE(MOD(ROWS($1:3),3)+1,
AGGREGATE(15,6,B$2:B$30/(INT(B$2:B$30)=AGGREGATE(15,6,INT(B$2:B$30)/(INT(B$2:B$30)>D1),1)),1),
AGGREGATE(14,6,B$2:B$30/(INT(B$2:B$30)=AGGREGATE(15,6,INT(B$2:B$30)/(INT(B$2:B$30)>D1),1)),1),
""),
"")

but because I am comparing the dates with the date 3 rows earlier (initially in D1) the formula has to be entered in D4 and D1 and D2 have to be left blank.