How to combine ranges and sort chronologically with a single formula?

[alexcr]

Let's say you have the following...

• In A1:J1, a range of dates; in B2:J2, a range of values corresponding to those dates.
• In A4:D4, a range of dates; in A5:D5, a range of values corresponding to those dates.
• In A7:Y7, a range of dates; in A8:Y8, a range of values corresponding to those dates.

You want to combine all the aforementioned dates in Row 10, starting in A10, without skipping over any duplicate dates. And you want the corresponding values for those dates to be in Row 11, starting in A11. Finally, you want this data to be sorted chronologically, from earlier date to latest date.

Is there a formula that you can enter in A10 and A11 that would accomplish this?

(Note that the data in Rows 1-2, Rows 4-5, and Rows 7-8 is regularly changing, with date/value columns being added and removed, so this formula would ideally accommodate for such changes without needing to be updated.)

Thanks in advance!

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	22/10/2021	20/03/2021	14/06/2021	11/06/2021	22/08/2021
2	64	89	76	99	56
3
4	11/06/2021	25/09/2021	16/04/2021	18/11/2021	01/06/2021	01/12/2021
5	8	95	9	39	54	50
6
7	20/08/2021	03/04/2021	16/02/2021	21/04/2021	03/02/2021	23/09/2021	20/08/2021
8	30	63	26	79	90	70	79
9
10	03/02/2021	16/02/2021	20/03/2021	03/04/2021	16/04/2021	21/04/2021	01/06/2021	11/06/2021	11/06/2021	14/06/2021	20/08/2021	20/08/2021	22/08/2021	23/09/2021	25/09/2021	22/10/2021	18/11/2021	01/12/2021
11	90	26	89	63	9	79	54	99	8	76	30	79	56	70	95	64	39	50
Main

Cell Formulas
Range	Formula
A10:R11	A10	=SORT(CHOOSE({1;2},TRANSPOSE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,A1:Z1,A4:Z4,A7:Z7)&"</m></k>","//m")),TRANSPOSE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,A2:Z2,A5:Z5,A8:Z8)&"</m></k>","//m"))),,1,1)
Dynamic array formulas.

 

[alexcr]

How about

That worked! Much thanks, @Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.