How to automate Fill Rate?

R

rokunation

New Member
Joined
Apr 17, 2022
Messages
35
Office Version
  1. 2013
Platform
  1. Windows
I have to calculate fill rate monthly by location and wondering if there is any way to automate the process if I dump the data in a sheet, then the charts can appear in the another sheet.

Open to suggestions.

This is the data sheet:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1CUSTOMER NAMEINVOICE NUMBERINVOICE DATELINE ABBREVPART NUMBERDESCRIPTIONQUANTITY SOLDNET PRICEEXTENDED PRICEUNIT COSTORIGINPO NUMBERATTENTIONActual DateSTATUSIdentifierPart Request IDRequest TimeReqApproveTime Diff (Req-REQApp)Time Diff (Operational Hours) (Req-REQApp)Approved TimeReady TimeClosed TimeRequest->Ready Calendar TimeTime Difference (Approve-->Ready)Time Difference (Operational Hours) (Approve->Ready)Fill/No FillROLL UP NAME
2CITY OF TORONTO -DISCO9466314072022NGF7312MPOIL FILTER118.2318.2324.43NAPA ACCESS GENERATED079013 770662STOCK04/07/2022STOCKNGF7312MP079013 7706621476942022-04-07 11:45:27 AM2022-04-07 11:51:12 AM0:05:450:05:452022-04-07 1:20:41 PM2022-04-07 1:36:22 PM2022-04-07 2:12:15 PM1:50:550:15:410:15:41FILLCITY OF TORONTO -DISCO
3CITY OF TORONTO -DISCO9466314072022NGF9886AIR FILTER165.9665.9688.38NAPA ACCESS GENERATED079013 770662STOCK04/07/2022STOCKNGF9886079013 7706621476952022-04-07 11:46:03 AM2022-04-07 11:51:12 AM0:05:090:05:092022-04-07 1:20:41 PM2022-04-07 1:36:31 PM2022-04-07 2:12:15 PM1:50:280:15:500:15:50FILLCITY OF TORONTO -DISCO
4CITY OF TORONTO -DISCO9466144052022NUP89250BATTERY127.8227.8227.82NAPA ACCESS GENERATED267085 770302STOCK04/05/2022STOCKNUP89250267085 7703021472422022-04-05 7:50:00 AM2022-04-05 8:21:11 AM0:31:110:31:112022-04-05 8:31:31 AM2022-04-05 8:47:52 AM2022-04-05 9:12:20 AM0:57:520:16:210:16:21FILLCITY OF TORONTO -DISCO
5CITY OF TORONTO -DISCO9467144212022NLG900331004 HALOGEN CAPSULE15.585.585.58NAPA ACCESS GENERATED204039 771567STOCK04/21/2022STOCKNLG9003204039 7715671491172022-04-21 12:39:25 PM2022-04-21 1:14:53 PM0:35:280:35:282022-04-21 1:14:59 PM2022-04-21 1:31:22 PM2022-04-21 2:02:18 PM0:51:570:16:230:16:23FILLCITY OF TORONTO -DISCO
6CITY OF TORONTO -DISCO9466664122022NGF1748XDHD LUBE FILTER142.3442.3456.73NAPA ACCESS GENERATED188048 771083STOCK04/12/2022STOCKNGF1748XD188048 7710831482422022-04-12 12:29:27 PM2022-04-12 12:35:03 PM0:05:360:05:362022-04-12 1:01:44 PM2022-04-12 1:18:52 PM2022-04-12 1:20:57 PM0:49:250:17:080:17:08FILLCITY OF TORONTO -DISCO
7CITY OF TORONTO -DISCO9466244062022KFL472GASKET CLEAR SILICONE212.0224.0412.02NAPA ACCESS GENERATED194005 770502STOCK04/06/2022STOCKKFL472194005 7705021474172022-04-06 8:18:08 AM2022-04-06 8:19:39 AM0:01:310:01:312022-04-06 8:45:00 AM2022-04-06 9:02:32 AM2022-04-06 11:42:45 AM0:44:240:17:320:17:32FILLCITY OF TORONTO -DISCO
8CITY OF TORONTO -DISCO9466264062022NGF7182HD LUBE FILTER112.5412.5416.8NAPA ACCESS GENERATED204032 770501STOCK04/06/2022STOCKNGF7182204032 7705011474082022-04-06 7:50:24 AM2022-04-06 7:53:56 AM0:03:320:03:322022-04-06 8:45:00 AM2022-04-06 9:02:32 AM2022-04-06 11:44:49 AM1:12:080:17:320:17:32FILLCITY OF TORONTO -DISCO
9CITY OF TORONTO -DISCO94662940720225KF25960KEEP FILL1482.57482.57482.57NAPA ACCESS GENERATEDFL1702 770210STOCK04/07/2022STOCK5KF25960FL1702 7702101474092022-04-06 7:51:17 AM2022-04-06 8:33:47 AM0:42:300:42:302022-04-06 8:45:00 AM2022-04-06 9:02:32 AM2022-04-07 8:51:48 AM1:11:150:17:320:17:32FILLCITY OF TORONTO -DISCO
10CITY OF TORONTO -DISCO9467104212022DSSDS10025RUST CHECK110.3310.3310.33NAPA ACCESS GENERATED267127 771628STOCK04/21/2022STOCKDSSDS10025267127 7716281489782022-04-20 1:55:47 PM2022-04-20 2:46:21 PM0:50:340:50:342022-04-21 6:51:06 AM2022-04-21 7:08:53 AM2022-04-21 7:19:40 AM17:13:060:17:470:17:47FILLCITY OF TORONTO -DISCO
11CITY OF TORONTO -DISCO94667641320222SC87712194CABIN FILTER1164.24164.24164.24NAPA ACCESS GENERATED323085 769116STOCK04/13/2022STOCK2SC87712194323085 7691161474842022-04-06 9:36:34 AM2022-04-07 12:32:13 PM26:55:3911:25:392022-04-07 1:20:41 PM2022-04-07 1:39:30 PM2022-04-13 7:50:24 AM28:02:560:18:490:18:49FILLCITY OF TORONTO -DISCO
12CITY OF TORONTO -DISCO94664740820222TSICE1480CCABLE TIES / TECHSPAN111.1411.1411.14GENERATED AT THE COUNTER159034 770300STOCK04/08/2022STOCK2TSICE1480C159034 7703001477002022-04-07 12:02:57 PM2022-04-07 12:04:59 PM0:02:020:02:022022-04-07 1:20:41 PM2022-04-07 1:39:45 PM2022-04-08 11:16:53 AM1:36:480:19:040:19:04FILLCITY OF TORONTO -DISCO
13CITY OF TORONTO -DISCO9466684122022KFL837AIR INTAKE KLEEN26.0112.026.01NAPA ACCESS GENERATED188042 770520STOCK04/12/2022STOCKKFL837188042 7705201477132022-04-07 1:06:49 PM2022-04-07 1:19:50 PM0:13:010:13:012022-04-07 1:20:51 PM2022-04-07 1:39:55 PM2022-04-12 2:21:50 PM0:33:060:19:040:19:04FILLCITY OF TORONTO -DISCO
14CITY OF TORONTO -DISCO94667641320222SC6D84389987CABIN AIR FILTER152.552.552.5NAPA ACCESS GENERATED323085 769116STOCK04/13/2022STOCK2SC6D84389987323085 7691161474852022-04-06 9:36:50 AM2022-04-07 12:46:30 PM27:09:4011:39:402022-04-07 1:20:41 PM2022-04-07 1:40:08 PM2022-04-13 7:50:25 AM28:03:180:19:270:19:27FILLCITY OF TORONTO -DISCO
15CITY OF TORONTO -DISCO94669041420222TWTRL4550TAIL LIGHT RED122.8822.8822.88NAPA ACCESS GENERATED267232 770964STOCK04/14/2022STOCK2TWTRL4550267232 7709641481932022-04-12 7:30:51 AM2022-04-12 10:54:26 AM3:23:353:23:352022-04-12 11:22:14 AM2022-04-12 11:42:16 AM2022-04-14 1:39:44 PM4:11:250:20:020:20:02FILLCITY OF TORONTO -DISCO
16CITY OF TORONTO -DISCO9466334072022NGF4466CABIN FILTER117.5517.5523.51NAPA ACCESS GENERATED194025 770503STOCK04/07/2022STOCKNGF4466194025 7705031475202022-04-06 10:49:57 AM2022-04-06 10:56:52 AM0:06:550:06:552022-04-06 11:18:21 AM2022-04-06 11:39:43 AM2022-04-07 2:17:18 PM0:49:460:21:220:21:22FILLCITY OF TORONTO -DISCO
17CITY OF TORONTO -DISCO9466334072022NGF500804AIR FILTER-CORRUGATED1105.24105.24141.02NAPA ACCESS GENERATED194025 770503STOCK04/07/2022STOCKNGF500804194025 7705031475192022-04-06 10:49:38 AM2022-04-06 10:58:21 AM0:08:430:08:432022-04-06 11:18:21 AM2022-04-06 11:39:47 AM2022-04-07 2:17:17 PM0:50:090:21:260:21:26FILLCITY OF TORONTO -DISCO
18CITY OF TORONTO -DISCO94670242020226RT4160041600 WYPALL187.3587.3587.35NAPA ACCESS GENERATEDFL1702 769242STOCK04/20/2022STOCK6RT41600FL1702 7692421483892022-04-13 8:49:26 AM2022-04-13 8:58:59 AM0:09:330:09:332022-04-13 11:19:59 AM2022-04-13 11:42:25 AM2022-04-20 7:50:36 AM2:52:590:22:260:22:26FILLCITY OF TORONTO -DISCO
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G144:G1424,G2:G41Cell Value<0textNO
G1Cell Value<1textNO




