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

[ayazgreat]

Hi Krish.
I have posted subjected thread on below link and i request you please help me to resolve it

https://www.mrexcel.com/board/threads/copy-id-data-based-on-converting-date-series-into-start-date-and-end-date.1223851/


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[sandy666]

my three cents with Power Query


NumbersIDStartDateEndDate


1234522
03/01/2022
06/01/2022


2345622
03/01/2022
04/01/2022



// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"ID", type text}, {"Date", type date}}),
Group = Table.Group(Type, {"Numbers", "ID"}, {{"StartDate", each List.Min([Date]), type date}, {"EndDate", each List.Max([Date]), type date}})
in
Group

[ayazgreat]

my three cents with Power Query



Numbers
ID
StartDate
EndDate



12345

22

03/01/2022


06/01/2022




23456

22

03/01/2022


04/01/2022






// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"ID", type text}, {"Date", type date}}),
Group = Table.Group(Type, {"Numbers", "ID"}, {{"StartDate", each List.Min([Date]), type date}, {"EndDate", each List.Max([Date]), type date}})
in
Group

Thanks for your reply I just want to know that where should I copy these power query code to run further I have 5 lacs rows of data so how much does it take time to complete ?

[sandy666]

1. don't quote whole post, this is not necessary
2. update your profile about excel version
3. your source data should be an Excel Table
4. whole work took me approx. 10 seconds to take the result table (including drinking coffee);)
5. FYI: this is NOT vba

[sandy666]

.
.
here is a few examples of Power Query (from real posts) (https://excelfox.com/forum/forumdisplay.php/30-ETL-PQ-Tips-and-Tricks)

and here much more about Power Query : Learn Power Query (https://learn.microsoft.com/en-us/power-query/)

[ayazgreat]

I am facing an issue of incorrect result and furthermore if add following column in result sheet so how should power query be updated

[sandy666]

as you can see there is no date 02/04/2022 in the source data (copied from mrexcel)
attach proper example with detailed description what you want to achieve and we will see what will be

[ayazgreat]

My Actual required result in attached file in result sheet if anybody can solve it please through VBA code ?

[DocAElstein]

Hello
I have the same problem that everyone else is having with you.
You are not explaining clearly enough for anyone to understand what you want, and you keep changing your data and results.

So it is impossible for anyone to help you.



For example, in your latest file this is the data

Row\ColABCD
1NumbersIDDate

2123452201/03/2022

3123452201/03/2022

4123452201/03/2022

5123452201/03/2022

6123452201/05/2022

7123452201/05/2022

8123452201/06/2022

9123452201/06/2022

10123452204/02/2022

11123452204/02/2022

12123452204/02/2022

13123452204/03/2022

14123452204/03/2022

15123452204/04/2022

16234562201/03/2022

17234562201/03/2022

18234562201/03/2022

19234562201/04/2022

20234562201/04/2022

21
Worksheet: Data


, and this is your results.

Row\ColABCDEFG
1NumberIDStart DateEnd DateDaysWorking Days

2123452201/03/202204/04/20229264Incorrect

3234562201/03/202201/04/202221

4

5

6

7123452201/03/202201/06/202243Correct

8123452204/02/202204/04/202230
Worksheet: Result

It is impossible to understand why you have more than one result for 12345


Alan

[ayazgreat]

Alan,

Please find attached updated file for your question that why I have more than 1 result for 12345.
Actually if you see result sheet I am calculating total days and total working days between start date and end date and all result is based on each number and different dates of each number.

[DocAElstein]

Hello
We may have a few problems.

First Problem: Dates in Excel.
Dates in Excel are always a big problem, especially when sharing workbooks. Excel is very bad at determining the correct date from written and given data.
We must overcome this problem first, or else we will be wasting our time because Excel will randomly give the wrong dates to different people and so everything will get in a confusing mess: Everyone will always get incorrect results. ( I am probably the only person in the whole world that understands fully all the problems in Excel with dates. So you are very lucky that I am helping you ! )

In order to make any progress we must be sure that we see the same dates. Usually we will see different dates. Usually everyone will see different dates when looking at the same shared workbook. Excel is useless with dates. We must do extra work to make sure we see the same dates.

Please do all the following if you want me to help further:


Give me another workbook similar to your last, with both
Worksheet Data
, and
Worksheet Results
So it can be the last workbook you gave me. ( Use the workbook that I return to you, Book8_11.xls

but also extra things I need from you:

_1) in the Data worksheet I need to see 2 extra helper columns. ( These are just for me to help you, - later they can be deleted ).
_ 1a) In column H I want to see the same date that is in column C, but I want to see the month written in words. Similar to this: https://i.postimg.cc/KjgcyXH1/Month-in-Words.jpg 4788
You write this in please. Please use the English shortened words for month, ( Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec )
_ 1b) In the workbook that I am returning to you, Book8_11.xls , run the macro Sub GetFuckingValue2Dates()
( That macro should put some numbers in column G like this: https://i.postimg.cc/nzWV5V08/Dates-in-Value2.jpg 4789 )

_2) When you have finished _1), ( writing in some dates in words, and ruinning the macro), make me a screenshot to show exactly what you see in worksheet Data
_2a) Return me the workbook, Book8_11.xls
_2b) Give/ show me the screenshot.




