View Full Version : CONSOLIDATION Formula Challenge

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.

02-08-2009, 04:07 AM
Attaching target image, for previous thread.

02-08-2009, 04:08 AM
Attaching sample workbook.

02-08-2009, 04:21 AM
If a formula solution is too difficult, I am open to a VBA solution also.

thanks and regards

02-08-2009, 02:21 PM
Any thoughts on solving this VBAXers?

Any help is sincerely appreciated.

02-08-2009, 05:07 PM
Ok, my first mode of attack after thinking about it, is to feed in the 4 account names and filter out a unique list of names.

Then the rest is just doing LOOKUPS from those ranges.

For this example, I wish to filter out the 2 unique account names "John Smith" and Jane Smith".

I tried to use the following code, adapting from here (http://spreadsheetpage.com/index.php/site/eee/issue_no_16_january_31_2000/).

Option Explicit

Sub BuildUnique1()
Dim vArr As Variant
Dim vArr1 As Variant
Set RNG = Range("Section6_a", "Section6_f", "Section6_k", "Section6_p")
vArr = Application.Transpose(RNG)
ShellSort vArr
ReDim vArr1(1 To 1)
vArr1(1) = vArr(1)
j = 1
For i = LBound(vArr, 1) + 1 To UBound(vArr, 1)
If vArr(i) <> vArr1(j) Then
j = j + 1
ReDim Preserve vArr1(1 To j)
vArr1(j) = vArr(i)
End If
End Sub

Sub ShellSort(list As Variant, Optional ByVal LowIndex As Variant, Optional HiIndex As Variant)
'Translation of Shell's Sort as described in
' "Numerical Recipes in C", 2nd edition, Press et al.
'For large arrays, consider Quicksort. This algorithm is at least
'as good up to about 100 or so elements. But with 500 randomized
'elements it is about 27% slower than QSort, and looks
'increasingly worse as the array size increases.

'Dec 17, '98 - David J. Braden

Dim i As Long, j As Long, inc As Long
Dim var As Variant

If IsMissing(LowIndex) Then LowIndex = LBound(list)
If IsMissing(HiIndex) Then HiIndex = UBound(list)
inc = 1
Do While inc <= HiIndex - LowIndex: inc = 3 * inc + 1: Loop
inc = inc \ 3
For i = LowIndex + inc To HiIndex
var = list(i)
j = i
Do While list(j - inc) > var
list(j) = list(j - inc)
j = j - inc
If j <= inc Then Exit Do
list(j) = var
Loop While inc > 1
End Sub

I would like to dump the 2 unique names (in this example), in E3 and E19 respectively.

The sample workbook to apply the code is attached below.

At the moment, it doesn't like the line:

Set RNG = Range("Section6_a", "Section6_f", "Section6_k", "Section6_p")

and says it is "a wrong number of arguments or invalid property assignment".

Could anyone please help me in correcting this as a start.

02-08-2009, 05:08 PM
workbook attached for previous post.

02-09-2009, 03:37 AM
Any thoughts on getting the first step to work VBAXer's?

What I am tryying to do is take the 4 account names and derive the (MAX) 2 unique pensioner names, and then put each unique name in the target cells, as shown in the workbook.

If this works, then it might be easier to extract the other info from the 4 accounts.

02-09-2009, 04:08 PM
Just so you don't feel lonely in this thread, I'll look at this tomorrow!

02-09-2009, 06:12 PM
Just so you don't feel lonely in this thread, I'll look at this tomorrow!

Thanks md, you are a real trooper :)!