Concatenate an array

[Raan Ruuster]

This is my very first post on this forum, and I've exhausted my search to hopefully ensure that my exact question hasn't been answered before (though if I'm wrong, a link to the correct post would be greatly appreciated!). I consider myself much more advanced than an "average" user, but much much less advanced than probably anyone on this forum. I know I will probably have to get a UDF for this, but I'm incapable of coding in VBA. I'm having trouble with a scheduling form that I'm creating and would love a little input from someone who has more knowledge than me.

Disclaimer: My system is a 64 bit running excel 2010, so MCONCAT will not work for me, though I would think it would be close to solving my problem if it did.

What I have so far:

Through use of an IF array function, I now have an array that looks like this {"8:00breakfasttext1";FALSE;FALSE;"8:45swimtext4"}. This is simply a test, so I don't have the exact formatting of the text string perfect yet, but I don't suppose that matters at all (I know how to format it the way I want)​

What I need:

I need to concatenate this array in a given cell, skipping FALSE, and delimitted by a "NEW LINE" character [Char(10)]​

What it should look like in the given cell:

8:00breakfasttext1
8:45swimtext4​

I think that I've answered most questions concerning my situation, but if I've left anything out, let me know. Thanks so much for taking the time to look into this issue for me.

 

[circledchicken]

Hi and welcome,

Perhaps try this UDF:

Function concat(vArr As Variant) As String

concat = Replace(Join(Application.Transpose(vArr), Chr(10)), "False" & Chr(10), "")

End Function

Below is a sample of the function being used in a worksheet, where you would replace the sample IF condition in brackets with your actual IF condition (and will likely need to use CTRL-SHIFT-ENTER rather than just ENTER):

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD="align: right"]1[/TD]
[TD]8:00breakfasttext1
8:45swimtext4[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
B1	=concat(IF(A1=1,{"8:00breakfasttext1";FALSE;FALSE;"8:45swimtext4"},0))

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Function concat(vArr As Variant) As String

concat = Replace(Join(Application.Transpose(vArr), Chr(10)), "False" & Chr(10), "")

End Function

I think your code would need to look more like this in order to handle the last cell being False...

Function Concat(vArr As Variant) As String
  Concat = Replace(vbLf & Join(Application.Transpose(vArr), vbLf), vbLf & "False", "")
  Do While Left(Concat, 1) = vbLf
    Concat = Mid(Concat, 2)
  Loop
End Function

Note that I use the built-in constant for Line Feeds (vbLf) rather than using Chr(10).

 

[Raan Ruuster]

Works perfectly.. Thank you all so much!

 

[circledchicken]

I think your code would need to look more like this in order to handle the last cell being False...

Function Concat(vArr As Variant) As String
  Concat = Replace(vbLf & Join(Application.Transpose(vArr), vbLf), vbLf & "False", "")
  Do While Left(Concat, 1) = vbLf
    Concat = Mid(Concat, 2)
  Loop
End Function

Note that I use the built-in constant for Line Feeds (vbLf) rather than using Chr(10).

Oops - thanks for correcting me.