Concatenate If

Alex0013

Board Regular
Joined
Jul 23, 2014
Messages
158
Office Version
  1. 365
Platform
  1. Windows
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]


Code:
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
 
You can try this out:

Code:
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
 
Upvote 0
That didn't work. Not sure what that is supposed to do actually, doesn't evaluate for me at least.
 
Upvote 0
The arguments of this ConcatIf mirror those of SUMIF, with the addition of (optional) Delimiter and NoDuplicates arguments.

Code:
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
 
Upvote 0
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!
 
Upvote 0
I thought I edited my post but it apparently did not take.

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


Excel 2010
ABCD
1PHTypeType-Result
2141VCAC145
3145ACRQ155
4160VCVC141,160,121
5121VC
6155RQ
Sheet1
Cell Formulas
RangeFormula
D2=myvlookup(C2,$B$2:$B$6,-1)


Glad Mike's is working.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top