finding out where the money spread...

[RCMetrics]

Hi,

this is naturally through large amount of data... but to make it simple:

I've got $100 debit through a Hardware Account and have several other "Type" of account.

now I also have employee spending accounts:

Acct. Emp. 1 = $35
Acct. Emp. 2 = $25
Acct. Emp. 3 = $45
Acct. Emp. 4 = $65

This may not be an exact science, but I would like to get possible combination of spending account that makes up to a 100$ to pin point where the $100 was probably spent and help the investigation process.

Ideally, the response would come back with Acct.1 (35) and Acct. 4 (65) = $100

But in the end, what combinations through all my employee's accounts makes up $100 ?

Is this at all possible through some formulas? The goal here is to find where numbers could have been spread if we are if down in one area. Again, this wouldn't give us the final verdict... but would help looking into the proper section of (department, Employee etc...)


Thanks

 

[MrKowz]

Here is a bit of code I came across several months back. It will let you take a list of values, define a target value, and how many solutions you want, and it will return a list of all combinations that exactly match the target value:

Option Explicit
 
 
Function RealEqual(A, B, Epsilon As Double)
    RealEqual = Abs(A - B) <= Epsilon
End Function
 
 
Function ExtendRslt(CurrRslt, NewVal, Separator)
    If CurrRslt = "" Then ExtendRslt = NewVal _
    Else ExtendRslt = CurrRslt & Separator & NewVal
End Function
 
 
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
        ByVal CurrIdx As Integer, _
        ByVal CurrTotal, ByVal Epsilon As Double, _
        ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
    Dim i As Integer
    For i = CurrIdx To UBound(InArr)
        If RealEqual(CurrTotal + InArr(i), TargetVal, Epsilon) Then
            Rslt(UBound(Rslt)) = (CurrTotal + InArr(i)) _
                & Separator & Format(Now(), "hh:mm:ss") _
                & Separator & ExtendRslt(CurrRslt, i, Separator)
            If MaxSoln = 0 Then
                If UBound(Rslt) Mod 100 = 0 Then Debug.Print UBound(Rslt) & "=" & Rslt(UBound(Rslt))
            Else
                If UBound(Rslt) >= MaxSoln Then Exit Sub
            End If
            ReDim Preserve Rslt(UBound(Rslt) + 1)
        ElseIf CurrTotal + InArr(i) > TargetVal + Epsilon Then
        ElseIf CurrIdx < UBound(InArr) Then
            recursiveMatch MaxSoln, TargetVal, InArr(), i + 1, _
                CurrTotal + InArr(i), Epsilon, Rslt(), _
                ExtendRslt(CurrRslt, i, Separator), _
                Separator
            If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
        Else
            'we've run out of possible elements and we still don 't have a match
        End If
   Next i
End Sub
Function ArrLen(arr()) As Integer
    On Error Resume Next
    ArrLen = UBound(arr) - LBound(arr) + 1
End Function
 
 
Sub startSearch()
    'The selection should be a single contiguous range in a single column.
    'The first cell indicates the number of solutions wanted.  Specify zero for all.
    'The 2nd cell is the target value.
    'The rest of the cells are the values available for matching.
    'The output is in the column adjacent to the one containing the input data.
    Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer
    StartTime = Now()
    MaxSoln = Selection.Cells(1).Value
    TargetVal = Selection.Cells(2).Value
    InArr = Application.WorksheetFunction.Transpose( _
        Selection.Offset(2, 0).Resize(Selection.rows.Count - 2).Value)
    ReDim Rslt(0)
    recursiveMatch MaxSoln, TargetVal, InArr, LBound(InArr), 0, 0.00000001, _
        Rslt, "", ", "
    Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
    ReDim Preserve Rslt(UBound(Rslt) + 1)
    Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
    Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
    Application.WorksheetFunction.Transpose(Rslt)
End Sub

You will need to initially set up your sheet like this (when you make # solutions = 0, that will return ALL possible solutions):
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD># Solutions Want</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Target Value</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Acct. Emp. 1</TD><TD style="TEXT-ALIGN: right">$35 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Acct. Emp. 2</TD><TD style="TEXT-ALIGN: right">$25 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Acct. Emp. 3</TD><TD style="TEXT-ALIGN: right">$45 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Acct. Emp. 4</TD><TD style="TEXT-ALIGN: right">$65 </TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>



And select cells B1:B6, then run "StartSearch". After it runs, it will look similar to this:
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD># Solutions Want</TD><TD style="TEXT-ALIGN: right">0</TD><TD>100, 09:00:05, 1, 4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Target Value</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">9:00:05</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Acct. Emp. 1</TD><TD style="TEXT-ALIGN: right">$35 </TD><TD style="TEXT-ALIGN: right">9:00:05</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Acct. Emp. 2</TD><TD style="TEXT-ALIGN: right">$25 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Acct. Emp. 3</TD><TD style="TEXT-ALIGN: right">$45 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Acct. Emp. 4</TD><TD style="TEXT-ALIGN: right">$65 </TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>



The value in C1, "100, 09:00:05, 1, 4", represents that it found a total value of 100, at time 09:00:05, and the rows in your data it found the combination is rows 1 and 4. Note that this is the rows in your DATA ARRAY, not the rows of the spreadsheet.

To give an example of when there are 2 or more matches:
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD># Solutions Want</TD><TD style="TEXT-ALIGN: right">0</TD><TD>100, 09:02:33, 1, 4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Target Value</TD><TD style="TEXT-ALIGN: right">100</TD><TD>100, 09:02:33, 2, 5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Acct. Emp. 1</TD><TD style="TEXT-ALIGN: right">$35 </TD><TD style="TEXT-ALIGN: right">9:02:33</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Acct. Emp. 2</TD><TD style="TEXT-ALIGN: right">$25 </TD><TD style="TEXT-ALIGN: right">9:02:33</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Acct. Emp. 3</TD><TD style="TEXT-ALIGN: right">$45 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Acct. Emp. 4</TD><TD style="TEXT-ALIGN: right">$65 </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Acct. Emp. 5</TD><TD style="TEXT-ALIGN: right">$75 </TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

 

[RCMetrics]

thanks, that's actually pretty good...just tested it and works pretty well, I'm impressed.

Now, this would work fine for exact value, but what if there's some rounding happening.. anyway to give the "Target Value" a range of say $99.98 to $100.02 rather than an exact 100?

Either way this is a great start and can definitely leverage off this solution... but if there's any option to give it a range... even better.

Thanks again.

 

[MrKowz]

It would honestly be easier to run the macro if you want to look at that small of a range than to recode it. I barely understand how this macro works (I did not program it), and it would take a solid amount of coding to let it accomodate a range of values.