Function to summarize text in Vba Excel

Question

Now I'm using MS Excel 2019. I desire to make function to get text at Summary Steps column and Sumary Values column from Steps and Values Column It's described as .

I tried with this function. However, It doesn't work at all

Function Congdoan_Time(Congdoan As Range, Time As Range, gtri As Boolean) As String

Dim xValue, TimeValue As String
Dim xChar As String
Dim xOutValue, xTimeValue As String

xValue = Congdoan.Value
TimeValue = Time.Value
Dim arr, timearr As Variant
Dim text, texttime As String
Dim nextarr As Variant
arr = Split(xValue, ",")
timearr = Split(TimeValue, "-")
Dim i As Long
Dim vallue As Variant
vallue = timearr(0)
  For i = LBound(arr) To UBound(arr) - 1
        If arr(i) = arr(i + 1) And i < UBound(arr) - 1 Then
        vallue = Val(vallue) + Val(timearr(i + 1))
         End If
        If arr(i) = arr(i + 1) And i = UBound(arr) - 1 Then
         End If
         If arr(i) <> arr(i + 1) Then
         xOutValue = xOutValue & "," & arr(i)
         xTimeValue = xTimeValue & "-" & vallue
        vallue = Val(timearr(i + 1))
        End If
    Next i

If xOutValue = "" Then
xOutValue = Join(arr, ",")
xTimeValue = vallue
End If

text = Right(xOutValue, Len(xOutValue) - 1)
nextarr = Split(text, ",")
If arr(UBound(arr)) <> nextarr(UBound(nextarr)) Then
text = text & "," & arr(UBound(arr))
xTimeValue = xTimeValue & "-" & Val(vallue) + Val(timearr(UBound(arr)))

End If
If gtri = True Then
Congdoan_Time = text
Else
Congdoan_Time = xTimeValue
End If
End Function

Formula at Sumary Steps Column

at Sumary Values Column

Please help to make another funtion that's work for me Thank you

Answer 1

My two cents using a dictionary:

Function Summary(steps As String, vals As String, pick As Boolean) As String

Dim arr_steps As Variant, arr_vals As Variant
Dim new_steps() As Variant, new_vals() As Variant

arr_steps = Split(steps, ",")
arr_vals = Split(vals, "-")

ReDim new_steps(UBound(arr_steps))
ReDim new_vals(UBound(arr_steps))

For x = 0 To UBound(arr_steps)
    If x = 0 Then
        new_steps(x) = arr_steps(x)
        new_vals(x) = arr_vals(x)
    ElseIf arr_steps(x) = arr_steps(x - 1) Then
        new_vals(x) = CDbl(new_vals(x - 1)) + CDbl(arr_vals(x))
        new_vals(x - 1) = ""
    Else
        new_steps(x) = arr_steps(x)
        new_vals(x) = arr_vals(x)
    End If
Next

If pick Then
    Summary = Join(new_steps, ",")
Else
    Summary = Join(new_vals, "-")
End If

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(?:^-+|[-,]+([-,])|,+$)"
    Summary = .Replace(Summary, "$1")
End With

End Function

Formula in C1:

=Summary(A1,B1,1)

Formula in D1:

=Summary(A1,B1,0)

---

Note: My locale uses decimal-comma instead of point. It should work out fine if yours is using dots. I just had to change these in the input.