Copy to Summarise data based on 2 criteria: User ID and gaps in continuous day segments for same user

[DocAElstein]

Hi

… 5.5 lacs rows

…Lacs?? I do not recognise this word. Internet tells me that possibly lacs or lakh is Indian and/ or Pakistani for 100000? Is that correct?
So 5.5 lacs rows is like 550000 – a bit over half a million ?
It is interesting feedback for me to know that the macro works with such large row numbers. ( I have no practical experience with these things: Currently my Computer forum work is just a part time Hobby )

I was surprised that my code works with such high numbers of rows.

---

…these code are taking a long time

I cannot really make any useful comment on the speed performance. I don’t have enough practical experience using my coding with real large data.

Just for learning purposes and info:
The coding I did for you uses the Clipboard in a very unusual and unconventional way. I have been doing this for a few months. Maybe someone somewhere did it before, but so far I have never seen anyone else do anything like it. It is possibly it is new stuff that nobody ever did before. Or maybe not. I am not sure. But I can certainly say that it is not usual or typical to do coding like this.
Basically what the coding does in Sub Attempt1() is
the following: To explain / summarise Sub Attempt1()
(Unconventional use of the clipboard)
It basically puts all the data in a VBA array. It works on that data from the array within VBA. (This is typical practice and is generally regarded as more efficient that working with the data row by row, from the worksheet)
It loops once through the data. – That is all normal and conventional.

It builds up a single text string for the output, which is put in the clipboard and then pasted out. - This is the unusual stuff

---

Here is a couple of more codings that you might like to try

Sub Conventional()
This has one major difference to Sub Attempt1()
It does not build up a long string to put in the clipboard and paste out. It does not use the clipboard.
Instead it pastes out each result line as it goes along in the loop.
This coding is much more typical and usual.
It is what most people would do

Sub Conventional() '  https://excelfox.com/forum/showthread.php/2836-Data-Copy?p=19600#post19600
On Error GoTo Bed
 Let Application.ScreenUpdating = False
Dim arrIn() As Variant: Let arrIn() = Worksheets("Data").Range("A2:D" & Worksheets("Data").Range("C" & Rows.Count & "").End(xlUp).Row + 1 & "").Value2 '
Dim wsRes As Worksheet: Set wsRes = ThisWorkbook.Worksheets.Item("Required Result")
' First info for Start Date
Dim Cnt As Long: Let Cnt = 1
Dim StrDt As Double: Let StrDt = arrIn(Cnt, 3)
    Do While arrIn(Cnt, 1) <> "" ' This will stop us when we go above the data range
        Do While (Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) Or Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) + 1) And arrIn(Cnt, 1) = arrIn(Cnt + 1, 1) ' This effectively stops us when the criteria for the data in multiple rows belonging in a single row in  Results  no longer is met ------------------------
         Let Cnt = Cnt + 1
        Loop   '                                                                                --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Let wsRes.Range("A" & wsRes.Range("A" & Rows.Count & "").End(xlUp).Row + 1 & ":D" & wsRes.Range("A" & Rows.Count & "").End(xlUp).Row + 1 & "").Value = Array(arrIn(Cnt, 1), arrIn(Cnt, 2), StrDt, (arrIn(Cnt, 3)))
    ' Its time now to move on to the next data set required for  Results
     Let Cnt = Cnt + 1
     Let StrDt = arrIn(Cnt, 3) ' Next info for Start Date
    Loop
Bed:
 Let Application.ScreenUpdating = True
End Sub

---

Sub Attempt1_Simplified()
This is the same basic coding as Sub Attempt1()
It is just simplified slightly. ( I have also omitted the extra stuff to help always get correct date format )
This sort of simplification you could do, or may have already considered, yourself.
( Initially when I give coding help in forums, I attempt to help in learning and so have some extra bits to aid clarity and learning.

Sub Attempt1_Simplified() '  https://excelfox.com/forum/showthread.php/2836-Data-Copy?p=19600#post19600

Dim arrIn() As Variant: Let arrIn() = Worksheets("Data").Range("A2:D" & Worksheets("Data").Range("C" & Rows.Count & "").End(xlUp).Row + 1 & "").Value2 '

' First info for Start Date
Dim Cnt As Long: Let Cnt = 1
Dim StrDt As Double: Let StrDt = arrIn(Cnt, 3)
    Do While arrIn(Cnt, 1) <> "" ' This will stop us when we go above the data range
        Do While (Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) Or Int(arrIn(Cnt + 1, 3)) = Int(arrIn(Cnt, 3)) + 1) And arrIn(Cnt, 1) = arrIn(Cnt + 1, 1) ' This effectively stops us when the criteria for the data in multiple rows belonging in a single row in  Results  no longer is met ------------------------
         Let Cnt = Cnt + 1
        Loop   '                                                                                --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Dim strClp As String
     Let strClp = strClp & arrIn(Cnt, 1) & vbTab & arrIn(Cnt, 2) & vbTab & StrDt & vbTab & (arrIn(Cnt, 3)) & vbCr & vbLf ' A single line in results in a format that the clipboard holds for an Excel row
    ' Its time now to move on to the next data set required for  Results
     Let Cnt = Cnt + 1
     Let StrDt = arrIn(Cnt, 3) ' Next info for Start Date
    Loop

' put full  Results  string of data into clipboard, and paste out
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/                      get text data from the clipboard or from the interpretaion representation of it - the Office thing that might be called the Office clipboard                      http://www.eileenslounge.com/posting.php?mode=edit&f=27&p=301028
     .SetText strClp
     .PutInClipboard
    End With

 Worksheets("Required Result").Paste Destination:=Worksheets("Required Result").Range("A2")

End Sub

---

Alan

[ayazgreat]

Alan Thanks so much explaining and guiding me.

[DocAElstein]

It would have been interesting if you had any feedback to any comparison in the performance of the conventional coding and the clipboard way coding

---

Alan Thanks so much explaining and guiding me.

I appreciate you thanking me, but I expect I may be wasting my time as you understand close to no English.

This might explain why no one ever understands what you want: As you can’t explain it, you don’t bother to try….
Unless you improve all your explanations considerably, then I think you will continue to get no help.