narnian_uk
New Member
- Joined
- Jul 28, 2021
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
- MacOS
I am using Excel 16.54 (this computer is too old for a more recent version to be installed) and therefore don't have access to the BYROW function, and have five arrays that look something like this:
The distribution of text and TRUEs varies depending on how a table elsewhere in the workbook is filled in, and there could be more or fewer rows (but each array will always have the same number of rows as the others). The arrays are all contained on a worksheet (at I43#:M43#).
From these five arrays, I would like to produce one array that looks like this:
In effect, I want to do something like running TEXTJOIN on each row:
Any thoughts would be very welcome. I have what seems like a needlessly complex solution at the moment; it works, but I'm pretty sure there must be a neater solution that isn't dependent on checking the content of each array separately.
Array 1 | Array 2 | Array 3 | Array 4 | Array 5 |
Text 1 | Text 2 | TRUE | Text 3 | TRUE |
Text 4 | TRUE | Text 5 | TRUE | Text 6 |
TRUE | TRUE | Text 7 | Text 8 | TRUE |
The distribution of text and TRUEs varies depending on how a table elsewhere in the workbook is filled in, and there could be more or fewer rows (but each array will always have the same number of rows as the others). The arrays are all contained on a worksheet (at I43#:M43#).
From these five arrays, I would like to produce one array that looks like this:
Text 1; Text 2; Text 3 |
Text 4; Text 5; Text 6 |
Text 7; Text 8 |
In effect, I want to do something like running TEXTJOIN on each row:
Excel Formula:
TEXTJOIN("; ", TRUE, IF([some way of referencing the row]<>TRUE, [some way of referencing the row], ""))
Any thoughts would be very welcome. I have what seems like a needlessly complex solution at the moment; it works, but I'm pretty sure there must be a neater solution that isn't dependent on checking the content of each array separately.