Question:
Does loading the Ranges into arrays and using:
For each cell in the myRange
speed up the calculation time? Is this better than using the
for x = 1 to myRange.Rows.Count
check_something = myRange.Cells(x, 1).Value
next x
style of coding
"JulieD" wrote:
another option:
---------------
Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit
As Variant, _
Optional DelimitWith As String) As String
'created by Gdall - with acknowledgement to Harold Staff
'ConcCheck - range to check for the criteria
'ConcRange - range to concatenation
'NOTE the above two ranges must be indentically sized.
'ConcCrit - the criteria
'DelimitWith - the delimination character(s)
Dim Cel As Range
Dim i As Long
Dim checkarray() As String
Dim rangearray() As String
i = Application.WorksheetFunction.CountA(ConcCheck)
j = Application.WorksheetFunction.CountA(ConcRange)
If i < j Then
Exit Function
End If
ReDim checkarray(i - 1)
ReDim rangearray(i - 1)
i = 0
For Each Cel In ConcCheck
checkarray(i) = Cel.Text
i = i + 1
Next
i = 0
For Each Cel In ConcRange
rangearray(i) = Cel.Text
i = i + 1
Next
For i = 0 To j - 1
If checkarray(i) = ConcCrit Then CONCAT_IF = _
CONCAT_IF & rangearray(i) & DelimitWith
Next
If CONCAT_IF < "" Then _
CONCAT_IF = Left$(CONCAT_IF, _
Len(CONCAT_IF) - Len(DelimitWith)) End Function
--------
Cheers
JulieD
"Simon Shaw" <simonATsimonstoolsDOTcom wrote in message
...
My solution:
Public Function ConcatenateIF(Lookup_Value_Range As Range, _
Match_Range As Range, _
Concatenate_Range As Range) As
String
Dim x As Long
Dim Lookup_Value As String
Dim Source_Cell As Range
Dim Lookup_Row_Count As Long
Set Source_Cell = Application.Caller
Lookup_Value = Lookup_Value_Range _
.Cells(Source_Cell.Row - Lookup_Value_Range.Row + 1, 1).Value
ConcatenateIF = ""
If Lookup_Value < 0 Then
Lookup_Row_Count = Match_Range.Rows.Count
For x = 1 To Lookup_Row_Count
If Lookup_Value = Match_Range.Cells(x, 1).Value _
And Concatenate_Range.Cells(x, 1).Value < 0 Then
If ConcatenateIF = "" Then
ConcatenateIF = Concatenate_Range.Cells(x, 1).Value
Else
ConcatenateIF = ConcatenateIF & Chr(10) &
Concatenate_Range.Cells(x, 1).Value
End If
End If
Next x
End If
End Function
"Simon Shaw" wrote:
Is there a function like SUMIF for text values that concatenates rather
than
sums?
I have a set of data as follows:
Truck No. Product Comments
----------- --------- ------------------
123 R-Gas Fully Reconciled
456 R-Gas Tank Dried
123 G-Gas Refilled
There are other columns of numbers. I then have a summary row for each
truck
number using sumif for the number columns, but I need to concatenate the
comments in the summary row for say truck 123 to be:
"R-Gas"&":"&" Fully Reconciled"&", "&Char(10)&"R-Gas"&":"&" Refilled"
with the results in one text wrapped cell displayed as follows:
R-Gas: Fully Reconciled
R-Gas: Refilled
Thanks
|