[chathacc]

Hi,

I wanted to know if anyone had any suggestions on how to combine data from multiple cells into one cell.

We have students who complete Math lessons. Our system provides a long list of each lesson completed, but I have to provide a monthly narrative of the lessons completed by each student for the month.

So I need to combine the lessons completed for each student into on cell. The issue is that a student could have completed anywhere from 1 lesson to 30 lessons during that month so I am not sure how to make the formula smart enough to pull the information automatically for all students.

There are character limits so I only need to pull the first five lessons completed for each student.

I have attached an example. Sheet 1 represents the list of lessons completed. Each lesson completed is a row. Some students have multiple rows.

Sheet 2 is how I want the data to appear for my narrative. The student name in Column A with the first five lessons a student has completed in Column B. It does not have to have the ", and" in between the last two lessons, but it would be a nice touch.

[JBeaucaire]

String Concatenation is not a native function to Excel, so it has to be added. I've installed a new function into this worksheet called CONCATIF() and it works like a SUMIF() formula with two additional parameters.

=ConCatIf(Sheet1!$A$2:$A$55,A2,Sheet1!$B$2:$B$55," , ",TRUE)

First parameter: Range to evaluate
Second param: comparison string. (I am assuming you will simply list the students in column A)
Third param: Range to return values from (These are the lessons)
Fourth param: The delimiting string for the concatenation, I'm using a comma in this example, but you could use anything.
Fifth param: (optional) TRUE means eliminate duplicated values, FALSE (or omitted) means string all values, even duplicates.

This should give you enough info to apply this UDF to your sheet and use it place of that formula of your own... your sample formula of:

============

So in column B you now see ALL the lessons each student completed using this CONCATIF() function matching the names.

Now, in column C I added a formula to display only the first 5 lessons listed in column B.

=============
Here's the UDF code I installed in the worksheet:

Option Explicit

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Erickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10, C1, $B$1:$B$10, ",", True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Replace(ConcatIf, Delimiter & Delimiter, Delimiter)    'eliminates blank values
    If Left(ConcatIf, Len(Delimiter)) = Delimiter Then ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function