Filter with TextJoin: Multiple Date Ranges in Non-Consecutive Cells

P

Pulsar3000

New Member
Joined
Apr 19, 2021
Messages
44
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone:

I'm trying to compare a date to multiple date ranges in non-consecutive cells and if the date falls between the pairs, I want the value preceding the "Start Date" returned.

In the attached example, I want the various colors returned in cell F4 whose date ranges contain the date in cell E4.

I also want the color returned for each occurrence the E4 date was found in the various date ranges. I think the formula in F4 needs the "unique" portion removed.

In the real file I'll be doing this formula in, there will be various dates looked up not just one like in this example but I presume I can just reference the cell with the actual date that needs to be looked up in the formula that I ultimately receive from you.

Look forward to your responses!!

Thank You!
Non-Consecutive Date Ranges.xlsx
EFGHIJKLMNOP
3DateFormula
43/4/2021#VALUE!
5
6Start DateEnd DateStart DateEnd DateStart DateEnd Date
7Blue1/1/20211/7/2021Something 1Something 2Green3/4/20213/15/2021Something 3Yellow3/4/20214/30/2021
Sheet2
Cell Formulas
RangeFormula
F4F4=TEXTJOIN(", ",TRUE,UNIQUE(FILTER((E7)+(J7)+(N7),(F7,K7,O7>=E4)*((G7,L7,P7)<=E4),"")))
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Is it this that you are after?
Note that my dates are in d/m/y format.

21 06 17.xlsm
EFGHIJKLMNOP
3DateFormula
44/03/2021Green, Yellow
5
6Start DateEnd DateStart DateEnd DateStart DateEnd Date
7Blue1/01/20217/01/2021Something 1Something 2Green4/03/202115/03/2021Something 3Yellow4/03/202130/04/2021
Date Range
Cell Formulas
RangeFormula
F4F4=TEXTJOIN(", ",TRUE,IF(F6:O6="Start Date",IF(F7:O7<=E4,IF(G6:P6="End Date",IF(G7:P7>=E4,E7:N7,""),""),""),""))
 
Upvote 0
Peter_SSs said:
Is it this that you are after?
Note that my dates are in d/m/y format.

21 06 17.xlsm
EFGHIJKLMNOP
3DateFormula
44/03/2021Green, Yellow
5
6Start DateEnd DateStart DateEnd DateStart DateEnd Date
7Blue1/01/20217/01/2021Something 1Something 2Green4/03/202115/03/2021Something 3Yellow4/03/202130/04/2021
Date Range
Cell Formulas
RangeFormula
F4F4=TEXTJOIN(", ",TRUE,IF(F6:O6="Start Date",IF(F7:O7<=E4,IF(G6:P6="End Date",IF(G7:P7>=E4,E7:N7,""),""),""),""))
Click to expand...
Hi @Peter_SSs

That does resolve my OP question but I just realized I made a mistake in my OP :(. There's an additional criteria that it needs to match before checking the various date ranges. I have added "Type" in Column D.

What I'm looking for is to compare the E4 date to the various date ranges but only if the type in cell D4 matches the items in cells D9 and D10.

In addition, the Start and End Dates actually have a description with a sequence that I didn't previously mention but I was able to figure that part out.

You think you can help with this modified inquiry? Apologies for my mistake.
Non-Consecutive Date Ranges.xlsx
DEFGHIJKLMNOPQ
1Vague Description 1 Start DateVague Description 1 End Date
2Vague Description 2 Start DateVague Description 2 End Date
3TypeDateFormulaVague Description 3 Start DateVague Description 3 End Date
4Option 23/8/2021Green | Yellow
5
6
7
8TypeItem 1Vague Description 1 Start DateVague Description 1 End DateSomething 1Something 2Item 2Vague Description 2 Start DateVague Description 2 End DateSomething 3Item 3Vague Description 3 Start DateVague Description 3 End Date
9Option 1Blue1/1/20211/7/2021Something 1Something 2Green2/3/20213/15/2021Something 3Yellow3/7/20214/30/2021
10Option 2Orange2/3/20212/17/2021Something 1Something 2Pink2/18/20213/7/2021Something 3Gray2/3/20213/23/2021
Sheet2
Cell Formulas
RangeFormula
F4F4=TEXTJOIN(" | ",TRUE,IF(F8:O8=H1:H3,IF(F9:O9<=E4,IF(G8:P8=I1:I3,IF(G9:P9>=E4,E9:N9,""),""),""),""))
 
Upvote 0
Assuming you have the LET function, try this one.

21 06 17.xlsm
DEFGHIJKLMNOP
1
2
3TypeDateFormula
4Option 28/03/2021Gray
5
6
7
8TypeItem 1Vague Description 1 Start DateVague Description 1 End DateSomething 1Something 2Item 2Vague Description 2 Start DateVague Description 2 End DateSomething 3Item 3Vague Description 3 Start DateVague Description 3 End Date
9Option 1Blue1/01/20217/01/2021Something 1Something 2Green3/02/202115/03/2021Something 3Yellow7/03/202130/04/2021
10Option 2Orange3/02/202117/02/2021Something 1Something 2Pink18/02/20217/03/2021Something 3Gray3/02/202123/03/2021
Date Range (2)
Cell Formulas
RangeFormula
F4F4=LET(rng,INDEX(E9:P10,MATCH(D4,D9:D10,0),0),seq,SEQUENCE(,10),TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(INDEX(rng,seq+1)<=E4,IF(RIGHT(G8:P8,6)="d Date",IF(INDEX(rng,seq+2)>=E4,INDEX(rng,seq),""),""),""),"")))
 
