Concatenating array rows without BYROW

[narnian_uk]

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:

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:

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.

 

[Fluff]

How about

=if(I43="","",TEXTJOIN("; ", TRUE, IF(I43:M43,i43:m43,"")))

and drag down

 

[narnian_uk]

Unfortunately, since the arrays vary in length, dragging down isn't an 'ideal' option; I'd prefer a resulting array which matches the number of rows of the 'input' arrays.

 

[Fluff]

If you don't have the lambda functions, then I don't think you have much choice.

 

[Anonymous1378]

Do you have access to TEXTSPLIT()?

 

[narnian_uk]

If you don't have the lambda functions, then I don't think you have much choice.

This is what I suspect, but wanted to check. Thanks.

Do you have access to TEXTSPLIT()?

No, sadly not.

 

[JEC]

How about a UDF?

Function jec(rng As Range) As Variant
 Dim ar, j As Long, jj As Long
 ar = rng
 ReDim ary(1 To UBound(ar), 0)
 
 For j = 1 To UBound(ar)
   For jj = 1 To UBound(ar, 2)
     If ar(j, jj) <> True Then ary(j, 0) = ary(j, 0) & IIf(ary(j, 0) = "", "", "; ") & ar(j, jj)
   Next
 Next
  
 jec = ary
End Function

Book1
	A	B	C	D	E	F	G	H
1	Array 1	Array 2	Array 3	Array 4	Array 5			Output
2	Text 1	Text 2	TRUE	Text 3	TRUE			Text 1; Text 2; Text 3
3	Text 4	TRUE	Text 5	TRUE	Text 6			Text 4; Text 5; Text 6
4	TRUE	TRUE	Text 7	Text 8	TRUE			Text 7; Text 8
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=jec(A2:E4)
Dynamic array formulas.

 

[Anonymous1378]

Do you have access to FILTERXML()?

 

[Fluff]

Not on a Mac unfortunately. It's Windows PC only.

 

[Anonymous1378]

Then I guess I'm out of ideas besides

=MID(SUBSTITUTE(SUBSTITUTE("; "&I43#&"|; "&J43#&"|; "&K43#&"|; "&L43#&"|; "&M43#&"|","; TRUE|",""),"|",""),3,999)