National_Shipping
New Member
- Joined
- Mar 14, 2019
- Messages
- 2
So, I want to create a Pivot table that displays DELIVERY DATE, sum of RUN#, sum of ORDER#, sum of ORDER AMOUNT, calculates RUN TIME (using DISPATCH TIME & RETURN TIME) in one or two Pivot Table(s). I am having a tough time figuring it out.
Data sample is below that I import into a table from another excel file. So...
[TABLE="width: 798"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Scheduled Asset[/TD]
[TD]Order #[/TD]
[TD]Invoice #[/TD]
[TD]Delivery Date[/TD]
[TD]Order Amount[/TD]
[TD]Dispatch Time[/TD]
[TD]Run#[/TD]
[TD]Return Time[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]939387[/TD]
[TD]939387[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2416.47 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]939032[/TD]
[TD]J39032[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]920.38 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]935695[/TD]
[TD]851740[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]942040[/TD]
[TD]942040[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]646.05 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]944240[/TD]
[TD]944240[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]893.1 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]941981[/TD]
[TD]941981[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]317.94 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]942344[/TD]
[TD]942344[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]891.95 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]938106[/TD]
[TD]K38106[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]414.95 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]944048[/TD]
[TD]944048[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]112.35 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]942994[/TD]
[TD]942994[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]336.5 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]944232[/TD]
[TD]851745[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]f T5[/TD]
[TD]944331[/TD]
[TD]944331[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]115.69 [/TD]
[TD]6:43:00 AM[/TD]
[TD]3740[/TD]
[TD="align: right"]8:33:00 AM[/TD]
[/TR]
[TR]
[TD]f T5[/TD]
[TD]943915[/TD]
[TD]943915[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]608.16 [/TD]
[TD]6:43:00 AM[/TD]
[TD]3740[/TD]
[TD="align: right"]8:33:00 AM[/TD]
[/TR]
[TR]
[TD]m T14[/TD]
[TD]943407[/TD]
[TD]J43407[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2027.04 [/TD]
[TD]6:50:00 AM[/TD]
[TD]3739[/TD]
[TD="align: right"]8:25:00 AM[/TD]
[/TR]
[TR]
[TD]m T14[/TD]
[TD]944204[/TD]
[TD]944204[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]472.5 [/TD]
[TD]6:50:00 AM[/TD]
[TD]3739[/TD]
[TD="align: right"]8:25:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]943479[/TD]
[TD]J43479[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]1488.82 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]939219[/TD]
[TD]939219[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]493.47 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]941287[/TD]
[TD]941287[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]306.33 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]939608[/TD]
[TD]939608[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2117.76 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]942643[/TD]
[TD]942643[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]198.43 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]942233[/TD]
[TD]942233[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]988.75 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]943766[/TD]
[TD]943766[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]102.77 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]936471[/TD]
[TD]936471[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2148.31 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]936495[/TD]
[TD]936495[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2148.31 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]936564[/TD]
[TD]936564[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2097.7 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]899291[/TD]
[TD]I99291[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]11416 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]941893[/TD]
[TD]941893[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]277.18 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]942165[/TD]
[TD]942165[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]268.71 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]925239[/TD]
[TD]925239[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]853.3 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]939620[/TD]
[TD]851741[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]m T10[/TD]
[TD]944194[/TD]
[TD]944194[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]10276.05 [/TD]
[TD]7:55:00 AM[/TD]
[TD]3738[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
Data sample is below that I import into a table from another excel file. So...
[TABLE="width: 798"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Scheduled Asset[/TD]
[TD]Order #[/TD]
[TD]Invoice #[/TD]
[TD]Delivery Date[/TD]
[TD]Order Amount[/TD]
[TD]Dispatch Time[/TD]
[TD]Run#[/TD]
[TD]Return Time[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]939387[/TD]
[TD]939387[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2416.47 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]939032[/TD]
[TD]J39032[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]920.38 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]935695[/TD]
[TD]851740[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]942040[/TD]
[TD]942040[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]646.05 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]944240[/TD]
[TD]944240[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]893.1 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]941981[/TD]
[TD]941981[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]317.94 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]942344[/TD]
[TD]942344[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]891.95 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]938106[/TD]
[TD]K38106[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]414.95 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]944048[/TD]
[TD]944048[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]112.35 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]942994[/TD]
[TD]942994[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]336.5 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]b T12[/TD]
[TD]944232[/TD]
[TD]851745[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0 [/TD]
[TD]5:59:00 AM[/TD]
[TD]3728[/TD]
[TD="align: right"]3:03:00 PM[/TD]
[/TR]
[TR]
[TD]f T5[/TD]
[TD]944331[/TD]
[TD]944331[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]115.69 [/TD]
[TD]6:43:00 AM[/TD]
[TD]3740[/TD]
[TD="align: right"]8:33:00 AM[/TD]
[/TR]
[TR]
[TD]f T5[/TD]
[TD]943915[/TD]
[TD]943915[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]608.16 [/TD]
[TD]6:43:00 AM[/TD]
[TD]3740[/TD]
[TD="align: right"]8:33:00 AM[/TD]
[/TR]
[TR]
[TD]m T14[/TD]
[TD]943407[/TD]
[TD]J43407[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2027.04 [/TD]
[TD]6:50:00 AM[/TD]
[TD]3739[/TD]
[TD="align: right"]8:25:00 AM[/TD]
[/TR]
[TR]
[TD]m T14[/TD]
[TD]944204[/TD]
[TD]944204[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]472.5 [/TD]
[TD]6:50:00 AM[/TD]
[TD]3739[/TD]
[TD="align: right"]8:25:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]943479[/TD]
[TD]J43479[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]1488.82 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]939219[/TD]
[TD]939219[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]493.47 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]941287[/TD]
[TD]941287[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]306.33 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]939608[/TD]
[TD]939608[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2117.76 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T13[/TD]
[TD]942643[/TD]
[TD]942643[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]198.43 [/TD]
[TD]6:57:00 AM[/TD]
[TD]3730[/TD]
[TD="align: right"]11:03:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]942233[/TD]
[TD]942233[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]988.75 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]943766[/TD]
[TD]943766[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]102.77 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]936471[/TD]
[TD]936471[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2148.31 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]936495[/TD]
[TD]936495[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2148.31 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T15[/TD]
[TD]936564[/TD]
[TD]936564[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2097.7 [/TD]
[TD]7:16:00 AM[/TD]
[TD]3726[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]899291[/TD]
[TD]I99291[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]11416 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]941893[/TD]
[TD]941893[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]277.18 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]942165[/TD]
[TD]942165[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]268.71 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]925239[/TD]
[TD]925239[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]853.3 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]b T11[/TD]
[TD]939620[/TD]
[TD]851741[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0 [/TD]
[TD]7:19:00 AM[/TD]
[TD]3729[/TD]
[TD="align: right"]11:36:00 AM[/TD]
[/TR]
[TR]
[TD]m T10[/TD]
[TD]944194[/TD]
[TD]944194[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]10276.05 [/TD]
[TD]7:55:00 AM[/TD]
[TD]3738[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[/TR]
</tbody>[/TABLE]