Upvote 0
Peter_SSs said:
Assuming you have the LET function, try this one.

21 06 17.xlsm
DEFGHIJKLMNOP
1
2
3TypeDateFormula
4Option 28/03/2021Gray
5
6
7
8TypeItem 1Vague Description 1 Start DateVague Description 1 End DateSomething 1Something 2Item 2Vague Description 2 Start DateVague Description 2 End DateSomething 3Item 3Vague Description 3 Start DateVague Description 3 End Date
9Option 1Blue1/01/20217/01/2021Something 1Something 2Green3/02/202115/03/2021Something 3Yellow7/03/202130/04/2021
10Option 2Orange3/02/202117/02/2021Something 1Something 2Pink18/02/20217/03/2021Something 3Gray3/02/202123/03/2021
Date Range (2)
Cell Formulas
RangeFormula
F4F4=LET(rng,INDEX(E9:P10,MATCH(D4,D9:D10,0),0),seq,SEQUENCE(,10),TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(INDEX(rng,seq+1)<=E4,IF(RIGHT(G8:P8,6)="d Date",IF(INDEX(rng,seq+2)>=E4,INDEX(rng,seq),""),""),""),"")))
Click to expand...
@Peter_SSs

Unfortunately, I don't have the let function. What would be an alternative?
 
Upvote 0
Without LET Peter's formula becomes.
Excel Formula: 
=TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(INDEX(INDEX(E9:P10,MATCH(D4,D9:D10,0),0),SEQUENCE(,10)+1)<=E4,IF(RIGHT(G8:P8,6)="d Date",IF(INDEX(INDEX(E9:P10,MATCH(D4,D9:D10,0),0),SEQUENCE(,10)+2)>=E4,INDEX(INDEX(E9:P10,MATCH(D4,D9:D10,0),0),SEQUENCE(,10)),""),""),""),""))
 
Upvote 0
Peter_SSs said:
=LET(rng,INDEX(E9:P10,MATCH(D4,D9:D10,0),0),seq,SEQUENCE(,10),TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(INDEX(rng,seq+1)<=E4,IF(RIGHT(G8:P8,6)="d Date",IF(INDEX(rng,seq+2)>=E4,INDEX(rng,seq),""),""),""),"")))
Click to expand...

Joe4 said:
If you are using 365, you should have access to the LET function.
If you do not see it, see this link: Excel 365 LET Function not recognized
Click to expand...
@Joe4

I double checked. I do have the let function in Microsoft online version but not other people using this do so I don't want the formula to break because they don't have the proper version for this particular function.

Thank you for the heads up though!
 
Upvote 0
Fluff said:
Without LET Peter's formula becomes.
Excel Formula: 
=TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(INDEX(INDEX(E9:P10,MATCH(D4,D9:D10,0),0),SEQUENCE(,10)+1)<=E4,IF(RIGHT(G8:P8,6)="d Date",IF(INDEX(INDEX(E9:P10,MATCH(D4,D9:D10,0),0),SEQUENCE(,10)+2)>=E4,INDEX(INDEX(E9:P10,MATCH(D4,D9:D10,0),0),SEQUENCE(,10)),""),""),""),""))
Click to expand...
@Fluff

Thank You! This seems to be doing what I want.

Why is Sequence 10? Why is there a +1, +2, and nothing added after the Sequence function?

I want to understand the function as I need to reuse it on other files but it doesn't seem to be working on those. I suspect is the Sequence function as there are various ranges of columns.
 
Upvote 0
Before dissecting that formula, what about this simpler one instead?

Pulsar3000.xlsm
DEFGHIJKLMNOP
3TypeDateFormula
4Option 18/03/2021Green, Yellow
5
6
7
8TypeItem 1Vague Description 1 Start DateVague Description 1 End DateSomething 1Something 2Item 2Vague Description 2 Start DateVague Description 2 End DateSomething 3Item 3Vague Description 3 Start DateVague Description 3 End Date
9Option 1Blue1/01/20217/01/2021Something 1Something 2Green3/02/202115/03/2021Something 3Yellow7/03/202130/04/2021
10Option 2Orange3/02/202117/02/2021Something 1Something 2Pink18/02/20217/03/2021Something 3Gray3/02/202123/03/2021
Date Range (2)
Cell Formulas
RangeFormula
F4F4=TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(F9:O10<=E4,IF(G9:P10>=E4,IF(D9:D10=D4,E9:N10,""),""),""),""))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
147
AlfredDB
A
C
  • Solved
VLOOKUP not working for a small range of dates
Replies
1
Views
192
awoohaw
awoohaw
T
  • Question
Multiple filters on filtered list
Replies
1
Views
258
Cubist
Cubist
N
  • Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
189
Alex Blakenburg
A
J
  • Solved
Advanced Filter using a date as the criteria
Replies
5
Views
357
Alex Blakenburg
A

Forum statistics

Threads
1,215,523
Messages
6,125,321
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top