UDF to concatenate column range conditional upon another column

[berlinhammer]

Hello All,

Having my first go at writing a UDF for VBA as I'm getting frustrated by the concatenate function.

I basically want to create my own function that will concatenate values in column B, if the value in column A is correct.

The code I have tried is such a mess I shan't post it, it very much does not work. I've been looking at this link but it is a bit beyond me...

http://www.cpearson.com/excel/returningarraysfromvba.aspx

With a very simple data set as an example, I want the following output in column C:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]pet
[/TD]
[TD]food
[/TD]
[TD]conc
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]chicken
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]fish
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]catnip
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]dog
[/TD]
[TD]bone
[/TD]
[TD]bone,chum
[/TD]
[/TR]
[TR]
[TD]dog
[/TD]
[TD]chum
[/TD]
[TD]bone,chum
[/TD]
[/TR]
</TBODY>[/TABLE]

I think I need to create a cell based function which takes a conditional range (col A) and concatenate range (col B) as inputs, stores this as a 5 by 3 array and then returns the concatenated output I wish (by referencing the stored array) based on what value is in column A. A "concatenate if", if you like. I'm new to a lot of the syntax though so if anyone has any suggestions I would be very grateful.

Many thanks in advance for any assistance

Jon

 

[Domski]

It's a bit basic but try this:

Public Function MyConcat(varCriteria, rngConcat As Range) As String

    Dim rngLoopRange As Range


    For Each rngLoopRange In rngConcat
        If rngLoopRange.Offset(0, -1) = varCriteria Then
            If MyConcat = "" Then
                MyConcat = rngLoopRange
            Else
                MyConcat = MyConcat & "," & rngLoopRange
            End If
        End If
    Next rngLoopRange


End Function

Used like this: =myconcat(A2,$B$2:$B$6)

Dom

 

[Rick Rothstein]

I think the UDF that I posted in this mini-blog article will allow you to do exactly what you want...

LookUp Value and Concatenate All Found Results

 

[berlinhammer]

Thanks a million gents, I look forward to trying out your code tomorrow. Will let you know how I get on

Jon

 

[berlinhammer]

Thanks chaps that works well! Had to tweak slightly for my purposes but all good in the end. Much appreciated.

Jon