Replacing MCONCAT with CONCAT_RANGE (UDF)

[carl]

Currently using this formula:

=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5:E1074;ROW(E5:E1074)-MIN(ROW(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.

 

[Bernie Deitrick]

Carl,

If you are going to use a UDF, you might as well write one that does exactly
what you want. So instead of cobbling together non-custom UDF's, post an
example of what you have and what result you want.

HTH,
Bernie
MS Excel MVP

 

[carl]

Thank You Bernie.

Can I send you a copy of my sheet with a description of what I am trying to
accomplish ?

 

[Bernie Deitrick]

Sure. Take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP

 

[JulieD]

Hi Bernie

would be interested in any feedback with regards to the CONCAT_RANGE
function that you might have.

 

[Bernie Deitrick]

Julie,

Will do, if I get the workbook from Carl.

HTH,
Bernie
MS Excel MVP

 

[carl]

hi bernie. i sent you the workbook. please note my global settings are ";"
versus ",".

 

[Bernie Deitrick]

Carl,

Below is a UDF that takes one range and three optional arguments: a
separator string, true/false for return only filtered, and true/false to
return only unique values.

It is used like this, in your workbook:

=ConcatUF(D51074,";",TRUE,TRUE)

It could be used like

=ConcatUF(D51074,,,TRUE)
=ConcatUF(D51074,";",TRUE)
=ConcatUF(D51074,";",,TRUE)
=ConcatUF(D51074,";")

HTH,
Bernie
MS Excel MVP


Function ConcatUF(rngInput As Range, _
Optional strSep As String, _
Optional boolFiltered As Boolean, _
Optional boolUnique As Boolean) As String
Dim myCell As Range

For Each myCell In rngInput
If (Not boolFiltered Or _
(boolFiltered And (Application.Subtotal(3, myCell) = 1))) And _
((Not boolUnique) Or _
(boolUnique And (InStr(1, ConcatUF, myCell.Value) = 0))) Then
If ConcatUF = "" Then
ConcatUF = myCell.Value
Else
ConcatUF = ConcatUF & strSep & myCell.Value
End If
End If
Next myCell

End Function

 

[Bernie Deitrick]

For consistency with the name, I should have defined the function with the
parameters ordered as

Function ConcatUF(rngInput As Range, _
Optional strSep As String, _
Optional boolUnique As Boolean, _
Optional boolFiltered As Boolean) As String

I had initially use the name ConcatFU, but then figured that wasn't nice....

Bernie