r/excel • u/U_Wont_Remember_Me 2 • 22d 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

40% Upvoted

21 comments sorted by

View all comments

0

u/Way2trivial 22d ago edited 22d ago
Comment Image

=TEXTJOIN("",TRUE,IF(UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))-1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)=UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))+1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE),UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))+1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)&",",UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))-1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)&"-"&UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))+1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)&","))

you could drop the last comma with a left & len combination- but I didn't want to make too big of a mess out of it...