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]
Thanks!
Alex
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