So
_1) You have 3 things to do for me: write in manually in words some dates; run a macro; make a screenshot
_2) You have 2 things to give me when you are finished doing those things for me: a workbook; and a picture to show me what you see in worksheet Data

Make sure you give me the correct information – take your time – I am busy now for all today, and cannot help again until tomorrow


Alan









Book8_11.xls https://app.box.com/s/4a97tul85xacufys77jzbjs3i422eahy

[ayazgreat]

Please find attached required detail




https://www.youtube.com/@alanelston2330/featured

[DocAElstein]

Hello
Thanks for the file and screenshot.
( You seem to have changed the format of column C ? I am not sure why you did that? It can be a bit confusing and irritating to someone trying to help you when you change things for no apparent reason. Perhaps you did not understand fully what I was asking for. I did not ask you to change anything in column C. So I am a bit confused. )

So we have learnt nothing about the date issue, because you changed the format in column C.

Question on you
Why did you change the format in column C?

You are continuing to make it very difficult to help you.



But let us try to move on……

You have given these 3 results for the Number 12345

12345 22 3-Jan-22 4-Jan-22
12345 22 6-Jan-22 7-Jan-22
12345 22 2-Apr-22 4-Apr-22


Questions for you to answer please

_(i) Why is this not the correct result?
12345 22 3-Jan-22 4-Apr-22

_(ii) Why is this not the correct result?
12345 22 3-Jan-22 7-Jan-22
12345 22 2-Apr-22 4-Apr-22

_(iii) Why is this not the correct result?
12345 22 3-Jan-22 4-Jan-22
12345 22 6-Jan-22 4-Apr-22


Is the answer:- that there is a break in days / missing days
4-Jan-22 ……… 6-Jan-22
7-Jan-22 …………………………… 2-Apr-22

Is the criteria for different rows in the results 2 things:
_a) Different Number
and also
_b) For the same number, if there is a break in days - in other words, for a valid Start date and End date pair in the results, there must be consecutive days in the rows in column C Data to which those Start date and End date pair refer.


Alan

[ayazgreat]

Is the answer:- that there is a break in days / missing days
4-Jan-22 ……… 6-Jan-22
7-Jan-22 …………………………… 2-Apr-22

Is the criteria for different rows in the results 2 things:
_a) Different Number
and also
_b) For the same number, if there is a break in days - in other words, for a valid Start date and End date pair in the results, there must be consecutive days in the rows in column C Data to which those Start date and End date pair refer.



Yes you are right regarding to above all answers

[DocAElstein]

Hello
OK thanks for the extra info/ confirmation.
( I think the biggest problem is the communication between us. You maybe have difficulty with the English language. It must be very difficult for you as a Pakistani. I can speak none of your language. )

