Data mining from 2 sheets into one sheet in chronological order.

[MrDB4Excel]

I need to indicate here that even though I have Excel 2013 I do have the xlookup Addin that does work in Excel 2013.
Are there one or more formulas that will pull data from 4 columns each from two sheets and provide that data in chronologically numbered order in a third sheet? The attached XL2BB Mini Sheets all contain bogus data but relevant to the intended outcome.
I will provide three XL2BB Mini Sheets as follows:
First Mini Sheet contains 11 rows of data plus the header row from the 24FdJan sheet.
Second Mini Sheet contains 11 rows of data plus the header row from the 24TdJan sheet.
Third Mini Sheet contains 22 rows of data plus the header row from the JanWordDoc sheet.
You can see that I simply copied the text from 24FdJan & 24TdJan and placed this text as found in these 2 sheets but obviously not in chronological order.
What I need is for the JanWordDoc sheet to contain lookup formulas that would insert in chronological order therein from the data that is mined from sheets 24FdJan & 24TdJan.
I would prefer to not use VBA.
If necessary, possibly sheets 24FdJan & 24TdJan could have helper columns that are reading the data from the relevant columns, which are Doc #, Link Code Plus Received Meta Data, Link Code Plus Sent Meta Data, and Subject.
If anyone can help this would be much appreciated.

Email Conversations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Doc #	Link Code	Sender	Subject	Sent Date	@	Rec'd Time	Month	Day			Doc #	Link Code Plus Received Meta Data
4	1 >	24Fd01-1	dodge	Subject, from email message	Mon 01/Jan/2024	@	10:38	Jan	01	10	38	1 >	24Fd01-1--Message received from dodge Mon 01/Jan/2024--@--10:38	1
5	5 >	24Fd01-2	dodge	Subject, from email message	Tue 02/Jan/2024	@	00:41	Jan	02	00	41	5 >	24Fd01-2--Message received from dodge Tue 02/Jan/2024--@--00:41	2
6	7 >	24Fd01-3	dodge	Subject, from email message	Tue 02/Jan/2024	@	07:38	Jan	02	07	38	7 >	24Fd01-3--Message received from dodge Tue 02/Jan/2024--@--07:38	3
7	12 >	24Fd01-4	dodge	Subject, from email message	Thu 04/Jan/2024	@	10:58	Jan	04	10	58	12 >	24Fd01-4--Message received from dodge Thu 04/Jan/2024--@--10:58	4
8	18 >	24Fd01-5	dodge	Subject, from email message	Fri 05/Jan/2024	@	22:03	Jan	05	22	03	18 >	24Fd01-5--Message received from dodge Fri 05/Jan/2024--@--22:03	5
9	19 >	24Fd01-6	dodge	Subject, from email message	Sat 06/Jan/2024	@	11:20	Jan	06	11	20	19 >	24Fd01-6--Message received from dodge Sat 06/Jan/2024--@--11:20	6
10	20 >	24Fd01-7	dodge	Subject, from email message	Sat 06/Jan/2024	@	11:23	Jan	06	11	23	20 >	24Fd01-7--Message received from dodge Sat 06/Jan/2024--@--11:23	7
11	23 >	24Fd01-8	dodge	Subject, from email message	Sun 07/Jan/2024	@	05:19	Jan	07	05	19	23 >	24Fd01-8--Message received from dodge Sun 07/Jan/2024--@--05:19	8
12	24 >	24Fd01-9	dodge	Subject, from email message	Sun 07/Jan/2024	@	11:46	Jan	07	11	46	24 >	24Fd01-9--Message received from dodge Sun 07/Jan/2024--@--11:46	9
13	26 >	24Fd01-10	dodge	Subject, from email message	Mon 08/Jan/2024	@	07:05	Jan	08	07	05	26 >	24Fd01-10--Message received from dodge Mon 08/Jan/2024--@--07:05	10
14	28 >	24Fd01-11	dodge	Subject, from email message	Mon 08/Jan/2024	@	10:29	Jan	08	10	29	28 >	24Fd01-11--Message received from dodge Mon 08/Jan/2024--@--10:29	11
24FdJan

