FILTER not matching records to dates.

[Ochimus]

Attached has list of scheduled meetings in Cols B - F taking place in whatever month is selected in D3. (For this sample month is set at March 2023).
For each meeting, the Title, Custodian and Summary entries (Cols D - F) need to be transposed into the column in Cols I - V matching the value in Col B.

Required result is shown manually in rows 20 - 34.

Day Dates in rows 5 and 12 are set by formula, because first and last days will vary across months, and that works perfectly.

Extracted the "D" values in Col B from the dates in Col C, so a Filter formula could look for the relevant matches, and that works perfectly:

Tried filtering the Table with following formula to copy across the Title, Custodian and Summary into rows 6 - 8 or 13 - 15 (depending on the date). E.g:

J6: =IF(J$5="","",FILTER(Table2[[#All],[Title]],Table2[[#All],[D]]=J$5))
J7: =IF(J$5="","",FILTER(Table2[[#All],[Custodian]],Table2[[#All],[D]]=J$5))
J8: =IF(J$5="","",FILTER(Table2[[#All],[Summary]],Table2[[#All],[D]]=J$5))

(If there is no number in Col whatever in row 5, cell blank. Otherwise filter Cols D, E and F for row(s) where Col B matches the value in row 5, and copy whatever to rows 6 - 8),

1. Format is throwing #CALC!, but can't see why?

2. Can't see how to set a Second meeting on same day (e.g. C10) so it starts on row 9 instead of row 6?

All solutions, suggestion or alternatives welcome as ever.

Ochimus

[CARACALLA]

=IF(C8="","",TEXT(C8,"d"))+0

copy down



This change a text in number

after

=TRANSPOSE(IF(N$5="","",FILTER(Table2[[Title]:[Summary]],Table2[D]=N5)))

Transpose change a horizontal array in vertical

[raptor_k7]

Hi, Refer updated file.

Please enter the following formula in B8 and simply copy the formula down to the other cells in column B.

Please Login or Register  to view this content.

Please enter the following formula in J6 and simply update the formula in other cells in column J as per your requirement.

Please Login or Register  to view this content.

PS: You will get a SPILL error when multiple meetings in a particular day. Will try a workaround to overcome this issue.

[Ochimus]

Many thanks to both for prompt responses and suggestions.
Only challenge remaining is to work out how to cope with a second meeting on same date?
Perhaps the formula could incorporate a "Count" element that will find and copy the second entry of that Day into the fourth to sixth rows of the relevant date?

Ochimus

[Ochimus]

Worked it out.
Added a "Count" column in Col A (=COUNTIF(B$8:B8,B8), then changed the formulae in the Calendar to an XLOOKUP:
J6=IFERROR(IF(XLOOKUP(1&J5,$A$8:$A$1000&$B$8:$B$1000,$D$8:$D$1000)=0,"",XLOOKUP(1&J5,$A$8:$A$1000&$B$8:$B$1000,$D$8:$D$1000)),"")
J9=IFERROR(IF(XLOOKUP(2&J5,$A$8:$A$1000&$B$8:$B$1000,$D$8:$D$1000)=0,"",XLOOKUP(2&J5,$A$8:$A$1000&$B$8:$B$1000,$D$8:$D$1000)),"")

(If Column A says 1, and Col B says the value in J5, J6 is the matching data in Col D. If there is no match to the date in Col J, cell stays blank. If Column A says 2, and Col B says the value in J5, J9 is the second matching value in Col D. If there is no second match, formula generates an error in row 9, so set cell to blank)

Can now mark this as Solved.

Ochimus