Hello Guys,
This is my first time posting on the forum and would appreciate getting some pointers on a formula that has been developed to extract dates, titles form a worksheet entitled "Monthly_Publication_List". The formula reads two columns of data (i.e. dates, non-dates) and a publication title based on a dropdown containing both the month and year. In the output, in front of each date, there is a unicode character that generates a book icon. All in all, the formula works perfectly, however, I would like to make the unicode character more flexible. In other words, not hard-code but retrieved from an additional column (i.e. C) in the Monthly_Publication_List worksheet. Here is the coding that should do this kind of processing, however, the unicode characters are being read and output from C in its entirety and not on a single title/date basis.
Here is the original and modified code that performs these operations.
Original code:
Modified code (not working as expected):
Date file snapshot:
Output (original not reading unicode from Column C)
View attachment 106754
Output (reading column C data to retrieve the unicode)
If any other information is required just let me know. Any help is greatly appreciated.
This is my first time posting on the forum and would appreciate getting some pointers on a formula that has been developed to extract dates, titles form a worksheet entitled "Monthly_Publication_List". The formula reads two columns of data (i.e. dates, non-dates) and a publication title based on a dropdown containing both the month and year. In the output, in front of each date, there is a unicode character that generates a book icon. All in all, the formula works perfectly, however, I would like to make the unicode character more flexible. In other words, not hard-code but retrieved from an additional column (i.e. C) in the Monthly_Publication_List worksheet. Here is the coding that should do this kind of processing, however, the unicode characters are being read and output from C in its entirety and not on a single title/date basis.
Here is the original and modified code that performs these operations.
Original code:
Excel Formula:
=LET(s,Monthly_Publication_List!$A$3:$B$1002,d,INDEX(s,,1),p,INDEX(s,,2),
t,SORT(FILTER(s,IFERROR(EOMONTH(--d,-1)+1,IF(ISNUMBER(SEARCH(TEXT(B18,"mmm"),d)),B18,0))=B18)),
u,INDEX(t,,1),
IF(OR(ISERR(t)),"",HSTACK(IFERROR(TEXTJOIN(CHAR(10),,UNICHAR(128218)&IFERROR(DAY(u),u)),""),TEXTJOIN(CHAR(10),,INDEX(t,,2)))))Modified code (not working as expected):
Excel Formula:
=LET(
s, Monthly_Publication_List!$A$3:$C$1002,
d, INDEX(s,,1),
p, INDEX(s,,2),
unichar_codes, INDEX(s,,3),
unichar_prefix, TEXTJOIN("", TRUE, IFERROR(UNICHAR(unichar_codes), "")),
filtered_data, FILTER(s, IFERROR(EOMONTH(--d,-1)+1, IF(ISNUMBER(SEARCH(TEXT(B21,"mmm"),d)), B21, 0)) = B21),
sorted_data, SORT(filtered_data),
dates, INDEX(sorted_data,,1),
titles, INDEX(sorted_data,,2),
formatted_dates, TEXTJOIN(CHAR(10), TRUE, IFERROR(unichar_prefix & IFERROR(DAY(dates), dates), "")),
formatted_titles, TEXTJOIN(CHAR(10), TRUE, titles),
IF(OR(ISERR(sorted_data)), "", HSTACK(formatted_dates, formatted_titles))
)Date file snapshot:
Output (original not reading unicode from Column C)
View attachment 106754
Output (reading column C data to retrieve the unicode)
If any other information is required just let me know. Any help is greatly appreciated.
