Terribly slow UDF similar to CONCATENATE IF

[MAXARN]

Hey again,

I modified an UDF I found somewhere so it works for me, the problem is that it's so CPU intensive that just using it once makes excel work for 3-6 seconds when I edit any cell.

All it does is look at a column and copy text when a criteria is met on a different column and bunch the matches together. It goes like this:

Public Function ConcatIf(ConcatRange As Range, PID As Integer) As String
Dim rng As Range
For Each rng In ConcatRange
If rng.Offset(0, -11).Value = PID Then
ConcatIf = ConcatIf & rng.Value
End If
Next rng

End Function

Is this just the way it goes when working with strings or can something be done?

edit: I don't know that it matters, but only 9 rows contain values so far of the ~400 rows at the moment (but it's growing).

 

[MAXARN]

Ok, just noticed a significant difference if I limit the range instead of working on the whole column. Still, being that the range will grow to unknown length it might pose a problem later on.

 

[jasonb75]

Use a dynamic named range instead of a full column.

http://www.ozgrid.com/Excel/DynamicRanges.htm

In addition, I would suggest using a different UDF

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

With an array formula,

=Aconcat(IF(PID_Range=PID,ConcatRange,""))

Note that the formula must be array confirmed with Shift Ctrl Enter.

ConcatRange and PID should be the same ranges or values you would use with your version, PID_Range refers to the range 11 columns left of ConcatRange.

 

[MAXARN]

Use a dynamic named range instead of a full column.

http://www.ozgrid.com/Excel/DynamicRanges.htm

In addition, I would suggest using a different UDF

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

With an array formula,

=Aconcat(IF(PID_Range=PID,ConcatRange,""))

Note that the formula must be array confirmed with Shift Ctrl Enter.

ConcatRange and PID should be the same ranges or values you would use with your version, PID_Range refers to the range 11 columns left of ConcatRange.

Ill give that a go.

 

[jasonb75]

Something I didn't think to mention earlier,

The alternative UDF suggested isn't intended to be any faster than your own, but to eliminate possible errors in the results.

The dynamic named ranges should take care of your performance issues while allowing for additional rows of data.

 

[MAXARN]

I'm probably missing something (offset the defined name somehow?), all I'm getting so far is a long row of concatenated falsefalsefalse etc.

 

[jasonb75]

Did you remember the double quotes for the false argument?

=Aconcat(IF(PID_Range=PID,ConcatRange,""))

 

[MAXARN]

Did you remember the double quotes for the false argument?

=Aconcat(IF(PID_Range=PID,ConcatRange,""))

At that point no I didn't, and now It worked, but I now realize that the cell that is gathering this is also gathering strings from other places. I'll check if I can just make that a part of the array.

 

[MAXARN]

That worked as well, appreciate it.

Can the UDF be sped up by eliminating blank cell? If so, what would that code look like?

 

[jasonb75]

You would still have to test each cell to see if it was blank or not, which would take just as long.