SOLVED

Pull data between two dates from multiple tables

Copper Contributor

I am trying to filter between two dates based on the start and end dates of classes. I can't figure out how to sort through different tables and the formula I was given doesn't pull the exact dates that I need. I want to be able to add classes as time goes on, so the sheet should be able to update based on the two dates at the top of the sheet 'Snapshot'. As of now, it should be pulling class [A-001 - A-004], [B-002 - B-010], and [C-001 - C-010].  

9 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Kupka8 

I cleaned up the formula in A7 visually (splitting it across multiple lines), but left it essentially unchanged, to make it easier to understand. The main problem is that it relies on the WEEKNUM function (calculating the week of the year), which would make sense if individual classes did not cover multiple calendar years. But as they do, a different technique is needed.


I believe what you want is this formula, in cell A21 in the attached workbook:

 

=LET(tasks, SORT(VSTACK(TaskA, TaskB, TaskC), {1,2}),
    start_dates, TAKE(DROP(tasks, , 1), , 1),    end_dates, TAKE(tasks, , -1),
    period_start, calendar_start,    period_end, $D$1,
    FILTER(tasks, (start_dates <= period_end) * (end_dates >= period_start), "none")
)

 

When you have tested that and are satisfied with the results, replace the formula in A7 with the formula in A21, and delete the latter.


Some other changes I made (such as to row/column widths and to how Snapshot's freeze panes are defined) are inconsequential, and you can change them as you wish.


Also:

  • The End Date for class C-007 looks like a date value, but is actually stored as text (despite the Date formatting; compare the values shown in the formula bar for that cell and the cell beneath it). I verified that using a formula with the TYPE function. I'll let you review that cell's value and edit it as appropriate.
  • The formula in D1 of Snapshot was unnecessarily complicated; I recommend instead 

 

=calendar_start + 56

 

although maybe you want to add 55 to cover exactly 8 weeks, instead of adding 56.

That seems to work how I would like it to. How would I add more arrays to that formula and do the arrays you have in the formula extend to A:A, B:B, etc? When I try to add another array, it comes up with a #NUM error

@Kupka8 

The arrays in that formula are defined just as in the workbook you supplied; I made no changes there.  As currently defined in Formulas → Name Manager, each array can have up to 999 rows (worksheet rows 2 thru 1,000):

2023-09-20.png

and that number of rows can be readily expanded as needed.  (You are not required to use dynamic named arrays such as TaskA, TaskB, TaskC, but consistency is nice.)

 

For help with your #NUM error, you'll have to show your work.  Either attach your updated workbook (or a subset thereof) or post the formulas you now have in the Snapshot column A cell and for any names you added to/changed in Name Manager.

 

I figured it out, thank you!!

@SnowMan55 

 

I can add the additional classes, but I want to add milestone dates as well.  When I try to do so, I get a #N/A error.  

@Kupka8 

By introducing milestone dates (between the Start Date and End Date columns of the dynamic named range TaskF only), you are changing the structure of the "tasks" variable within the A7 formula. The FILTER function within that formula then fails because it is then working on #N/A values that the VSTACK function generated as a result of the different structures.


You can see this difference in structure in the attached workbook, as shown by the results from A7 (where I have removed the reference to TaskF) and A21 (where I have included only TaskA and TaskF, showing results before any filtering).


Based on the large number of defined Task"x" ranges you have defined, it appears that this is the wrong approach, even before this difference in range structure is involved. Tell us if you see some value in having all those separate ranges, but I see a simpler, consistent structure in using one range (or better, one Excel table) for all the class-specific data.


That is demonstrated on the "Class Dates (2)" worksheet. Using references to the Excel table tblClasses (that I created from your data) makes for simpler maintenance, and simpler and clearer formulas (on "LRTC (2)").


The inclusion of milestone dates in columns C, D, and E of LRTC hints that you want to use those in conditional formatting also. The last selected class (G-002) on "LRTC (2)" has data that demonstrates one possible formatting scenario, using the following rules:
2023-09-22.png


For additional consistency and clarity, I put the start and end dates of the period of interest into cells A1 and D1. The associated text is defined in two custom formats. (Besides, the calendar starts on or before what was previously called "Calendar Start Date". The calendar is coded to always start on a Sunday.)


Using tblClasses and changing some formulas, nearly all of the Name Manager entries can become unnecessary.

@SnowMan55 


Its easier to add the new classes to the task "x" ranges.  I have roughly 250 classes and when the next class year list comes out, there will be even more.  Being able to easily see which classes need to be added or removed seems easier to do when they are arranged how I had them originally. 

 

I am adding the milestone data for conditional formatting, and I would say I would need 10 milestones for the longest class.  

@SnowMan55

How would I add the filter function to the formula you have in A7? I can't seem to get it to work correctly.

@Kupka8 

The FILTER function is already present in the A7 formula (whether you are talking about the one on sheet 'LRTC' or the one on sheet 'LRTC (2)').

 

Assuming that you want a criterion to subset the results (one you would use AND to describe, rather than OR), you wrap the syntax for your new row-related evaluation in parentheses and multiply the existing second parameter of the FILTER function by that.  Notice how the FILTERs in both formulas are already doing such a multiplication.

 

Note that you can post the code for a formula without having to upload a workbook. After you click "Reply" and the "Enter reply text" box appears, click "Open full text editor" beneath it to get additional capabilities from a message toolbar; the tool on the far right opens a popup where you can paste your formula.  (Position the text cursor appropriately within/after your text before you click that tool button.)

2023-09-29 AK.jpg

 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Kupka8 

I cleaned up the formula in A7 visually (splitting it across multiple lines), but left it essentially unchanged, to make it easier to understand. The main problem is that it relies on the WEEKNUM function (calculating the week of the year), which would make sense if individual classes did not cover multiple calendar years. But as they do, a different technique is needed.


I believe what you want is this formula, in cell A21 in the attached workbook:

 

=LET(tasks, SORT(VSTACK(TaskA, TaskB, TaskC), {1,2}),
    start_dates, TAKE(DROP(tasks, , 1), , 1),    end_dates, TAKE(tasks, , -1),
    period_start, calendar_start,    period_end, $D$1,
    FILTER(tasks, (start_dates <= period_end) * (end_dates >= period_start), "none")
)

 

When you have tested that and are satisfied with the results, replace the formula in A7 with the formula in A21, and delete the latter.


Some other changes I made (such as to row/column widths and to how Snapshot's freeze panes are defined) are inconsequential, and you can change them as you wish.


Also:

  • The End Date for class C-007 looks like a date value, but is actually stored as text (despite the Date formatting; compare the values shown in the formula bar for that cell and the cell beneath it). I verified that using a formula with the TYPE function. I'll let you review that cell's value and edit it as appropriate.
  • The formula in D1 of Snapshot was unnecessarily complicated; I recommend instead 

 

=calendar_start + 56

 

although maybe you want to add 55 to cover exactly 8 weeks, instead of adding 56.

View solution in original post