Plot a chart based on common date from multiple date rows using VBA.

[Muhtar]

Hi,

I have a data like this (with many dates and outputs, here is just a simplified example):

I want to plot a line chart showing all outputs with one single x-axis (CommonDate: 02/01/2024 - 02/21/2024). however, coming up with CommonDate row based on the date1/date2/date3/,,, etc is challenging. Can someone help? My goal is to create a CommonDate row and then plot all outputs in a single line chart.

 

[myall_blues]

Try this perhaps? This is a dynamic array formula so you only need to enter it in one cell and it will spill to the right assuming there is nothing in the way.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date 1	1/02/2024	2/02/2024	3/02/2024	4/02/2024	5/02/2024	6/02/2024	7/02/2024	8/02/2024	9/02/2024	10/02/2024	11/02/2024	12/02/2024	13/02/2024	14/02/2024
2	Output 1	89	89	89	89	89	89	89	89	89	89	89	89
3	Date 2	5/02/2024	6/02/2024	7/02/2024	8/02/2024	9/02/2024	10/02/2024	11/02/2024	12/02/2024
4	Output 2	7	8	9	5	7	2	3
5	Date 3												16/02/2024	17/02/2024	18/02/2024	19/02/2024	20/02/2024	21/02/2024
6	Output 3												82	67	45	45	43	76
7
8
9	Common Date	1/02/2024	2/02/2024	3/02/2024	4/02/2024	5/02/2024	5/02/2024	6/02/2024	6/02/2024	7/02/2024	7/02/2024	8/02/2024	8/02/2024	9/02/2024	9/02/2024	10/02/2024	10/02/2024	11/02/2024	11/02/2024
10		89	89	89	89	89	7	89	8	89	9	89	5	89	7	89	2	89	3
11
Sheet1

Cell Formulas
Range	Formula
B9:Z10	B9	=SORT(HSTACK(FILTER(B1:R2,B2:R2<>""),FILTER(B3:R4,B4:R4<>""),FILTER(B5:R6,B6:R6<>"")),,,TRUE)
Dynamic array formulas.