Cell Formulas
Range	Formula
G4:G14	G4	=IF(OR(ISBLANK(J4),ISBLANK(K4)),"",VALUE(LEFT(J4,2)&":"&RIGHT(K4,2)&":00"))
M4:M14	M4	=IF(OR(ISBLANK(E4),ISBLANK(G4)),"",B4&"--Message received from dodge "&TEXT(E4+G4,"ddd dd/mmm/yyyy--\@--hh:mm"))
N4:N12	N4	=RIGHT(B4,1)
H5:H14	H5	=TEXT(E5,"MMM")
I5:I14	I5	=TEXT(E5,"DD")
L5:L14	L5	=IF(ISBLANK(A5),"",(A5)*1)
N13:N14	N13	=RIGHT(B13,2)
B4:B14	B4	=TabLegend!$I$1&"Fd"&TEXT(E4,"MM-")&SUM(N3+1)

Email Conversations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Doc #	Link Code	Sender	Subject	Sent Date	@	Rec'd Time	Month	Day			Doc #	Link Code Plus Sent Meta Data
4	2 >	24Td01-1	Codger	Subject, from email message	Mon 01/Jan/2024	@	13:53	Jan	01	13	53	1 >	24Td01-1--Message sent to dodge Mon 01/Jan/2024--@--13:53	1
5	3 >	24Td01-2	Codger	Subject, from email message	Mon 01/Jan/2024	@	15:02	Jan	01	15	02	3 >	24Td01-2--Message sent to dodge Mon 01/Jan/2024--@--15:02	2
6	4 >	24Td01-3	Codger	Subject, from email message	Mon 01/Jan/2024	@	15:03	Jan	01	15	03	4 >	24Td01-3--Message sent to dodge Mon 01/Jan/2024--@--15:03	3
7	6 >	24Td01-4	Codger	Subject, from email message	Tue 02/Jan/2024	@	02:11	Jan	02	02	11	6 >	24Td01-4--Message sent to dodge Tue 02/Jan/2024--@--02:11	4
8	8 >	24Td01-5	Codger	Subject, from email message	Tue 02/Jan/2024	@	14:15	Jan	02	14	15	8 >	24Td01-5--Message sent to dodge Tue 02/Jan/2024--@--14:15	5
9	9 >	24Td01-6	Codger	Subject, from email message	Wed 03/Jan/2024	@	12:39	Jan	03	12	39	9 >	24Td01-6--Message sent to dodge Wed 03/Jan/2024--@--12:39	6
10	10 >	24Td01-7	Codger	Subject, from email message	Wed 03/Jan/2024	@	12:53	Jan	03	12	53	10 >	24Td01-7--Message sent to dodge Wed 03/Jan/2024--@--12:53	7
11	11 >	24Td01-8	Codger	Subject, from email message	Wed 03/Jan/2024	@	22:22	Jan	03	22	22	11 >	24Td01-8--Message sent to dodge Wed 03/Jan/2024--@--22:22	8
12	13 >	24Td01-9	Codger	Subject, from email message	Thu 04/Jan/2024	@	12:05	Jan	04	12	05	13 >	24Td01-9--Message sent to dodge Thu 04/Jan/2024--@--12:05	9
13	14 >	24Td01-10	Codger	Subject, from email message	Thu 04/Jan/2024	@	12:31	Jan	04	12	31	14 >	24Td01-10--Message sent to dodge Thu 04/Jan/2024--@--12:31	10
14	15 >	24Td01-11	Codger	Subject, from email message	Thu 04/Jan/2024	@	23:47	Jan	04	23	47	15 >	24Td01-11--Message sent to dodge Thu 04/Jan/2024--@--23:47	11
24TdJan

Cell Formulas
Range	Formula
G4:G14	G4	=IF(OR(ISBLANK(J4),ISBLANK(K4)),"",VALUE(LEFT(J4,2)&":"&RIGHT(K4,2)&":00"))
M4:M14	M4	=IF(OR(ISBLANK(E4),ISBLANK(G4)),"",B4&"--Message sent to dodge "&TEXT(E4+G4,"ddd dd/mmm/yyyy--\@--hh:mm"))
N4:N12	N4	=RIGHT(B4,1)
H5:H14	H5	=TEXT(E5,"MMM")
I5:I14	I5	=TEXT(E5,"DD")
L5:L14	L5	=IF(ISBLANK(A5),"",(A5)*1)
N13:N14	N13	=RIGHT(B13,2)
B4:B14	B4	=TabLegend!$I$1&"Td"&TEXT(E4,"MM-")&SUM(N3+1)

Email Conversations.xlsx
	A	B	C
