0

I have an unknown number of entries (rows) contained in 12 worksheets (1 per month). These rows track delivery data. I want to parse through each row, of each sheet and do the following:

Tally the number of deliveries from A to B. Tally the number of pieces from A to B. Tally the cost of delivery from A to B. Tally the total cargo weight from A to B.

These tally's need to be output to a summary worksheet and need to be both Monthly and YTD.

Sample columns, and a single data row, are as follows:

Date&Time PU     | Date&Time Del    |PU Loc. Name |PU Street        |PUCityStateZip       |DEL Loc.  Name|DEL Street        |DELCityStateZip        |Number of Pieces|Weight     |TotalAmt

01/01/2016 05:30 | 01/01/2016 07:30 | Warehouse A | 123 Nowhere St. | Toronto, ON P3L 3M0 | Hospital A   | 321 Made-up Ave. | Stratford, ON T45 6H8 | 6              | 240 Lbs   | $245.00
02/01/2016 06:30 | 02/01/2016 07:30 | Warehouse B | 123 Lost Road   | Hamilton, ON P3X 3Z0| Hospital A   | 321 Made-up Ave. | Stratford, ON T4P 6H8 | 3              | 240 Lbs   | $245.00
01/01/2016 09:30 | 02/01/2016 11:30 | Warehouse A | 123 Nowhere St. | Toronto, ON P3L 3M0 | Hospital B   | 123 Boul Fake.   | Montreal, QC T4Y 6J8  | 1              | 24 Lbs    | $45.00
04/01/2016 05:30 | 04/01/2016 07:30 | Warehouse A | 123 Nowhere St. | Toronto, ON P3L 3M0 | Hospital A   | 321 Made-up Ave. | Stratford, ON T4P 6H8 | 4              | 160 Lbs   | $145.00

Using the sample data above we would have(for total number of deliveries):
2 Deliveries from Warehouse A to Hospital A
1 Delivery from Warehouse B to Hospital A
1 Delivery from Warehouse A to Hospital B

I know how to cycle through the different worksheets. I also know how to cycle through to the end of the last row, in an unknown number of rows.

I do not know the best way to sort through my data and compare pick-up and delivery locations. ie. I need to be able to look through the data to determine that Warehouse A delivered to Hospital B 7 times in the month of January, 15 times in February...10 times in December etc.. I have no code for this done up, at this point, because I'm having issues visualising how best to approach this.

This must be a VBA solution--I could have this done by hand ages ago but I want an "automated" VBA solution.

Any assistance is appreciated.

WarOrdos
  • 55
  • 9
  • If you had a limited number of "locations" (e.g. you may know that there won't be more than 100 warehouses/hospitals involved) I would allocate each location a number, and create an array that is dimensioned `(100, 100, 12)` where the first dimension represents "from", the second dimension represents "to" and the third dimension represents month. Then just store values into the array as you process each month's sheet. – YowE3K Oct 29 '16 at 17:24
  • That is a sound idea. However, whilst I know locations which are apt to be frequent, technically there could be a limitless number of locations. Indexing in that manner would potentially leave holes open when a new location is in that month's stats. – WarOrdos Oct 29 '16 at 20:10
  • I'm thinking that I need to scan down the delivery location name column and make an array entry for each unique name listed, per month. Then, cross reference that list against repetitions and increment accordingly. That would seem to cover off the "unknown" locations and conducting tally's as well. However, I'm still somewhat fuzzy on the best way to go about this. – WarOrdos Oct 29 '16 at 20:29
  • 1. Load up the first record in the array (has to happen every time as the array is empty. 2. Look at the next delivery name and if same as first record in the array, increment the respective values in the first record, else add a new record to the array. 3. Once at the last row stop checking/loading and output to the summary sheet. – WarOrdos Oct 29 '16 at 20:29
  • Suggestions on the most efficient way to code this are appreciated. When I have something working I'll post here as well. – WarOrdos Oct 29 '16 at 20:30

1 Answers1

1

You could open a recordset with the following SQL statement:

SELECT Year([Date&Time Del]), Month([Date&Time Del]), [PU Loc. Name], [DEL Loc.  Name]
    COUNT(*) AS CountOfDeliveries
FROM [SheetName$]
GROUP BY Year([Date&Time Del]), Month([Date&Time Del]), [PU Loc. Name], [DEL Loc.  Name]

and paste the results into the output worksheet using CopyRecordset.

If you want to group by year and not by month (it's not quite clear from the question), use the following SQL statement:

SELECT Year([Date&Time Del]), [PU Loc. Name], [DEL Loc.  Name]
    COUNT(*) AS CountOfDeliveries
FROM [SheetName$]
GROUP BY Year([Date&Time Del]), [PU Loc. Name], [DEL Loc.  Name]

See here for something similar.

Community
  • 1
  • 1
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136