Dynamic chart based on last 24 hours

Craig1

Active Member
Joined
Jun 11, 2009
Messages
320
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi,
given in A1"
batch#StartEndColDColEColFColGColHColI
CRTAA800099/28/14 1:360:0879964736CRT8511195
CRTAA800089/28/14 0:489/28/14 1:360:481476997CRT8511195
CRTAA800079/27/14 22:309/28/14 0:482:185191135CRT8511195
CRTAA800069/27/14 21:499/27/14 22:300:401752941CRT8511195
CRTAA800059/27/14 12:389/27/14 21:499:11130681CRT8511195
CRTAA800049/27/14 11:399/27/14 12:380:581231692CRT8511195
CRTAA800039/27/14 3:509/27/14 11:397:49153793CRT8511195
CRTAA800029/27/14 3:009/27/14 3:500:5014331020CRT8511195
CRTAA800019/27/14 1:029/27/14 3:001:58607956CRT8511195
BHLAA800949/27/14 0:039/27/14 0:450:581304927BHL8871276
BHLAA800939/26/14 21:449/27/14 0:032:195501227BHL8871276
BHLAA800929/26/14 21:049/26/14 21:440:4019031219BHL8871276

<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:558
9/27/14 3:001020851
9/27/14 3:50793851
9/27/14 11:39692851
9/27/14 12:38681851
9/27/14 21:49941851
9/27/14 22:301135851
9/28/14 0:48997851
9/28/14 1:364736851

<tbody>
</tbody>
 

Craig1

Active Member
Joined
Jun 11, 2009
Messages
320
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Apr 11, 2010
Messages
11,003

ADVERTISEMENT

StartEndColDColEColFColGColHColI
28/09/2014 05:4528/09/2014 06:2000:3579964736CRT8511195
28/09/2014 03:2128/09/2014 03:5600:351476997CRT8511195
28/09/2014 00:5728/09/2014 01:3200:355191135CRT8511195
27/09/2014 22:3327/09/2014 23:0800:351752941CRT8511195
27/09/2014 20:0927/09/2014 20:4400:35130681CRT8511195
27/09/2014 17:4527/09/2014 18:2000:351231692CRT8511195
27/09/2014 15:2127/09/2014 15:5600:35153793CRT8511195
27/09/2014 12:5727/09/2014 13:3200:3514331020CRT8511195
27/09/2014 10:3327/09/2014 11:0800:35607956CRT8511195
27/09/2014 08:0927/09/2014 08:4400:351304927BHL8871276
27/09/2014 05:4527/09/2014 06:2000:355501227BHL8871276
27/09/2014 03:2127/09/2014 03:5600:3519031219BHL8871276
batches that ended between28/09/2014 06:15and27/09/2014 06:15
(assumed that 28/09/2014 06:15 is time of sampling the data)
batchcol E
cell C25>>>>>>CRTAA800081476formula in C25
CRTAA80007519=IF(C3<$E$19,IF(C3>$G$19,A3,""),"")
CRTAA800061752
CRTAA80005130
CRTAA800041231
CRTAA80003153formula in D25
CRTAA800021433=IF(C25="","",OFFSET($A$1,MATCH(C25,$A$2:$A$13,0),4))
CRTAA80001607
BHLAA800941304
BHLAA80093550

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

Craig1

Active Member
Joined
Jun 11, 2009
Messages
320
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

Board Regular
Joined
Apr 11, 2010
Messages
11,003

ADVERTISEMENT

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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Given data posted in post#2 in A1
Results in L1 as follows:
9/27/14 2:557batchdata1data2
9/27/14 3:00CRTAA800021020851
9/27/14 3:50CRTAA80003793851
9/27/14 11:39CRTAA80004692851
9/27/14 12:38CRTAA80005681851
9/27/14 21:49CRTAA80006941851
9/27/14 22:30CRTAA800071135851
9/28/14 0:48CRTAA80008997851

<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

Active Member
Joined
Jun 11, 2009
Messages
320
Thanks guys, I will try it out when I'm back on nights and let you know the outcome.

Thanks again.

Craig
 

Craig1

Active Member
Joined
Jun 11, 2009
Messages
320
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,965
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top