simple but not so simple formula needed (or vba?)

[auzanoo]

dear experts,

i have to make this (sample) data from this:

to this:

so,
1. the date just has to be unique,
2. the start time is from the first column, and then the end time is from the last column (of the date).
3. the row A and B must be from the summary.

i just confused about how to do the formula (or maybe using VBA?). the actual data is so big.


thanks in advance!

 

[bebo021999]

Are you still using Ex2007? I intend to use modern formula AGGREGATE which is for Ex2010 or later.

 

[Peter_SSs]

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Give this a try. It assumes that the data is on "Sheet1". Edit the code if required, or advise if the data will always be on the active sheet when the code is run.
The code creates a new sheet with the results.

Sub Rearrange()
  Dim rA As Range
  
  Application.ScreenUpdating = False
  Sheets("Sheet1").Copy Before:=Sheets(1)
  With Sheets(1)
    .UsedRange.MergeCells = False
    For Each rA In .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
      With rA
        .Cells(1).Value = Left(.Cells(1).Value, 11) & Right(.Cells(.Count).Value, 8)
        .Cells(1, 2).Resize(, 2).Value = .Cells(.Count + 1, 2).Resize(, 2).Value
        .EntireRow.Offset(1).Delete
      End With
    Next rA
  End With
  Application.ScreenUpdating = True
End Sub

 

[auzanoo]

Are you still using Ex2007? I intend to use modern formula AGGREGATE which is for Ex2010 or later.

still ex2007

 

[auzanoo]

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Give this a try. It assumes that the data is on "Sheet1". Edit the code if required, or advise if the data will always be on the active sheet when the code is run.
The code creates a new sheet with the results.

Sub Rearrange()
  Dim rA As Range
 
  Application.ScreenUpdating = False
  Sheets("Sheet1").Copy Before:=Sheets(1)
  With Sheets(1)
    .UsedRange.MergeCells = False
    For Each rA In .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
      With rA
        .Cells(1).Value = Left(.Cells(1).Value, 11) & Right(.Cells(.Count).Value, 8)
        .Cells(1, 2).Resize(, 2).Value = .Cells(.Count + 1, 2).Resize(, 2).Value
        .EntireRow.Offset(1).Delete
      End With
    Next rA
  End With
  Application.ScreenUpdating = True
End Sub

thanks for the future suggestion. i'll do that.

thank you so much for the code, Mr. Peter. it perfectly works. much love!
(sorry for the late response)

 

[Peter_SSs]

thank you so much for the code, Mr. Peter. it perfectly works.

You're welcome. Thanks for the confirmation.