I need to push a multidimensional file (Schools and Programs offered) into a file that has one line per school and all the programs offered for it into one cell instead of multiple rows.
If the following code online and added it into a macro, saved the file as macro-enabled, and then compiled the code in the VB editor.
Function ConcatenateIf(CriteriaRange As Range, criteriarange2 As Range, _
Condition As Variant, condition2 As Variant, ConcatenateRange As Range, _
Optional Separator As String = "|") As Variant 'Update 20150414
Dim xResult As String
On Error Resume Next
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 And criteriarange2 = condition2 Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
According to the site I found this on, it worked for them but I get the #NAME error when I try it. THe error appears at the function call (i.e., "=ConcatenateIF" turns into "#Name" when I run evaluate).
Format of the function is =ConcatenateIf(RANGE1, RANGE2, CRIT1, CRIT2, CONCATRANGE, "|").