Obviously there are lots of code-based ways of doing this type of thing, but for quick one-offs I find formulas faster and easier where it is acceptable to filter the results and paste the results columns to a new sheet etc.
EE-28568870.xlsx
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
Title | # Comments | Views | Activity |
---|---|---|---|
SUMPRODUCT with criteriya | 5 | 25 | |
First Blank Cell in a range | 7 | 34 | |
Excel: Find text between 2nd hyphen and next space | 10 | 30 | |
remove lower case characters in excel formula | 12 | 28 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!