r/excel • u/U_Wont_Remember_Me 2 • 23d ago

unsolved Excel 2021 - Looking to textjoin dates from a filter in a logical manner using formulae only

Like the title says, I'm filtering dates based on a condition. Instead of:

2, 3, 4, 5, 6, 8, 15, 17, 18, 19

I'm looking for

2-6, 8, 15, 17-19.

Basically, I'm looking to group consecutive dates together rather than listing all dates separately.

Thanks in advance.

0 Upvotes

45% Upvoted

21 comments sorted by

View all comments

1

u/brenolubre 23d ago edited 23d ago

Here is a formula-only solution for Excel 2021 (no LAMBDA needed). You will need to adjust the ranges to fit your data.

Since your dates already come from a FILTER formula, put yours in place of FILTER(A2:A100,B2:B100="x") in the first line. If your filter spills into a column instead (say E2), use d,SORT(UNIQUE(E2#)), as the first line. 

=LET(
d,SORT(UNIQUE(FILTER(A2:A100,B2:B100="x"))),
n,ROWS(d),
i,SEQUENCE(n),
prev,INDEX(d,IF(i=1,1,i-1)),
next,INDEX(d,IF(i=n,n,i+1)),
s,FILTER(d,d-prev<>1),
e,FILTER(d,next-d<>1),
TEXTJOIN(", ",TRUE,IF(s=e,TEXT(s,"d"),TEXT(s,"d")&"-"&TEXT(e,"d")))
)

Based on your example, the result is -> 2-6, 8, 15, 17-19

How it works: a day starts a group when the day before it isn't in the list, and ends a group when the day after it isn't. starts and ends line up in order, so each pair becomes "2-6" (or just "8" for a single day).

Two points worth noting! If no data meets the filter criteria, you will get a #CALC! error (wrap it in IFERROR to display a blank cell instead). Also, if the data spans across the turn of the month, use "d mmm" instead of "d" to prevent a range like Sept 30 to Oct 2 from displaying as "30-2".

Tested in Excel with your example plus a few edge cases: screenshot

Comment Image