Concatenate If

[Alex0013]

Hello All,

I have a Custom UDF below, and I was wondering if someone would be able to help me edit it to essentially be a Concatenate "IF" formula. Basically to work the exact same way as a SUMIF, pass a range to concatenate, pass a range to test, pass the test value. So an example would be:

Formula in Result column would be something like: =ConcatenateIfRange(B:B,"AC",A:A,",") Where B:B is the test range, "AC" is the test, A:A is the concatenated Range, and "," is the delimiter. I'm guessing this would just require adding 2 prompts to my existing formula and another IF statement wrapped up in the current one, but I'm not sure where to put the If statement to work how I want it to.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PH[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD]Type-Result[/TD]
[TD]Range-Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]141[/TD]
[TD]VC[/TD]
[TD][/TD]
[TD]AC[/TD]
[TD]145[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]145[/TD]
[TD]AC[/TD]
[TD][/TD]
[TD]RQ[/TD]
[TD]155[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]160[/TD]
[TD]VC[/TD]
[TD][/TD]
[TD]VC[/TD]
[TD]141,160,121[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]121[/TD]
[TD]VC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]155[/TD]
[TD]RQ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Function ConcatenateRange(ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String


Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long


cellArray = cell_range.Value


For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next


If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If


ConcatenateRange = newString


End Function

Thanks!
Alex

 

[LloydFinancials]

You can try this out:

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
     If rng = pValue Then
          If Application.WorksheetFunction.CountIf(pWorkRng, rng) = 1 Then
               xResult = rng.Offset(0, pIndex)
          Else
               xResult = xResult & "," & rng.Offset(0, pIndex)
          End If
     End If
Next
MYVLOOKUP = WorksheetFunction.Substitute(xResult, ",", "", 1)
End Function

 

[Alex0013]

That didn't work. Not sure what that is supposed to do actually, doesn't evaluate for me at least.

 

[mikerickson]

The arguments of this ConcatIf mirror those of SUMIF, with the addition of (optional) Delimiter and NoDuplicates arguments.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

 

[Alex0013]

Nice Mike, thanks, that works great! I do like the NoDuplicates addition, I don't need it in this case, but might be useful in the future!

 

[LloydFinancials]

I thought I edited my post but it apparently did not take.

Here is my sheet. The -1 is the index.

Excel 2010
	A	B	C	D
1	PH	Type	Type-Result
2	141	VC	AC	145
3	145	AC	RQ	155
4	160	VC	VC	141,160,121
5	121	VC
6	155	RQ
Sheet1

Cell Formulas
Range	Formula
D2	=myvlookup(C2,$B$2:$B$6,-1)

Glad Mike's is working.