Date range search where there is overlap

[MephistophelesUK]

I am really struggling on this one. At about the 5 hour mark of going round in circles and getting nowhere

.


I want my second table M5:O89 to query the data in the "To and From" columns of my first table and where there is overlapping dates - pull the information from there.

Easy to do in the numeric values using SUMIF's - so where there is overlap from Oct 72-74 the SUMIF returns 45 rather than just 30.

However, I also want to extract the text data - so for Oct 70 to Sept 72 it would return "A", but from Oct 72 to April 74 - "A,B" ... and so on.

I don't just want to check the six cells that are populated in my example so naming them won't work - it has to check the range.

I have had a suggestion of using TEXTJOIN but don't have 365 subscription so that doesn't seem to work.

There must be a way to do it but I am well and truly flummoxed.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Date range search where there is overlap
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[MephistophelesUK]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Date range search where there is overlap
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies - I had missed this.

It is a Cross-Post as the two solutions offered elsewhere have not resolved the problem. The original question was posted on Excel Forum under: Date range search where there is overlap

I cannot see a method of amending my original post to include that information.

 

[Fluff]

If you only have a few cells then you can use something like

=MID(IF(MEDIAN([@Month],$C$6,$D$6)=[@Month],", "&$F$6,"")&IF(MEDIAN([@Month],$C$7,$D$7)=[@Month],", "&$F$7,"")&IF(MEDIAN([@Month],$C$8,$D$8)=[@Month],", "&$F$8,""),3,100)

Otherwise you will need a macro.

 

[MephistophelesUK]

If you only have a few cells then you can use something like

=MID(IF(MEDIAN([@Month],$C$6,$D$6)=[@Month],", "&$F$6,"")&IF(MEDIAN([@Month],$C$7,$D$7)=[@Month],", "&$F$7,"")&IF(MEDIAN([@Month],$C$8,$D$8)=[@Month],", "&$F$8,""),3,100)

Otherwise you will need a macro.

Thanks - that seems the cleanest answer I've had and caters for the potentially different values in the Type column.

I need to apply across a range of 15 lines minimum rather than just the three that are populated at present - so 30 in total - I'm guessing that is going to take me outside of this solution?

 

[Fluff]

You can expand it by adding the other cells to check in the same manner.