Combining rows if 2 values are met

Hi there, I'm trying to combine multiple rows of answers by the same subject based on an Subject number and a certain text. For example rows 2 through 4 should be combined into a single row as their subject number and text matches, this should result in Subject 123 with the text Hello this is a test, in 2010 with times picked A = 6, B = 2, C= 3 and D = 1 in this row.

I tried doing this by creating IF formulas, but I could not figure out how to have it automatically loop to keep comparing to the row below. I also tried to write a VBA code, which works for a single row and a single column, but I cannot figure out how to make it check multiple rows and loop automatically.

Any help is greatly appreciated!

Originally Posted by MatrixMan

Try this:

Private Sub CombineTasks()
'if year & task name are the same combine the rows; override year with
'any year found (ignore blanks)and sum the "times picked" values in cols D->G.
Dim celS As Range, celY As Range, celT As Range, celA As Range, celB As Range, celC As Range, celD As Range
Dim rngData As Range, celCheck As Range
Dim lngCurrentRow As Long

    'set the data range to search:
    Set celA = Cells(Rows.Count, 1).End(xlUp)
    Set celB = Cells(1, Columns.Count).End(xlToLeft)
    Set rngData = Range(Cells(2, 1), Cells(celA.Row, celB.Column))
    
    For Each celS In rngData.Columns(1).Cells
        If celS.Value <> Empty Then
            Set celY = celS.Offset(0, 1)    'year
            Set celT = celS.Offset(0, 2)    'task text
            Set celA = celS.Offset(0, 3)    'times picked A
            Set celB = celS.Offset(0, 4)    'times picked B
            Set celC = celS.Offset(0, 5)    'times picked C
            Set celD = celS.Offset(0, 6)    'times picked D
            'search for matching subject IDs in col A:
            lngCurrentRow = celS.Row
BeginSearch:
            Set celCheck = rngData.Columns(1).Find(what:=celS.Value, after:=celS, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False)
            If Not celCheck Is Nothing Then
                If celCheck.Row <> lngCurrentRow Then
                    'subject ID matches - check task text:
                    If celT.Value = celCheck.Offset(0, 2).Value Then
                        'task text matches - check the year is the same or blank:
                        If celY.Value = celCheck.Offset(0, 1).Value Or celCheck.Offset(0, 1).Value = Empty Then
                            'merge the times picked:
                            celA.Value = celA.Value + celCheck.Offset(0, 3).Value
                            celB.Value = celB.Value + celCheck.Offset(0, 4).Value
                            celC.Value = celC.Value + celCheck.Offset(0, 5).Value
                            celD.Value = celD.Value + celCheck.Offset(0, 6).Value
                            'overwrite the year if blank:
                            If celY.Value = Empty And celCheck.Offset(0, 1).Value <> Empty Then _
                                celY.Value = celCheck.Offset(0, 1).Value
                            'clear the row:
                            rngData.Rows(celCheck.Row - 1).ClearContents
                            'keep checking for other matches:
                            GoTo BeginSearch
                        End If
                    End If
                End If
            End If
        End If
    Next celS
    
    'resort the data range:
    With Sheet1.Sort.SortFields
        .Clear
        .Add2 Key:=rngData.Resize(rngData.Rows.Count - 1, 1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End With
    With Sheet1.Sort
        .SetRange rngData
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'purge memory:
    Set rngData = Nothing
    Set celS = Nothing: Set celY = Nothing: Set celT = Nothing
    Set celA = Nothing: Set celB = Nothing: Set celC = Nothing: Set celD = Nothing
    
End Sub

Thank you this seems to be working perfectly! In my actual database I use columns A-AB. After expanding from CelD onwards instead of using CelY for year and CelT for the text and CelS can I just use AC, AD, AE?