The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.
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.