This is the graph sheet:

May 2022- Fill Rate COT FILE.xlsx
CDEFGHIJKLM
4
5Fill RateMay 20222022
6TOTAL QTYSTOCK (Marked Stock + <1h )NON STOCK (Marked Non-Stock + >1h)Monthly Fill RateTotal QtySTOCK (Marked Stock + <1h )NON STOCK (Marked Non-Stock + >1h)CTD
7Disco3572936482.07%2228159663271.63%
8Eastern69753016776.04%58543589226561.31%
9Ellesmere1510127623484.50%104847749273573.91%
10Finch4073317681.33%32102168104267.54%
11Total2971243054181.79%2177615102667469.35%
12
13Missed Fills- May 2022
14TOTAL QTY (Marked STOCK)Missed Fills (Marked STOCK but not READY <1h)Missed Fill Opportunities
15Disco312196.09%
16Eastern580508.62%
17Ellesmere1333574.28%
18Finch344133.78%
19Total25691395.41%
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SUMMARY
Cell Formulas
RangeFormula
F7:F10F7=D7-E7
L7:L11,G7:G11G7=E7/D7
I7I7=336+276+283+315+306+355+357
J7J7=152+199+226+269+246+211+293
K7K7=184+77+57+46+60+144+64
I8I8=831+876+790+975+960+725+697
J8J8=403+479+475+593+618+491+530
K8K8=428+397+315+382+342+234+167
I9I9=1445+1179+1286+1485+2069+1510+1510
J9J9=1009+828+898+1008+1539+1191+1276
K9K9=436+351+388+477+530+319+234
I10I10=464+394+383+509+657+396+407
J10J10=247+247+258+346+427+312+331
K10K10=217+147+125+163+230+84+76
D19:E19,D11:F11,I11:K11I11=SUM(I7:I10)
F15:F19F15=E15/D15
 

Attachments

  • Capture.PNG
    Capture.PNG
    15.2 KB · Views: 8

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
298
dshtesy
D
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
256
Raj1979
R
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
505
sharky12345
sharky12345
A
  • Solved
Count entry by time
Replies
3
Views
191
admvlad
A
S
  • Solved
Count of active employees between dates.
Replies
3
Views
658
Fluff
Fluff

Forum statistics

Threads
1,223,684
Messages
6,173,826
Members
452,535
Latest member
berdex

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top