Create smaller table with dropout date list

Hi and welcome to the forum.

As for second part - automatic setting of current day on workbook opening, you shall save your file in macro-enabled format (xlsm. xlsb or xls) and add simple code to Thisworkbook module:

Please Login or Register  to view this content.

I have not included this, as the second part would return error, because there is no data for 27th in the sample file

The first part I'd do with an array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Analysis of excel formulas shall go from innermost part outside. So let's try:

Formula:

Please Login or Register  to view this content.

this takes four vertical cells from rows 6:9 from column laying A12 rows right from column A so with A12=1 it takes data from column B (remember - rows 6:9, so from range B6:B9: o;2;o;2 ), if A12 = 3 from column D (so from range D6:D9), etc.

Formula:

Please Login or Register  to view this content.

for each of 4 cells in such range formula compares it with o. If it is not o (for instance B7) then it takes its row number (7) and deducts 5, if it is o - then it thakes just empty text ("") so for A12=1 the bold part will give:
(empty text)
2
(empty text)
4

Formula:

Please Login or Register  to view this content.

this (having in mind that the formula is in cell A14) returns first smallest value of such 4 values. Of course it is 2. the formula is copied down, so in A15 it reads:

Formula:

Please Login or Register  to view this content.

so it finds second smallest value: 4
but in A16

Formula:

Please Login or Register  to view this content.

there is no third smallest value - so it returns an error.
So if in A14:A17 we would have only this bold part we would have there
2
4
#VAL!
#VAL!
now:

Formula:

Please Login or Register  to view this content.

we are taking from range A6:A9 the value from the cell from row determined on previpis step, so values from cells:
A7 value (Smith)
A9 value (Broke)
#VAL! (and no values for next cells - still errors)
#VAL!
and to deal with these errors there is outermost instruction IFERROR, which changes each error into empty text, leaving non-error values untouched
A7 value (Smith)
A9 value (Broke)
(empty text)
(empty text)

Hope it's clear now.