OK, let me try to move on, …..
I will assume because of your comments at mrexcel ( https://www.mrexcel.com/board/threads/copy-id-data-based-on-converting-date-series-into-start-date-and-end-date.1223851/page-2#post-5986415 ) that you want date format of mm/dd/yyyy
( I will assume you changed the date format in column C in Copy of Book8_11.xls because you did not understand fully what I was asking you to do, because of the communication problem between us ).
I will try to make the coding reduce the chances of problems in dates when sharing the workbook


Here is a first attempt. If you need more help from me you will need to wait a couple of days.

Data
Row\ColABC
1NumbersIDDate

2123452201/03/2022

3123452201/03/2022

4123452201/03/2022

5123452201/03/2022

6123452201/04/2022

7123452201/06/2022

8123452201/06/2022

9123452201/07/2022

10123452204/02/2022

11123452204/02/2022

12123452204/02/2022

13123452204/03/2022

14123452204/03/2022

15123452204/04/2022

16234562201/03/2022

17234562201/03/2022

18234562201/03/2022

19234562201/04/2022

20234562201/04/2022
Worksheet: Data

Results
Row\ColABCD
1NumberIDStart DateEnd Date

2123452201/03/202201/04/2022

3123452201/06/202201/07/2022

4123452204/02/202204/04/2022

5234562201/03/202201/04/2022
Worksheet: Required Result




Coding

Sub Attempt1() ' https://excelfox.com/forum/showthread.php/2836-Data-Copy?p=19596&viewfull=1#post19596 https://excelfox.com/forum/showthread.php/2836-Data-Copy/page2#post19599
' Worksheets data info
Dim wsDta As Worksheet: Set wsDta = ThisWorkbook.Worksheets.Item("Data")
Dim Lr1 As Long: Let Lr1 = wsDta.Range("C" & wsDta.Rows.Count & "").End(xlUp).Row
Let wsDta.Range("C2:C" & Lr1 & "").NumberFormat = "mm\/dd\/yyyy" ' "mm/dd/yyyy" https://www.mrexcel.com/board/threads/copy-id-data-based-on-converting-date-series-into-start-date-and-end-date.1223851/page-2#post-5986415 ' This may overcomne soime problems
Dim wsRes As Worksheet: Set wsRes = ThisWorkbook.Worksheets.Item("Required Result")

Dim arrIn() As Variant: Let arrIn() = wsDta.Range("A2:D" & Lr1 + 1 & "").Value2 ' Value2 is important in this case to get consistant date infomation ' +1 makes Do While easier to stop at the end

' First info for Number, ID, and Start Date
Dim Cnt As Long: Let Cnt = 1
Dim StrDt As Double: Let StrDt = arrIn(Cnt, 3)
Dim Nmbr As Long: Let Nmbr = arrIn(Cnt, 1)
Dim ID As Long: Let ID = arrIn(Cnt, 2)
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 StpDt As Double: Let StpDt = (arrIn(Cnt, 3)) ' we have left a single or multiple set of rows for the output, so we know the End Date
Dim strClp As String
Let strClp = strClp & Nmbr & vbTab & ID & vbTab & StrDt & vbTab & StpDt & 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): Let Nmbr = arrIn(Cnt, 1): Let ID = arrIn(Cnt, 2) ' Next info for Number, ID, and Start Date
Loop
' put full Results string of data into clipboard, and paste out
Dim objDataObject As Object: Set objDataObject = 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
objDataObject.SetText strClp
objDataObject.PutInClipboard
wsRes.Paste Destination:=wsRes.Range("A2")

' attempt to get correct output date format
Dim Lr2 As Long: Let Lr2 = wsRes.Range("A" & wsRes.Rows.Count & "").End(xlUp).Row
Let wsRes.Range("C2:D" & Lr2 & "").NumberFormat = "mm\/dd\/yyyy"
End Sub




Alan

[ayazgreat]

Alan, it is amazing, you really did a great job.
Thank you so much for working and time you spent for it I really appreciate this.
However these code are taking a long time when I run this macro on 5.5 lacs rows

[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.