One tab has two columns. A lists of tasks to be performed based on unique Activity IDs and a column for Charge Numbers.
Each Activity ID can have multiple Charge Numbers in an adjacent cell on its row. The charge numbers are separated by columns in the Charge Number cell. There can be from 1 to 5 charge numbers for each Activity ID.
A second tab has a list of charging, for each month year to date, for each charge number. There is only 1 charging record for each Charge Number.
The charge number in this file is a combination of a department code (9 characters) and the Activity ID Charge Number. The department code is always the same for all records in the Charging tab (file)
Need to search the charging tab and sub total, YTD, all charges for each Activity ID
Column A Column B
Charge Number Activity ID (tasks)
AAMK, ACMK, AEMK, APMK A02C ;note - all charge numbers for a specific Activity ID are contained in 1 cell
AEDA A028
ARHU D02A
TAMK, TCMK, TFMK A029
Column A B C D E F G ... ... M N
Charging File
Full Charge Number Jan Feb Mar April May June July ... ... Dec Total
123456789 AEDA 2,000 500.00 500.00 1,000 4,000 2,000 10,000
123456789 TAMK 1,000 5,500 2,000 6,000 1,500 3,000 19,000
123456789 ....
...
...
Results:
Column A B
Activity ID YTD Total $$
A02C $***X
A028 $***X
D02A $***X