You're welcome. Glad it worked for you. Thanks for the follow-up.
I'll try for an explanation using row 2 in post 8 as the example
The formula looks at each cell in the relevant row range & joins (CONCAT) the relevant return related to that cell
If that cell is empty then return "" and move to the next one. That is the case for D2 in the example. So we have ""
If the cell is not empty and
not the same as the cell immediately left then return the date from row 1 and a "|" delimiter. That is the case for E2 so we get "45383|" with 45383 being the relevant number for the 2/04/24 date.
The formula also looks at that same cell and if it is not the same as the cell immediately to the right it does something else (see below) otherwise, as is the case for E2 where the cell to the right is the same as E2, returns "". So our return so far is still "45383|"
Moving to F3 it is not blank and
is the same as the cell immediately left so the first part returns "" but ..
.. the formula also looks at that same cell and if it is
not the same as the cell immediately to the right (that is the case this time) it returns the date from row 1 (45384 for 3/04/24) and a "|" and the value of the cell it is currently looking at "A" (from F3) & another "|"
So far then we have altogether
"45383|45384|A|"
After processing the whole D2:T2 range the string will have built up to
"45384|45385|A|45387|45389|C|45391|45392|B|45394|45395|C|45397|45397|A|45398|45398|B|"
That text is then split at every "|"
DROP the final (empty) value from after that final "|"
Convert all the text numbers back to real numbers so the dates can be formatted and there you have it.
Hope that made sense.
Thanks for taking the time to explain this, it is very helpful! I have never used the LET function. I think I understand generally, very smart solution.
What in the formula causes it to step from one cell to the next within the row, checking all the values? Is that just inherent in the function?
And what does the double dash do in the IFERROR part?
With your formula, I am able to achieve what I was trying to do in my setup - thanks again! However, my setup may be a little clunky and things slow down quickly as I am populating it with data. I am therefore wondering if you might have an idea for a more elegant and faster solution. Here is the (simplified) data I am collecting (under the Right 1 - 6 row):
| Title | Right | | | | | | Type | Start Date | End Date |
| Right 1 | Right 2 | Right 3 | Right 4 | Right 5 | Right 6 | | | |
| Title 1 | √ | | | √ | | | NX | 4/1/2024 | 6/30/2024 |
| Title 1 | | √ | √ | | √ | √ | H | 4/1/2024 | 6/30/2024 |
| Title 1 | | | √ | | | | X | 9/1/2024 | 9/31/2024 |
| Title 2 | | √ | | | | | X | 5/1/2024 | 5/31/2024 |
| Title 2 | √ | | | | | | H | 5/1/2024 | 5/31/2024 |
There are any of titles. Each title can have any number of entries (rows), specifying which rights have been licensed for which period, and whether they have been licensed exclusively (Type X) or non-exclusively (Type NX). The entries may also specify which rights may not be licensed during a specific period (Type H = holdback). So the data specifies during which period certain rights are not available or only available non-exclusively.
What I am looking to determine, from a specific date (e.g. today 2/27/2024) going forward, for any given title, what are the periods where rights are still available for license, either exclusively or non-exclusively. Based on the above, the results should be:
Title 1:
Right 1 - 2/27/2024, 3/31/2024, Available - 4/1/2024, 6/30/2024, Non-exclusive - 7/1/2024, (last date specified for title), Available
Right 2 - 2/27/2024, 3/31/2024, Available - 7/1/2024, (last date specified for title), Available
Right 3 - 2/27/2024, 3/31/2024, Available - 7/1/2024, 8/31/2024, Available - 10/1/2024, (last date specified for title), Available
etc.
Title 2:
Right 1 - 2/27/2024, 4/30/2024, Available - 6/1/2024, (last date specified for title), Available
etc.
Hopefully that makes sense.
I don't want to bore you with explaining what I did, but essentially for each entry above, next to it, I laid out for each day of the year (one column being one day of the year), over 10 years, which rights have been licensed or held back how (X, NX, H), by using IF functions strung together. Then on a separate sheet, again for each day of the year, over 10 years, I used COUNTIFS to check whether, across all entries for a given title, each right is Available or available Non-exclusively and marked that date as such (nothing if not available on a given date). That is how I ended up with series of Available, Non-exclusive, or nothing, to which I then applied your formula to pull start and end dates of each series (originally marked A, B, C in my post).
Creating these daily grids for 10 years twice with long formulas in each cell going down many rows seems to slow down things a lot quickly, so if there is a way to avoid having to go that route and getting the same results based on the above data with less computing power, that would be a lot better. For now the current solution works, but the work process is slow, so in case you have any ideas how to get the same results without those daily grids, that would be super helpful. I haven't been able to think of anything, although I could imagine it could be done with VBA, but if it isn't necessary, I would rather avoid it...