1	Doc #	Link Code Plus Received and Sent Meta Data	Subject
2	1 >	24Fd01-1--Message received from dodge Mon 01/Jan/2024--@--10:38	Subject, from email message
3	5 >	24Fd01-2--Message received from dodge Tue 02/Jan/2024--@--00:41	Subject, from email message
4	7 >	24Fd01-3--Message received from dodge Tue 02/Jan/2024--@--07:38	Subject, from email message
5	12 >	24Fd01-4--Message received from dodge Thu 04/Jan/2024--@--10:58	Subject, from email message
6	18 >	24Fd01-5--Message received from dodge Fri 05/Jan/2024--@--22:03	Subject, from email message
7	19 >	24Fd01-6--Message received from dodge Sat 06/Jan/2024--@--11:20	Subject, from email message
8	20 >	24Fd01-7--Message received from dodge Sat 06/Jan/2024--@--11:23	Subject, from email message
9	23 >	24Fd01-8--Message received from dodge Sun 07/Jan/2024--@--05:19	Subject, from email message
10	24 >	24Fd01-9--Message received from dodge Sun 07/Jan/2024--@--11:46	Subject, from email message
11	26 >	24Fd01-10--Message received from dodge Mon 08/Jan/2024--@--07:05	Subject, from email message
12	28 >	24Fd01-11--Message received from dodge Mon 08/Jan/2024--@--10:29	Subject, from email message
13	2 >	24Td01-1--Message sent to dodge Mon 01/Jan/2024--@--13:53	Subject, from email message
14	3 >	24Td01-2--Message sent to dodge Mon 01/Jan/2024--@--15:02	Subject, from email message
15	4 >	24Td01-3--Message sent to dodge Mon 01/Jan/2024--@--15:03	Subject, from email message
16	6 >	24Td01-4--Message sent to dodge Tue 02/Jan/2024--@--02:11	Subject, from email message
17	8 >	24Td01-5--Message sent to dodge Tue 02/Jan/2024--@--14:15	Subject, from email message
18	9 >	24Td01-6--Message sent to dodge Wed 03/Jan/2024--@--12:39	Subject, from email message
19	10 >	24Td01-7--Message sent to dodge Wed 03/Jan/2024--@--12:53	Subject, from email message
20	11 >	24Td01-8--Message sent to dodge Wed 03/Jan/2024--@--22:22	Subject, from email message
21	13 >	24Td01-9--Message sent to dodge Thu 04/Jan/2024--@--12:05	Subject, from email message
22	14 >	24Td01-10--Message sent to dodge Thu 04/Jan/2024--@--12:31	Subject, from email message
23	15 >	24Td01-11--Message sent to dodge Thu 04/Jan/2024--@--23:47	Subject, from email message
JanWordDoc

 

[RasGhul]

Hi MrDB4,

I did this in Power Query, How to Install Power Query for 2013

I would transform 24TdJan 1st, as the M code for 24FdJan Appends the 2 tables. Note that I changed Column M header to "Link Code 2"

When you add more data to either Tab or Table and Refresh, JanWordDoc with re-append and sort in time order.

Transform 24TDJan 1st, and Load to "Create Connection Only"

Data mining from 2 sheets_MrDB4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Doc #	Link Code	Sender	Subject	Sent Date	@	Rec'd Time	Month	Day	Column1	Column2	Doc #3	Link Code2
4	2		Codger	Subject, from email message	1/01/2024	@	1:53:00 PM	Jan	1	13	53	1	--Message sent to dodge Mon 01/Jan/2024--@--13:53
5	3		Codger	Subject, from email message	1/01/2024	@	3:02:00 PM	Jan	01	15	2	3	--Message sent to dodge Mon 01/Jan/2024--@--15:02
6	4		Codger	Subject, from email message	1/01/2024	@	3:03:00 PM	Jan	01	15	3	4	--Message sent to dodge Mon 01/Jan/2024--@--15:03
7	6		Codger	Subject, from email message	2/01/2024	@	2:11:00 AM	Jan	02	2	11	6	--Message sent to dodge Tue 02/Jan/2024--@--02:11
8	8		Codger	Subject, from email message	2/01/2024	@	2:15:00 PM	Jan	02	14	15	8	--Message sent to dodge Tue 02/Jan/2024--@--14:15
9	9		Codger	Subject, from email message	3/01/2024	@	12:39:00 PM	Jan	03	12	39	9	--Message sent to dodge Wed 03/Jan/2024--@--12:39
10	10		Codger	Subject, from email message	3/01/2024	@	12:53:00 PM	Jan	03	12	53	10	--Message sent to dodge Wed 03/Jan/2024--@--12:53
11	11		Codger	Subject, from email message	3/01/2024	@	10:22:00 PM	Jan	03	22	22	11	--Message sent to dodge Wed 03/Jan/2024--@--22:22
12	13		Codger	Subject, from email message	4/01/2024	@	12:05:00 PM	Jan	04	12	5	13	--Message sent to dodge Thu 04/Jan/2024--@--12:05
13	14		Codger	Subject, from email message	4/01/2024	@	12:31:00 PM	Jan	04	12	31	14	--Message sent to dodge Thu 04/Jan/2024--@--12:31
14	15		Codger	Subject, from email message	4/01/2024	@	11:47:00 PM	Jan	04	23	47	15	--Message sent to dodge Thu 04/Jan/2024--@--23:47
24TdJan

