TextJoin VBA for Excel 2013

[JCTalk]

Hi,

In Excel 2016 there is a nifty new formula called TextJoin which I have used to concatenate a bunch of cells together where an adjacent cell matches a unique criteria.

I'm trying to replicate this in Excel 2013 without success. I have found a VBA formula to use as an add in for Excel but it doesn't seem to work when you use an array range. It gives a #VALUE! error when ran with ctrl-shift-enter.

I'm not sure if I can post someone else's VBA from another site (but I will say that searching Google for "Lookup and return multiple values concatenated into one cell" will assist).

Is anyone able to help with a TextJoin VBA function that I can put in and use with Excel 2013 so I can use it with an array formula like this...

{=TEXTJOIN(", ",TRUE,IF(A2='Sheet 2'!$A:$A,'Sheet 2'!$C:$C,""))}

Many thanks for your help ninja's and all.

 

[JCTalk]

To confirm, the 2016 version of the formula works like this...

TEXTJOIN(delimiter, ignore_empty, text1, text2, ...., etc)

 

[p45cal]

See if you can't steal one of these:
http://www.contextures.com/rickrothsteinexcelvbatext.html
https://answers.microsoft.com/en-us...e-string/067af6cb-07a8-44dd-b21f-71abc1ee4358
https://sfdcexcelutilities.wordpress.com/2015/02/06/excel-concatenate-function-through-macro-udf/
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
http://www.ozgrid.com/forum/showthread.php?t=168394

 

[JCTalk]

See if you can't steal one of these:
http://www.contextures.com/rickrothsteinexcelvbatext.html
https://answers.microsoft.com/en-us...e-string/067af6cb-07a8-44dd-b21f-71abc1ee4358
https://sfdcexcelutilities.wordpress.com/2015/02/06/excel-concatenate-function-through-macro-udf/
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
http://www.ozgrid.com/forum/showthread.php?t=168394

Hi P45cal.

Thank you for for the links. All great options, but unfortunately I run into the same problem. They work fine when specifying a specifc range to concatenate, but when you try to use them with something like

IF(A2='Sheet 2'!$A:$A,'Sheet 2'!$C:$C,"")

it fails with #VALUE!.

They all seem to dislike a criteria being used. I have 2016 on my home computer, and when I use TextJoin with the above criteria it works fine. Takes a while to process but does work. Unfortunately I don't have access to 2016 during the day.

Many thanks

 

[p45cal]

They work fine when specifying a specifc range to concatenate, but when you try to use them with something like

IF(A2='Sheet 2'!$A:$A,'Sheet 2'!$C:$C,"")

it fails with #VALUE!.

They all seem to dislike a criteria being used.

Do me a favour would you?
Supply us with a file to play with, with some expected results, and especially with the testing you've done with the various udfs, so that we can try tweaking, and hopefully come to a good solution. It would save us a lot of work re-creating your testing as well as remove all doubt regarding the content type and help ensure you get a solution that works. (I only have Excel 2010 but I think you can supply an Excel 2016 file with TextJoin functions in, and when I open it it will show results of formulae, but it won't recalculate. Or you could also cut and paste/Values of cells with that function to another place too.)

Help us to help you. (While I can't guarantee that effort on your part will always be rewarded with effort on the part of responders, I can guarantee that little effort by people seeking solutions will always get little or no effort from responders.)

Given you do that, I promise I'll do what I can to help.