Dynamic chart based on last 24 hours

[Craig1]

Hi Guys,

I've created a chart regarding production and would like to make it dynamic, but it's not a straight forward dynamic chart, I don't think.
Basically what I need is the chart to find the data for the last 24 hours. In column A I have the batch number which is the Horizontal Axis Labels and in column B & C I have the start time and end time of the batch. The 2 lots of data are in columns F & H.
So for columns A, F & H I want them to look back until the find the batch that was active 24 hours ago.

Hope I have explained myself correctly. In the example below I would want to start from CRTAA80008 and back for 24 hours which in this case would be CRTAA80001 because this was copied at 01:44 so going back 24 hours this batch was active over the 24 hour mark.

Sorry I can't use excel Jeanie, work restrictions.

CRTAA80009 28-Sep-14 01:36:51 * 0:08 7996 4736 CRT 851 1195
CRTAA80008 28-Sep-14 00:48:17 28-Sep-14 01:36:51 0:48 1476 997 CRT 851 1195
CRTAA80007 27-Sep-14 22:30:01 28-Sep-14 00:48:17 2:18 519 1135 CRT 851 1195
CRTAA80006 27-Sep-14 21:49:05 27-Sep-14 22:30:01 0:40 1752 941 CRT 851 1195
CRTAA80005 27-Sep-14 12:38:04 27-Sep-14 21:49:05 9:11 130 681 CRT 851 1195
CRTAA80004 27-Sep-14 11:39:50 27-Sep-14 12:38:03 0:58 1231 692 CRT 851 1195
CRTAA80003 27-Sep-14 03:50:22 27-Sep-14 11:39:50 7:49 153 793 CRT 851 1195
CRTAA80002 27-Sep-14 03:00:19 27-Sep-14 03:50:22 0:50 1433 1020 CRT 851 1195
CRTAA80001 27-Sep-14 01:02:12 27-Sep-14 03:00:19 1:58 607 956 CRT 851 1195
BHLAA80094 27-Sep-14 00:03:30 27-Sep-14 00:45:53 0:58 1304 927 BHL 887 1276
BHLAA80093 26-Sep-14 21:44:19 27-Sep-14 00:03:30 2:19 550 1227 BHL 887 1276
BHLAA80092 26-Sep-14 21:04:05 26-Sep-14 21:44:19 0:40 1903 1219 BHL 887 1276

Thanks in advance.

Craig.

 

[cyrilbrd]

Hi,
given in A1"

batch#	Start	End	ColD	ColE	ColF	ColG	ColH	ColI
CRTAA80009	9/28/14 1:36		0:08	7996	4736	CRT	851	1195
CRTAA80008	9/28/14 0:48	9/28/14 1:36	0:48	1476	997	CRT	851	1195
CRTAA80007	9/27/14 22:30	9/28/14 0:48	2:18	519	1135	CRT	851	1195
CRTAA80006	9/27/14 21:49	9/27/14 22:30	0:40	1752	941	CRT	851	1195
CRTAA80005	9/27/14 12:38	9/27/14 21:49	9:11	130	681	CRT	851	1195
CRTAA80004	9/27/14 11:39	9/27/14 12:38	0:58	1231	692	CRT	851	1195
CRTAA80003	9/27/14 3:50	9/27/14 11:39	7:49	153	793	CRT	851	1195
CRTAA80002	9/27/14 3:00	9/27/14 3:50	0:50	1433	1020	CRT	851	1195
CRTAA80001	9/27/14 1:02	9/27/14 3:00	1:58	607	956	CRT	851	1195
BHLAA80094	9/27/14 0:03	9/27/14 0:45	0:58	1304	927	BHL	887	1276
BHLAA80093	9/26/14 21:44	9/27/14 0:03	2:19	550	1227	BHL	887	1276
BHLAA80092	9/26/14 21:04	9/26/14 21:44	0:40	1903	1219	BHL	887	1276

<tbody>
</tbody>

Would this range be what you needed assuming that present date time is 9/28/2014 2:55:28 AM?

9/27/14 2:55	8
	9/27/14 3:00	1020	851
	9/27/14 3:50	793	851
	9/27/14 11:39	692	851
	9/27/14 12:38	681	851
	9/27/14 21:49	941	851
	9/27/14 22:30	1135	851
	9/28/14 0:48	997	851
	9/28/14 1:36	4736	851

<tbody>
</tbody>

 

[Craig1]

Thanks for the reply Cyrilbrd, nearly there.
I can only use batch data that is complete so the 09 batch wouldn't be used. But it looks like the other info is correct, except the time and date wouldn't want to be used I would want the batch number to be used for the horizontal axis label. Sometimes the data will have 20 rows and sometimes it may only have 5, but looking at your work I think it looks correct. As long as it looks back 24 hours from NOW() and that the looking back captures the batch that was active 24 hours ago even if it started 26 hours ago, as long as it is still in process. Hope I have explained it right.
Thanks again.

Craig.

 

[cyrilbrd]

Ok understood, CRTAA80009 is to be excluded as it does not hold a value in Column C. Right?
Yes the data will actually show the batch number instead of the date, I just wanted to demonstrate and make sure to have understood your query.
Number of entries is not an issue.
Will adjust formulae and get back to you tomorrow. I am heading home...
Sent from my phone.

 