Cell Formulas
Range	Formula
G4:G14	G4	=IF(OR(ISBLANK(J4),ISBLANK(K4)),"",VALUE(LEFT(J4,2)&":"&RIGHT(K4,2)&":00"))
M4:M14	M4	=IF(OR(ISBLANK(E4),ISBLANK(G4)),"",B4&"--Message sent to dodge "&TEXT(E4+G4,"ddd dd/mmm/yyyy--\@--hh:mm"))
H5:H14	H5	=TEXT(E5,"MMM")
I5:I14	I5	=TEXT(E5,"DD")
L5:L14	L5	=IF(ISBLANK(A5),"",(A5)*1)

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rec'd Time", type time}, {"Column2", Int64.Type}, {"Day", Int64.Type}, {"Column1", Int64.Type}})
in
    #"Changed Type"

Then Transform 24FdJan, for Load to select Add to New Worksheet and name JanWordDoc

Data mining from 2 sheets_MrDB4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Doc #	Link Code	Sender	Subject	Sent Date	@	Rec'd Time	Month	Day	Column1	Column2	Doc #3	Link Code2
4	1		dodge	Subject, from email message	1/01/2024	@	10:38:00 AM	Jan	1	10	38	1	--Message received from dodge Mon 01/Jan/2024--@--10:38
5	5		dodge	Subject, from email message	2/01/2024	@	12:41:00 AM	Jan	02	0	41	5	--Message received from dodge Tue 02/Jan/2024--@--00:41
6	7		dodge	Subject, from email message	2/01/2024	@	7:38:00 AM	Jan	02	7	38	7	--Message received from dodge Tue 02/Jan/2024--@--07:38
7	12		dodge	Subject, from email message	4/01/2024	@	10:58:00 AM	Jan	04	10	58	12	--Message received from dodge Thu 04/Jan/2024--@--10:58
8	18		dodge	Subject, from email message	5/01/2024	@	10:03:00 PM	Jan	05	22	3	18	--Message received from dodge Fri 05/Jan/2024--@--22:03
9	19		dodge	Subject, from email message	6/01/2024	@	11:20:00 AM	Jan	06	11	20	19	--Message received from dodge Sat 06/Jan/2024--@--11:20
10	20		dodge	Subject, from email message	6/01/2024	@	11:23:00 AM	Jan	06	11	23	20	--Message received from dodge Sat 06/Jan/2024--@--11:23
11	23		dodge	Subject, from email message	7/01/2024	@	5:19:00 AM	Jan	07	5	19	23	--Message received from dodge Sun 07/Jan/2024--@--05:19
12	24		dodge	Subject, from email message	7/01/2024	@	11:46:00 AM	Jan	07	11	46	24	--Message received from dodge Sun 07/Jan/2024--@--11:46
13	26		dodge	Subject, from email message	8/01/2024	@	7:05:00 AM	Jan	08	7	5	26	--Message received from dodge Mon 08/Jan/2024--@--07:05
14	28		dodge	Subject, from email message	8/01/2024	@	10:29:00 AM	Jan	08	10	29	28	--Message received from dodge Mon 08/Jan/2024--@--10:29
24FdJan

