Sort and split by 2 criterias

[Eddy S]

Hello All
Could someone direct me to the right direction
So I have some data, for example

Name

Time

Count

John	14:00	20
Matilda	00:20	11
Ben	01:00	30
Susan	00:45	10
Edd	02:00	14
Tom	12:00	40

I need to sort this in time order and then split this data into the multiple tables where in every table SUM is not more then 51 and data is not repeating, example below

Name	Time	Count
Matilda	00:20​	11​
Susan	00:45​	10​
Ben	01:00​	30​
	total	51​
Name	Time	Count
Edd	02:00​	14​
	total	14​
Name	Time	Count
Tom	12:00​	40​
	total	40​
Name	Time	Count
John	14:00​	20​
	total	20​

Also this is all done in the google sheets
Thanks in advance

 

[DanteAmor]

Try the next version.
Your data in columns A to C, results in columns E to G

Sub split_Data_2()
  Dim r As Range
  Dim i As Long, j As Long
  Dim nTotal As Double
 
  Application.ScreenUpdating = False
  Range("A:C").Copy Range("I1")
  Set r = Range("I1", Range("K" & Rows.Count).End(3))
  r.Sort Range("J1"), xlAscending, Header:=xlYes
 
  Range("E1:G1").Value = Range("A1:C1").Value
  j = 1
  For i = 2 To r.Rows.Count
    If nTotal + Range("K" & i).Value > 51 Then
      j = j + 1
      Range("F" & j).Resize(1, 2).Value = Array("total", nTotal)
      j = j + 2
      Range("E" & j).Resize(1, 3).Value = Range("A1:C1").Value
      nTotal = 0
    End If
    j = j + 1
    Range("E" & j).Resize(1, 3).Value = Range("I" & i).Resize(1, 3).Value
    nTotal = nTotal + Range("K" & i).Value
  Next
  Range("F" & j + 1).Resize(1, 2).Value = Array("total", nTotal)
  Range("I:K").ClearContents
  Application.ScreenUpdating = True
End Sub

 

[Eddy S]

Try the next version.
Your data in columns A to C, results in columns E to G

Sub split_Data_2()
  Dim r As Range
  Dim i As Long, j As Long
  Dim nTotal As Double
 
  Application.ScreenUpdating = False
  Range("A:C").Copy Range("I1")
  Set r = Range("I1", Range("K" & Rows.Count).End(3))
  r.Sort Range("J1"), xlAscending, Header:=xlYes
 
  Range("E1:G1").Value = Range("A1:C1").Value
  j = 1
  For i = 2 To r.Rows.Count
    If nTotal + Range("K" & i).Value > 51 Then
      j = j + 1
      Range("F" & j).Resize(1, 2).Value = Array("total", nTotal)
      j = j + 2
      Range("E" & j).Resize(1, 3).Value = Range("A1:C1").Value
      nTotal = 0
    End If
    j = j + 1
    Range("E" & j).Resize(1, 3).Value = Range("I" & i).Resize(1, 3).Value
    nTotal = nTotal + Range("K" & i).Value
  Next
  Range("F" & j + 1).Resize(1, 2).Value = Array("total", nTotal)
  Range("I:K").ClearContents
  Application.ScreenUpdating = True
End Sub

Hi
This worked well for MS Excel, thanks for that . But is there any option to do this in Google sheets?

 

[DanteAmor]

I have no way to test in google sheets...

 

[Eddy S]

does anyone know how make this work in the google sheets?

 

[Eddy S]

Is here any Goole sheet master

 

[Joe4]

Is here any Goole sheet master

There are some people here who have used Google Sheets, but whether there are any "Google Sheet Masters" here, I don't know.
Remember, this is actually an Excel forum, not a Google Sheets one!