thelittleredfox
New Member
- Joined
- Dec 15, 2014
- Messages
- 17
Hi, I'm really struggling with this so I hope someone can help me!
My spreadsheet shows all commission payments on individual insurance policies, each of which has a unique number. There is one payment per policy per month.
My excel spreadsheet has two sheets. One sheet, "raw data" contains data copied and pasted from regular reports so I can't insert extra columns for formulae on that sheet. Each line in this sheet represents a payment on a policy, and they appear in date order - there are therefore multiple lines in the sheet for each policy, but only one line per policy within any given month.
On the "raw data" sheet:
column g is individual policy numbers
column a is the date of the payment, given as the day in the month
column m is monthly commission.
Summary sheet
I then have a summary sheet, which is supposed to show, for each month, if a payment has been made on each policy and if so which date it was made on, and how much for. This is the sheet into which I want to insert my formulae.
On my summary sheet:
column a is a list of all policy numbers - each appears only once, one line per policy number
column b,c,d etc. are headed by the months of the year going across the sheet. In the cell above the name of the month, I have put the month number, e.g. 8 for August.
If possible, for each month I want 2 columns. For example for August:
I use vlookup a lot but I don't normally use index and match, which I'm guessing is what I need to use here. I can't work out how to incorporate matching the dates within a month at all. Do I need the MONTH function?
Excel 2010.
My spreadsheet shows all commission payments on individual insurance policies, each of which has a unique number. There is one payment per policy per month.
My excel spreadsheet has two sheets. One sheet, "raw data" contains data copied and pasted from regular reports so I can't insert extra columns for formulae on that sheet. Each line in this sheet represents a payment on a policy, and they appear in date order - there are therefore multiple lines in the sheet for each policy, but only one line per policy within any given month.
On the "raw data" sheet:
column g is individual policy numbers
column a is the date of the payment, given as the day in the month
column m is monthly commission.
Summary sheet
I then have a summary sheet, which is supposed to show, for each month, if a payment has been made on each policy and if so which date it was made on, and how much for. This is the sheet into which I want to insert my formulae.
On my summary sheet:
column a is a list of all policy numbers - each appears only once, one line per policy number
column b,c,d etc. are headed by the months of the year going across the sheet. In the cell above the name of the month, I have put the month number, e.g. 8 for August.
If possible, for each month I want 2 columns. For example for August:
- the first column would be the date commission was paid in that month. If no commission payment appears in the "raw data" sheet, this should be blank.
- the second column would be the amount of commission which was paid that month, if commission was paid. If not, cell should appear blank.
I use vlookup a lot but I don't normally use index and match, which I'm guessing is what I need to use here. I can't work out how to incorporate matching the dates within a month at all. Do I need the MONTH function?
Excel 2010.