Cell Formulas
Range	Formula
G4:G14	G4	=IF(OR(ISBLANK(J4),ISBLANK(K4)),"",VALUE(LEFT(J4,2)&":"&RIGHT(K4,2)&":00"))
M4:M14	M4	=IF(OR(ISBLANK(E4),ISBLANK(G4)),"",B4&"--Message received from dodge "&TEXT(E4+G4,"ddd dd/mmm/yyyy--\@--hh:mm"))
H5:H14	H5	=TEXT(E5,"MMM")
I5:I14	I5	=TEXT(E5,"DD")
L5:L14	L5	=IF(ISBLANK(A5),"",(A5)*1)

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Rec'd Time", type time}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"24TdJanTBL"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Appended Query", "Sent Date", "Sent Date - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Sent Date - Copy", type number}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "Rec'd Time", "Rec'd Time - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Rec'd Time - Copy", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Date Time", each [#"Sent Date - Copy"]+[#"Rec'd Time - Copy"]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Date Time", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Sent Date - Copy", "Rec'd Time - Copy"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date Time", Order.Ascending}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Sorted Rows",{{"Sent Date", type date}})
in
    #"Changed Type4"

Data mining from 2 sheets_MrDB4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Doc #	Link Code	Sender	Subject	Sent Date	@	Rec'd Time	Month	Day	Column1	Column2	Doc #3	Link Code2	Date Time
2	1		dodge	Subject, from email message	1/01/2024	@	10:38:00 AM	Jan	1	10	38	1	--Message received from dodge Mon 01/Jan/2024--@--10:38	1/01/2024 10:38
3	2		Codger	Subject, from email message	1/01/2024	@	1:53:00 PM	Jan	1	13	53	1	--Message sent to dodge Mon 01/Jan/2024--@--13:53	1/01/2024 13:53
4	3		Codger	Subject, from email message	1/01/2024	@	3:02:00 PM	Jan	1	15	2	3	--Message sent to dodge Mon 01/Jan/2024--@--15:02	1/01/2024 15:02
5	4		Codger	Subject, from email message	1/01/2024	@	3:03:00 PM	Jan	1	15	3	4	--Message sent to dodge Mon 01/Jan/2024--@--15:03	1/01/2024 15:03
6	5		dodge	Subject, from email message	2/01/2024	@	12:41:00 AM	Jan	2	0	41	5	--Message received from dodge Tue 02/Jan/2024--@--00:41	2/01/2024 0:41
7	6		Codger	Subject, from email message	2/01/2024	@	2:11:00 AM	Jan	2	2	11	6	--Message sent to dodge Tue 02/Jan/2024--@--02:11	2/01/2024 2:11
8	7		dodge	Subject, from email message	2/01/2024	@	7:38:00 AM	Jan	2	7	38	7	--Message received from dodge Tue 02/Jan/2024--@--07:38	2/01/2024 7:38
9	8		Codger	Subject, from email message	2/01/2024	@	2:15:00 PM	Jan	2	14	15	8	--Message sent to dodge Tue 02/Jan/2024--@--14:15	2/01/2024 14:15
10	9		Codger	Subject, from email message	3/01/2024	@	12:39:00 PM	Jan	3	12	39	9	--Message sent to dodge Wed 03/Jan/2024--@--12:39	3/01/2024 12:39
11	10		Codger	Subject, from email message	3/01/2024	@	12:53:00 PM	Jan	3	12	53	10	--Message sent to dodge Wed 03/Jan/2024--@--12:53	3/01/2024 12:53
12	11		Codger	Subject, from email message	3/01/2024	@	10:22:00 PM	Jan	3	22	22	11	--Message sent to dodge Wed 03/Jan/2024--@--22:22	3/01/2024 22:22
13	12		dodge	Subject, from email message	4/01/2024	@	10:58:00 AM	Jan	4	10	58	12	--Message received from dodge Thu 04/Jan/2024--@--10:58	4/01/2024 10:58
14	13		Codger	Subject, from email message	4/01/2024	@	12:05:00 PM	Jan	4	12	5	13	--Message sent to dodge Thu 04/Jan/2024--@--12:05	4/01/2024 12:05
15	14		Codger	Subject, from email message	4/01/2024	@	12:31:00 PM	Jan	4	12	31	14	--Message sent to dodge Thu 04/Jan/2024--@--12:31	4/01/2024 12:31
16	15		Codger	Subject, from email message	4/01/2024	@	11:47:00 PM	Jan	4	23	47	15	--Message sent to dodge Thu 04/Jan/2024--@--23:47	4/01/2024 23:47
17	18		dodge	Subject, from email message	5/01/2024	@	10:03:00 PM	Jan	5	22	3	18	--Message received from dodge Fri 05/Jan/2024--@--22:03	5/01/2024 22:03
18	19		dodge	Subject, from email message	6/01/2024	@	11:20:00 AM	Jan	6	11	20	19	--Message received from dodge Sat 06/Jan/2024--@--11:20	6/01/2024 11:20
19	20		dodge	Subject, from email message	6/01/2024	@	11:23:00 AM	Jan	6	11	23	20	--Message received from dodge Sat 06/Jan/2024--@--11:23	6/01/2024 11:23
20	23		dodge	Subject, from email message	7/01/2024	@	5:19:00 AM	Jan	7	5	19	23	--Message received from dodge Sun 07/Jan/2024--@--05:19	7/01/2024 5:19
21	24		dodge	Subject, from email message	7/01/2024	@	11:46:00 AM	Jan	7	11	46	24	--Message received from dodge Sun 07/Jan/2024--@--11:46	7/01/2024 11:46
22	26		dodge	Subject, from email message	8/01/2024	@	7:05:00 AM	Jan	8	7	5	26	--Message received from dodge Mon 08/Jan/2024--@--07:05	8/01/2024 7:05
23	28		dodge	Subject, from email message	8/01/2024	@	10:29:00 AM	Jan	8	10	29	28	--Message received from dodge Mon 08/Jan/2024--@--10:29	8/01/2024 10:29
JanWordDoc

 

[MrDB4Excel]

Many thanks, RasGhul. I almost forgot about this, thinking it would be near impossible. I will look at this soon. I have a 2-day trip to Prague, CZ beginning Dec 1 so won't have time until after Dec 2 to work on this. I already have Power Query for 2013 installed and have used it quite a lot over the last couple years but what you are showing here is something I haven't tried before.

 

[MrDB4Excel]

Hi RasGuhl,
I have a little time today before my trip and have tried dealing with this and consequently got lost.
If you have time can you guide me thru the steps you used to get your results?
It would be so appreciated if you can help.

 

[RasGhul]

Ok

Note that when you Get Data into Power Query it converts your data into tables

• Change Column M Header name to Link Code 2 in both 24T & 24F
• Select D4 from 24FdJan / Data / From Table - Range / Tick my table has Headers and Load into PQuery
• In PQ Change yr Table name to 24FdJanTBL
• Change type Sent Date to Date, Rec'd Time to Time, from Day column to Doc #3 to Whole numbers
• Close & Load to / Table / New Worksheet / Tick Add this to Data Model / Rename this new sheet "JanWordDoc"
• Under Query's & Connections you should have the 24FdJanTBL query (11 rows loaded)

Repeat the above and create the 24TdJanTBL

• With this table Close & Load to / Only Create Connection / Tick Add this to Data Model

You should see both of your loaded queries 24F & 24T in Queries & Connections, now its time to Append & Transform.

• Select JanWordDoc tab, Double click 24dJanTBL to re-enter PQuery
• from the PQ Ribbon select Append Queries / Two Tables / Table to Append / 24TdJanTBL
• (both queries should now be Appended)
• Select Sent Date Column / Add Column / Add Duplicate Column
• Select Rec' Time Column / Add Column / Add Duplicate Column
• Change Type for Sent Date copy & Recd Time Copy to Decimal Number
• Add Column / Custom Column / New Column Name "Date & Time" / Custom Formula =[#"Sent Date - Copy"]+[#"Rec'd Time - Copy"]
• Delete Columns Sent Date copy & Recd Time Copy
• Change Type Date & Time Column to Date & Time - sort Ascending
• Close & Load

The trick was adding the serial date & time values to create the Date & Time column for time sort.

I hope this helps

 

[MrDB4Excel]

Hi RasGhul,
I finally had a go at this and it all seems good except some bits are missing and it seems some columns are not needed as you can see in the attached XL2bb Mini Sheet.
The previous Mini Sheet I sent contained sort of bogus data. This new Mini Sheet is the real deal but only 36 rows to keep it short.

Conversations with Roger Caymin-Word2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Doc #	Link Code	Sender	Subject	Date	@	Time	Month	Day	Column1	Column2	Doc #3	Link Code 2	0	40	N50	1219 >	<<< Last Number Used	Column4	50	Date & Time
2	34		Codger	but you don't have a badge	01/10/2021 00:00	@	0.679166667	Jan	10	16	18	34									44206.67917
3	80		Codger	major brain teaser...	01/24/2021 00:00	@	0.6125	Jan	24	14	42	80									44220.6125
4	1	21Fd01-1	dodge	Re: trumperstampers...	01/01/2021 00:00	@	0.508333333	Jan	1	10	38	1	21Fd01-1--Message received from dodge Fri 01/Jan/2021--@--12:12	1							44197.50833
5	2		Codger	truth???...	01/01/2021 00:00	@	0.508333333	Jan	1	13	53	1									44197.50833
6	20	21Fd01-7	dodge	even better rain site	01/06/2021 00:00	@	0.474305556	Jan	6	11	23	20	21Fd01-7--Message received from dodge Wed 06/Jan/2021--@--11:23	7							44202.47431
7	59		Codger	forgot	01/19/2021 00:00	@	0.434722222	Jan	19	10	26	59									44215.43472
8	85		Codger	Re: cheerio chap...	01/30/2021 00:00	@	0.127777778	Jan	30	3	4	85									44226.12778
9	53	21Fd01-23	dodge	Re: fascinating...	01/16/2021 00:00	@	0.929861111	Jan	16	22	19	53	21Fd01-23--Message received from dodge Sat 16/Jan/2021--@--22:19	23							44212.92986
10	72		Codger	CRPS...	01/21/2021 00:00	@	0.409722222	Jan	21	9	50	72									44217.40972
11	17		Codger	excellent joke	01/05/2021 00:00	@	0.441666667	Jan	5	10	36	17									44201.44167
12	37	21Fd01-15	dodge	Re: *******'s seditious nature...	01/12/2021 00:00	@	0.917361111	Jan	12	22	1	37	21Fd01-15--Message received from dodge Tue 12/Jan/2021--@--22:01	15							44208.91736
13	68		Codger	I take it back somewhat	01/20/2021 00:00	@	0.625694444	Jan	20	15	1	68									44216.62569
14	58		Codger	more holes and then some	01/19/2021 00:00	@	0.429861111	Jan	19	10	19	58									44215.42986
15	24	21Fd01-9	dodge	Re: dog barking material, maybe	01/07/2021 00:00	@	0.490277778	Jan	7	11	46	24	21Fd01-9--Message received from dodge Thu 07/Jan/2021--@--11:46	9							44203.49028
16	30	21Fd01-12	dodge	Re: little bit this, little bit that...	01/09/2021 00:00	@	0.167361111	Jan	9	4	1	30	21Fd01-12--Message received from dodge Sat 09/Jan/2021--@--04:01	12							44205.16736
17	41	21Fd01-16	dodge	Re: so funny yet so true	01/13/2021 00:00	@	0.946527778	Jan	13	22	43	41	21Fd01-16--Message received from dodge Wed 13/Jan/2021--@--22:43	16							44209.94653
18	28	21Fd01-11	dodge	Re: This from CNBC	01/08/2021 00:00	@	0.436805556	Jan	8	10	29	28	21Fd01-11--Message received from dodge Fri 08/Jan/2021--@--10:29	11							44204.43681
19	5	21Fd01-2	dodge	Re: truth???...	01/02/2021 00:00	@	0.028472222	Jan	2	0	41	5	21Fd01-2--Message received from dodge Sat 02/Jan/2021--@--00:41	2							44198.02847
20	38		Codger	in addition...	01/12/2021 00:00	@	0.934027778	Jan	12	22	25	38									44208.93403
21	13		Codger	absolute perfect sense	01/04/2021 00:00	@	0.503472222	Jan	4	12	5	13									44200.50347
22	64	21Fd01-29	dodge	Re: Humiliated...	01/20/2021 00:00	@	0.434027778	Jan	20	10	25	64	21Fd01-29--Message received from dodge Wed 20/Jan/2021--@--10:25	29							44216.43403
23	44		Codger	magnetars before today...	01/14/2021 00:00	@	0.559722222	Jan	14	13	26	44									44210.55972
24	77	21Fd01-34	dodge	Re: selfish sycophants...	01/22/2021 00:00	@	0.114583333	Jan	22	2	45	77	21Fd01-34--Message received from dodge Fri 22/Jan/2021--@--02:45	34							44218.11458
25	40		Codger	Impeachment Resolution	01/13/2021 00:00	@	0.928472222	Jan	13	22	17	40									44209.92847
26	88		Codger	me too, old and sleepy	01/31/2021 00:00	@	0.970138889	Jan	31	23	17	88									44227.97014
27	55	21Fd01-24	dodge	Re: another good one from Bill Maher	01/17/2021 00:00	@	0.61875	Jan	17	14	51	55	21Fd01-24--Message received from dodge Sun 17/Jan/2021--@--14:51	24							44213.61875
28	79	21Fd01-35	dodge	Re: it has been a long year	01/24/2021 00:00	@	0.495833333	Jan	24	11	54	79	21Fd01-35--Message received from dodge Sun 24/Jan/2021--@--11:54	35							44220.49583
29	62		Codger	Humiliated...	01/20/2021 00:00	@	0.166666667	Jan	20	4	0	62									44216.16667
30	82	21Fd01-37	dodge	OK?	01/29/2021 00:00	@	0.95625	Jan	29	22	57	82	21Fd01-37--Message received from dodge Fri 29/Jan/2021--@--22:57	37							44225.95625
31	10		Codger	cool stuff...	01/03/2021 00:00	@	0.536805556	Jan	3	12	53	10									44199.53681
32	76	21Fd01-33	dodge	Re: safe travel	01/21/2021 00:00	@	0.528472222	Jan	21	12	41	76	21Fd01-33--Message received from dodge Thu 21/Jan/2021--@--12:41	33							44217.52847
33	86		Codger	Blue Walnuts	01/30/2021 00:00	@	0.409027778	Jan	30	9	49	86									44226.40903
34	75	21Fd01-32	dodge	Re: CRPS...	01/21/2021 00:00	@	0.479861111	Jan	21	11	31	75	21Fd01-32--Message received from dodge Thu 21/Jan/2021--@--11:31	32							44217.47986
35	71		Codger	selfish sycophants...	01/20/2021 00:00	@	0.95625	Jan	20	22	57	71									44216.95625
36	52		Codger	fascinating...	01/16/2021 00:00	@	0.642361111	Jan	16	15	25	52									44212.64236
JanWordDoc

Here is the Mini Sheet of the "With this table Close & Load to / Only Create Connection / Tick Add this to Data Model" step in your instructions. This is the sheet named 21TdJan. Previous bogus tables were from a file named for the year 2024 is why the sheets were named 24FdJan & 24TdJan.

Conversations with Roger Caymin-Word2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Doc #	Link Code	Sender	Subject	Sent Date	@	Rec'd Time	Month	Day	Column1	Column2	Doc #3	Link Code 2	Column4	50	N50	1219 >	<<< Last Number Used
2	2 >	#VALUE!	Codger	truth???...	Fri 01/Jan/2021	@	12:12	Jan	01	13	53	1 >	#VALUE!	#VALUE!
3	3 >	#VALUE!	Codger	ever hear or read about this?	Fri 01/Jan/2021	@	15:02	Jan	01	15	02	3 >	#VALUE!	#VALUE!
4	4 >	#VALUE!	Codger	FW A Reformed Calendar By An Ancient Ruler	Fri 01/Jan/2021	@	15:03	Jan	01	15	03	4 >	#VALUE!	#VALUE!
5	6 >	#VALUE!	Codger	COVID numbers	Sat 02/Jan/2021	@	02:11	Jan	02	02	11	6 >	#VALUE!	#VALUE!
6	8 >	#VALUE!	Codger	did you know	Sat 02/Jan/2021	@	14:15	Jan	02	14	15	8 >	#VALUE!	#VALUE!
7	9 >	#VALUE!	Codger	interesting machine work	Sun 03/Jan/2021	@	12:39	Jan	03	12	39	9 >	#VALUE!	#VALUE!
8	10 >	#VALUE!	Codger	cool stuff...	Sun 03/Jan/2021	@	12:53	Jan	03	12	53	10 >	#VALUE!	#VALUE!
9	11 >	#VALUE!	Codger	legitimate question	Sun 03/Jan/2021	@	22:22	Jan	03	22	22	11 >	#VALUE!	#VALUE!
10	13 >	#VALUE!	Codger	absolute perfect sense	Mon 04/Jan/2021	@	12:05	Jan	04	12	05	13 >	#VALUE!	#VALUE!
11	14 >	#VALUE!	Codger	cooperation is key...	Mon 04/Jan/2021	@	12:31	Jan	04	12	31	14 >	#VALUE!	#VALUE!
21TdJan

Cell Formulas
Range	Formula
M2:M11	M2	=IF(OR(ISBLANK(E2),ISBLANK(G2)),"",B2&"--Message sent to dodge "&TEXT(E2+G2,"ddd dd/mmm/yyyy--\@--hh:mm"))
N2:N10	N2	=RIGHT(B2,1)
G3:G11	G3	=IF(OR(ISBLANK(J3),ISBLANK(K3)),"",VALUE(LEFT(J3,2)&":"&RIGHT(K3,2)&":00"))
H3:H11	H3	=TEXT(E3,"MMM")
I3:I11	I3	=TEXT(E3,"DD")
L3:L11	L3	=IF(ISBLANK(A3),"",(A3)*1)
N11	N11	=RIGHT(B11,2)
B2:B11	B2	=TabLegend!$I$1&"Td"&TEXT(E2,"MM-")&SUM(N1+1)