Thursday, June 5, 2014

ExcelFu - Concatenate string with condition

How can I achieve table #2 from table #1 in Excel?

TABLE1

=>
TABLE2 

Name
No.
No.
Name
A
1
1
A,D,K
B
2
2
B,G,H,I
C
3
3
C,E,F
D
1
E
3
F
3
G
2
H
2
I
2
K
1


There are 2 things I need to do to create table 2:

1. Filter TABLE2 by No. column
2. Concatenate those records which have the same No.

But, with the built-in functions set of Excel spreadsheet, I can only do 1 thing at a time. I meant I can only concatenate string without condition or only filter records without concatenate the results.

So, the only way is to create a User Define Function:

1. Press ALT+F11 in excel to open the Microsoft Visual Basic window.

2. Click Insert menu > Module and define the ConcatenateIf function:

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 = 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

> Save

3. Go back to the spreadsheet, use this formula to archive column Name of TABLE2

=concatenateIf($B$2:$B$12,D2,$A$2:$A$12,",")

with:

  • $B$2:$B$12 :  column No. of TABLE1
  • D2 : column No. of TABLE2
  • $A$2:$A$12: column Name of TABLE1



Reference: http://www.eileenslounge.com/viewtopic.php?f=27&t=12298