xluser2007
02-08-2009, 04:06 AM
Hi All,
I am re-posting this thread (http://www.mrexcel.com/forum/showthread.php?t=369667) here, as my original post from MrExcel.com has not gathered any replies, and it is easier to post a workbook here at VBAX for this type of problem.
I have a bit of a formula challenge.
Basically in a worksheet UserForm, I have space for the user to enter Pensioner Data for 4 Accounts (2 Pensioners MAX - so can enter 4 accounts for 1 pensioner and none for the other, or 3 for one pensioner and 1 for the other, or 2 for one pensioner and 2 for the other).
In the first image below, we have 2 ficticious pensioners - John Smith and Jane Smith - in this example, they have 2 accounts each.
After setting up the formulae correctly, I would like to have them consolidated, in this case into the 2 accounts, MAX 1 for each pensioner.
I have attached a sample workbook containing the example.
The key rules for CONSOLIDATING are:
1. To search through each of the 4 account ranges and pick out the key names i.e. in this Case there are 2 John Smith's and Jane Smith's, so we will have one of each. If there were 4 John Smith's, then we would consolidate into one account i.e. MAX one account per name.
2. For the commencement date to be the earliest date for that Pensioner.
3. For the Balance at the start of the year, it is simply the sum of balances at the start of the year.
4. As per rule #3, the ending balances should be the sum of the ending balances for that pensioner.
5. For the last field i.e. Segregation of Assets, if the Person has a Single "Y" then this should dominate the "N" value, if all "N"'s then it should be an "N" for that pensioner.
Could anyone please show mw how to construct the formulae to consolidate the pensioners.
I am re-posting this thread (http://www.mrexcel.com/forum/showthread.php?t=369667) here, as my original post from MrExcel.com has not gathered any replies, and it is easier to post a workbook here at VBAX for this type of problem.
I have a bit of a formula challenge.
Basically in a worksheet UserForm, I have space for the user to enter Pensioner Data for 4 Accounts (2 Pensioners MAX - so can enter 4 accounts for 1 pensioner and none for the other, or 3 for one pensioner and 1 for the other, or 2 for one pensioner and 2 for the other).
In the first image below, we have 2 ficticious pensioners - John Smith and Jane Smith - in this example, they have 2 accounts each.
After setting up the formulae correctly, I would like to have them consolidated, in this case into the 2 accounts, MAX 1 for each pensioner.
I have attached a sample workbook containing the example.
The key rules for CONSOLIDATING are:
1. To search through each of the 4 account ranges and pick out the key names i.e. in this Case there are 2 John Smith's and Jane Smith's, so we will have one of each. If there were 4 John Smith's, then we would consolidate into one account i.e. MAX one account per name.
2. For the commencement date to be the earliest date for that Pensioner.
3. For the Balance at the start of the year, it is simply the sum of balances at the start of the year.
4. As per rule #3, the ending balances should be the sum of the ending balances for that pensioner.
5. For the last field i.e. Segregation of Assets, if the Person has a Single "Y" then this should dominate the "N" value, if all "N"'s then it should be an "N" for that pensioner.
Could anyone please show mw how to construct the formulae to consolidate the pensioners.