[oldbrewer]

Start	End	ColD	ColE	ColF	ColG	ColH	ColI
28/09/2014 05:45	28/09/2014 06:20	00:35	7996	4736	CRT	851	1195
28/09/2014 03:21	28/09/2014 03:56	00:35	1476	997	CRT	851	1195
28/09/2014 00:57	28/09/2014 01:32	00:35	519	1135	CRT	851	1195
27/09/2014 22:33	27/09/2014 23:08	00:35	1752	941	CRT	851	1195
27/09/2014 20:09	27/09/2014 20:44	00:35	130	681	CRT	851	1195
27/09/2014 17:45	27/09/2014 18:20	00:35	1231	692	CRT	851	1195
27/09/2014 15:21	27/09/2014 15:56	00:35	153	793	CRT	851	1195
27/09/2014 12:57	27/09/2014 13:32	00:35	1433	1020	CRT	851	1195
27/09/2014 10:33	27/09/2014 11:08	00:35	607	956	CRT	851	1195
27/09/2014 08:09	27/09/2014 08:44	00:35	1304	927	BHL	887	1276
27/09/2014 05:45	27/09/2014 06:20	00:35	550	1227	BHL	887	1276
27/09/2014 03:21	27/09/2014 03:56	00:35	1903	1219	BHL	887	1276
	batches that ended between	28/09/2014 06:15	and	27/09/2014 06:15
					(assumed that 28/09/2014 06:15 is time of sampling the data)
	batch	col E
cell C25>>>>>>	CRTAA80008	1476		formula in C25
	CRTAA80007	519		=IF(C3<$E$19,IF(C3>$G$19,A3,""),"")
	CRTAA80006	1752
	CRTAA80005	130
	CRTAA80004	1231
	CRTAA80003	153		formula in D25
	CRTAA80002	1433		=IF(C25="","",OFFSET($A$1,MATCH(C25,$A$2:$A$13,0),4))
	CRTAA80001	607
	BHLAA80094	1304
	BHLAA80093	550

<colgroup><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[Craig1]

Thanks for the replies guys,
Cyrilbrd thanks for taking time to sort for me.
Oldbrewer, I can see what your doing but not 100% sure how to get that info into a dynamic chart.


Thanks again.
Craig

 

[oldbrewer]

Craig I automatically pulled the data just base the chart on that and remember to use na() for blanks to avoid them plotting - if tomorrow you have only 3 my data will give you three.....

 

[cyrilbrd]

Given data posted in post#2 in A1
Results in L1 as follows:

9/27/14 2:55	7	batch	data1	data2
	9/27/14 3:00	CRTAA80002	1020	851
	9/27/14 3:50	CRTAA80003	793	851
	9/27/14 11:39	CRTAA80004	692	851
	9/27/14 12:38	CRTAA80005	681	851
	9/27/14 21:49	CRTAA80006	941	851
	9/27/14 22:30	CRTAA80007	1135	851
	9/28/14 0:48	CRTAA80008	997	851

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Formula in L1 =NOW()-1
Formula in M1 =COUNTIFS(B2:B33,">"&L1,C2:C33,"<>"&"")
Formula in M2 =IF(ROWS($1:1)>$M$1,"",SMALL(IF($C$2:$C$35<>"",IF($B$2:$B$35>$L$1,$B$2:$B$34)),ROWS($1:1))) Ctrl + Shift + Enter not just Enter on a PC or command + Return on a MAC. Copied down till needed. This is the base to return the dates that are eligible.
Formula in N2 is =IF(ROWS($1:1)>$M$1,"",INDEX($A$2:$A$35,SMALL(IF($B$2:$B$35=$M2,ROW($A$1:$A$34)),COUNTIF($M$2:$M2,$M2)))) Ctrl + Shift + Enter not just Enter on a PC or command + Return on a MAC. Copied down till needed. This is your X-axis.
Formula in O2 and P2 based on =IF(ROWS($1:1)>$M$1,"",INDEX($F$2:$F$35,SMALL(IF($B$2:$B$35=$M2,ROW($A$1:$A$34)),COUNTIF($M$2:$M2,$M2)))) Ctrl + Shift + Enter not just Enter on a PC or command + Return on a MAC. Copied down till needed. Adjust range of index to fit your model and data requirement.


Dynamic chart with Named ranges as follows:
XAxis1 is =OFFSET($M$1,1,0,COUNTA($M:$M)-1)
YAxis 1 is =OFFSET($O$1,1,0,COUNTIF($O:$O,">0"))
YAxis2 is =OFFSET($P$1,1,0,COUNTIF($P:$P,">0"))

Thus will automatically update both data table and chart dynamically based on value in L1.
Would that work for you?

 

[Craig1]

Thanks guys, I will try it out when I'm back on nights and let you know the outcome.

Thanks again.

Craig

 

[Craig1]

Hi Guys,

Thanks for the support so far.
{=IF(ROWS($1:1)>$M$1,"",SMALL(IF($C$2:$C$35<>"",IF($B$2:$B$35>$L$1,$B$2:$B$34)),ROWS($1:1))} - amended to fit is giving me the first batch to start after the 24 hour and not the active batch 24 hours ago. Have I amended the code correctly.

Thanks in advance, as always

Craig.