vikki123us
New Member
- Joined
- Jan 22, 2013
- Messages
- 5
I've attached a sample spreadsheet. But basically what I have to do is get a total by client id for the amount of invoices on a monthly basis and populate them into the correct tab and column. The problem is that a client can have multiple lines per month and I just need a total by client id by month. Invoices are produced almost on a daily basis through out the month. I update a master list at the end of the month..
any idea how or what I should do?
Master List:
<tbody>
</tbody>
Invoiced Annually Tab:
<tbody>
</tbody>Invoiced Quarterly TAb:
<tbody>
</tbody>
any idea how or what I should do?
Master List:
Client Id | Segment | Client Name | Plan Id | Plan Name | Internal/External | Plan Type | Invoice Type | Bill Cycle | Period Ending | Billing Date | Billing Month | Invoice Number | Rev | Invoice Status | Approval Status | Level | Level Code | Description | Fee Category (If Appl.) | Fee Service (If Appl.) | Payment Method | Activity Type | Invoice Amount | Charge Quantity | Invoice Description | Invoice Comment | Source | Stat Period | Stat Range |
4515 | a | Jane Smith | 220 | 01/01/2013 | 1 | 0 | -200 | ||||||||||||||||||||||
4515 | a | Jane Smith | 121 | 01/01/2013 | 1 | 0 | -200 | ||||||||||||||||||||||
7845 | a | John Doe | 25631 | 01/02/2013 | 2 | 0 | -120 | ||||||||||||||||||||||
7845 | a | John Doe | 45158 | 01/02/2013 | 2 | 0 | -120 | ||||||||||||||||||||||
2159 | a | Help Me | 48935 | 01/03/2013 | 3 | 0 | -50 | ||||||||||||||||||||||
2518 | a | Client Name A | 59874 | 01/04/2013 | 4 | 0 | -650 | ||||||||||||||||||||||
2518 | a | Client Name A | 5462 | 01/04/2013 | 4 | 0 | -650 | ||||||||||||||||||||||
2518 | a | Client Name A | 124 | 01/04/2013 | 4 | 0 | -650 | ||||||||||||||||||||||
89463 | a | Client Name B | 2356 | 01/05/2013 | 5 | 1 | -125 | ||||||||||||||||||||||
89463 | a | Client Name B | 2568 | 01/05/2013 | 5 | 1 | -125 | ||||||||||||||||||||||
74523 | b | Client Name C | 28465 | 01/06/2013 | 6 | 2 | -800 | ||||||||||||||||||||||
5216 | b | Joe Smith | 596 | 01/07/2013 | 7 | 0 | -450 | ||||||||||||||||||||||
5216 | b | Joe Smith | 251 | 01/07/2013 | 7 | 0 | -450 | ||||||||||||||||||||||
1204 | b | Adam Tree | 54635 | 01/08/2013 | 8 | 0 | -630 | ||||||||||||||||||||||
8596 | c | Jane Doe | 1234 | 01/09/2013 | 9 | 0 | -120 | ||||||||||||||||||||||
44845 | c | I don't know | 1 | 01/10/2013 | 10 | 1 | -500 | ||||||||||||||||||||||
95632 | c | new client | 784 | 01/11/2013 | 11 | 1 | -800 | ||||||||||||||||||||||
87542 | a | needs to be added | 5941 | 01/25/2013 | 12 | 1 | -100 |
<tbody>
</tbody>
Invoiced Annually Tab:
Client ID | Segment | Plan ID | Bill Cycle | Type | Frequency | Client Name | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
5216 | Joe Smith | |||||||||||||||||
1204 | Adam Tree | |||||||||||||||||
8596 | Jane Doe | |||||||||||||||||
<tbody>
</tbody>
Client ID | Segment | Plan ID | Bill Cycle | Type | Frequency | Client Name | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
2518 | Client Name A | |||||||||||||||||
89463 | Client Name B | |||||||||||||||||
74523 | Client Name C | |||||||||||||||||
<tbody>
</tbody>