How to condense several rows into fewer rows?

[Excel Fox]

Or a UDF to do the trick....

Function Condensor(rngSourceRange As Variant, lngFragments As Long, lngIndex As Long) As Variant

    Dim lng As Long
    Dim sngSegment As Single
    Dim varSourceRange As Variant
    Dim sngFraction As Single
    Dim sngDivisions As Long
    Dim varIntermediate As Variant
    Dim sngCounter As Single
    Dim lngCounter As Long
    
    On Error GoTo ErrH
    varSourceRange = Application.Transpose(rngSourceRange)
    lng = UBound(varSourceRange)
    sngSegment = lng / lngFragments
    If Int(sngSegment) = sngSegment Then
        For lng = 1 + (sngSegment * (lngIndex - 1)) To sngSegment * lngIndex
            Condensor = Condensor + varSourceRange(lng)
        Next lng
    Else
        sngFraction = Abs(Int(sngSegment) - sngSegment)
        sngDivisions = 1 / sngFraction
        ReDim varIntermediate(1 To lng * sngDivisions)
        For lng = 1 To lng * sngDivisions
            If sngCounter < sngDivisions Then
            sngCounter = sngCounter + 1
            varIntermediate(lng) = varSourceRange(lngCounter + 1) / sngDivisions
            Else
                lngCounter = lngCounter + 1
                sngCounter = 1
                varIntermediate(lng) = varSourceRange(lngCounter + 1) / sngDivisions
            End If
        Next lng
        For lng = 1 + ((sngSegment / Abs(Int(sngSegment) - sngSegment)) * (lngIndex - 1)) To Int((sngSegment / Abs(Int(sngSegment) - sngSegment)) * lngIndex)
            Condensor = Condensor + varIntermediate(lng)
        Next lng
    End If
    Exit Function
ErrH: Condensor = ""
    
End Function

Check attached workbook for reference