Aug 01 2022 11:49 PM
Hi, I am trying to generate a list (text) from my data and I couldn't get the ARRAYTOTEXT to work. I am using Microsoft 365 version 2206 64 bit (15330.20264 click-to-run), running on Windows 10. Please find sample file here.
My aim is to generate a list (in this sample, a list of activities done) by selecting the range of travel dates. For example, if the travel date range from 2018-2020, the generated list should contain all the activities done within the duration. The only input should be "selecting the date range".
Since I am working with text, I used ARRAYTOTEXT to join the contents of the arrays. This is possible when the cell range is typed into the ARRAYTOTEXT formula manually, but it ends up with "there's a problem with the formula" when I try to refer the cell range from another cell (ie. D2, D3 in the excel sheet 'generate list'). I used CELL function combined with ARRAYTOTEXT.
The expected result should be as in D6. The problem I faced happened in C6. Does anyone have an idea what other function can I use other than these 2 to generate the list, or what can I do to make them work?
Thank you!
Aug 02 2022 10:31 AM
Aug 02 2022 11:45 PM
Thanks for the suggestion. That will give a whole list of all events ever done (result from your formula in E6, versus the desired result in D6). I would like to display only those events happened within the range specified from date in C2 to C3.
Aug 02 2022 11:57 PM - edited Aug 03 2022 12:16 AM
@CFYeap Sorry! Misinterpreted your question. What Excel version are you on?
Edit: This one should work (not very pretty though):
=SUBSTITUTE(ARRAYTOTEXT(INDEX(Table2[Event],MATCH(C2,Table1[Date],0)):INDEX(Table2[Event],MATCH(C3,Table1[Date],0))),",",CHAR(10))
Aug 03 2022 12:58 AM
SolutionHi @CFYeap
Alternatively, in 'generate list'!D6:
=LET(
From, XMATCH(C2,Table1[Date]),
To, XMATCH(C3,Table1[Date]),
TEXTJOIN(CHAR(10),,INDEX(Table2[Event], SEQUENCE(To-From+1,,From)))
)
Aug 04 2022 01:41 AM
Aug 04 2022 01:59 AM
Aug 04 2022 03:46 AM
As variant
=TEXTJOIN( CHAR(10),,
XLOOKUP(C2, Table1[Date], Table2[Event]):
XLOOKUP(C3, Table1[Date], Table2[Event]))
Aug 03 2022 12:58 AM
SolutionHi @CFYeap
Alternatively, in 'generate list'!D6:
=LET(
From, XMATCH(C2,Table1[Date]),
To, XMATCH(C3,Table1[Date]),
TEXTJOIN(CHAR(10),,INDEX(Table2[Event], SEQUENCE(To-From+1,,From)))
)