G
Guest
I am trying to automate a manual system used to track and bill plowing and
sanding services. I am looking for a formula which will match the customer
from the Customer History Sheet to the Plowing and Sanding Route Sheet, then
identify the dates containing ‘P’, ‘S’, and ‘N’ in the Plowing and Sanding
Sheet and list these dates in the Customer History Sheet in the respective
column beneath the appropriate Headers; listing the date multiple times
within the category if service was performed more than once on that date.
This is all being done manually at the moment so I can alter the layout of
the Customer History Sheet if that simplifies the process.
Plowing and Sanding Route Sheet contains route order sequence in column A,
customer ID in column B, Last Name in column C, First Name in column D,
Location in column E, Phone in column F, nothing in column G. Columns H
through DP are consecutive dates January 1st through December 31st.
Within the respective cell ‘P’ is entered to indicate plowing, ‘S’ to
indicate sanding, ‘N’ to indicate no charge for service performed. Cells
remain null when there was no activity. If a heavy storm hit it is possible
to have multiple ‘P’ and ‘S’ within the same date-cell.
Customer History Sheet contains Last Name and First Name concatenated in
cell B1, cell, Phone in cell B2, and Customer ID in cell D1. Headers are as
follows: Date Plowed in cell A4, Date Sanded in cell A5, Date N/C in cell
A6, with other billing information in following columns.
I am open to suggestions!
Thanks,
ilmeaz
sanding services. I am looking for a formula which will match the customer
from the Customer History Sheet to the Plowing and Sanding Route Sheet, then
identify the dates containing ‘P’, ‘S’, and ‘N’ in the Plowing and Sanding
Sheet and list these dates in the Customer History Sheet in the respective
column beneath the appropriate Headers; listing the date multiple times
within the category if service was performed more than once on that date.
This is all being done manually at the moment so I can alter the layout of
the Customer History Sheet if that simplifies the process.
Plowing and Sanding Route Sheet contains route order sequence in column A,
customer ID in column B, Last Name in column C, First Name in column D,
Location in column E, Phone in column F, nothing in column G. Columns H
through DP are consecutive dates January 1st through December 31st.
Within the respective cell ‘P’ is entered to indicate plowing, ‘S’ to
indicate sanding, ‘N’ to indicate no charge for service performed. Cells
remain null when there was no activity. If a heavy storm hit it is possible
to have multiple ‘P’ and ‘S’ within the same date-cell.
Customer History Sheet contains Last Name and First Name concatenated in
cell B1, cell, Phone in cell B2, and Customer ID in cell D1. Headers are as
follows: Date Plowed in cell A4, Date Sanded in cell A5, Date N/C in cell
A6, with other billing information in following columns.
I am open to suggestions!
Thanks,
ilmeaz