Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Trim(Condition) Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function
Sub kTest()
Dim k, i As Long, kk(), n As Long
k = Range("a1:b" & Range("a" & Rows.Count).End(3).Row).Value2
ReDim kk(1 To UBound(k, 1), 1 To 2)
With CreateObject("scripting.dictionary")
.comparemode = 1
For i = 1 To UBound(k, 1)
If Len(k(i, 1)) Then
If Not .exists(k(i, 1)) Then
n = n + 1
kk(n, 1) = k(i, 1): kk(n, 2) = k(i, 2)
.Item(k(i, 1)) = n
Else
kk(.Item(k(i, 1)), 2) = kk(.Item(k(i, 1)), 2) & k(i, 2)
End If
End If
Next
End With
If n Then
Worksheets.Add
Range("a1").Resize(n, 2) = kk
End If
End Sub
=concatenateif($a2:$a50, b60, $b2:$b50, " ")
If CriteriaRange.Cells(i).Value = Condition Then
.Thank you for your help. The problem is I have 46,000 rows of this type of data. What do you suggest for a file that large?
Also some numbers have 5 rows others have 2 or 3. It varies for each column A value.
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.