find sum in list of of numbers

Hello,

I have a list of numbers in a column and I need to find which numbers
when summed together equal a figure.  I have a list of invoice amounts
that I need to match up with payments (the payments are always made for
several invoices so I need to come up with sums of several invoices to
get to this payment amount).

An example would be I have this in the following section (A1:A10):
 $17,213.82
 $4,563.02
 $85,693.42
 $1,166.01
 $725.90
 $580.09
 $2,243.75
 $240.16
 $207.70
 $725.90

I need to find which combination of these figures would sum $1,173.76.

Thanks in Advance,
Dza the troubled accountant

0
dvpetta (9)
1/4/2006 4:27:14 PM
excel 39879 articles. 2 followers. Follow

25 Replies
593 Views

Similar Articles

[PageSpeed] 34

Here is some code. Note that you need to create references to a couple of 
librarys in order tom make this code work (In VBE select Tools -> 
References). 

  'Microsoft Scripting Runtime
  'Microsoft VBScript Regular Expressions 1.0 or higher

This code should be placed in a standard module...

Option Explicit
' Original solution created by
' Harlan Grove

Public Sub FindSums()
  'This *REQUIRES* VBAProject references to
  'Microsoft Scripting Runtime
  'Microsoft VBScript Regular Expressions 1.0 or higher

  Const TOL As Double = 0.0001  'modify as needed
  Dim c As Variant

  Dim j As Long, k As Long, n As Long, p As Boolean
  Dim s As String, t As Double, u As Double
  Dim v As Variant, x As Variant, y As Variant
  Dim dc1 As New Dictionary, dc2 As New Dictionary
  Dim dcn As Dictionary, dco As Dictionary
  Dim re As New RegExp
  Dim wks As Worksheet
  Application.EnableCancelKey = xlErrorHandler

  re.Global = True
  re.IgnoreCase = True

  On Error Resume Next

  Set wks = ActiveSheet
  Set x = Intersect(Selection, wks.UsedRange)

  If x Is Nothing Then
    Err.Clear
    Exit Sub
  End If

  y = Application.InputBox( _
    Prompt:="Enter target value:", _
    Title:="Find Sums", _
    Default:="", _
    Type:=1 _
  )

  If VarType(y) = vbBoolean Then
    Exit Sub
  Else
    t = y
  End If

  On Error GoTo 0

  Set dco = dc1
  Set dcn = dc2

  Call recsoln

  For Each y In x.Value2
    If VarType(y) = vbDouble Then
      If Abs(t - y) < TOL Then
        recsoln "+" & Format(y)

      ElseIf dco.Exists(y) Then
        dco(y) = dco(y) + 1

      ElseIf y < t - TOL Then
        dco.Add Key:=y, Item:=1

        c = CDec(c + 1)
        Application.StatusBar = "[1] " & Format(c)

      End If

    End If
  Next y

  n = dco.Count

  ReDim v(1 To n, 1 To 3)

  For k = 1 To n
    v(k, 1) = dco.Keys(k - 1)
    v(k, 2) = dco.Items(k - 1)
  Next k

  qsortd v, 1, n

  For k = n To 1 Step -1
    v(k, 3) = v(k, 1) * v(k, 2) + v(IIf(k = n, n, k + 1), 3)
    If v(k, 3) > t Then dcn.Add Key:="+" & _
      Format(v(k, 1)), Item:=v(k, 1)
  Next k

  On Error GoTo CleanUp
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

  For k = 2 To n
    dco.RemoveAll
    swapo dco, dcn

    For Each y In dco.Keys
      p = False

      For j = 1 To n
        If v(j, 3) < t - dco(y) - TOL Then Exit For
        x = v(j, 1)
        s = "+" & Format(x)
        If Right(y, Len(s)) = s Then p = True
        If p Then
          re.Pattern = "\" & s & "(?=(\+|$))"
          If re.Execute(y).Count < v(j, 2) Then
            u = dco(y) + x
            If Abs(t - u) < TOL Then
              recsoln y & s
            ElseIf u < t - TOL Then
              dcn.Add Key:=y & s, Item:=u
              c = CDec(c + 1)
              Application.StatusBar = "[" & Format(k) & "] " & _
                  Format(c)
            End If
          End If
        End If
      Next j
    Next y

    If dcn.Count = 0 Then Exit For
  Next k

  If (recsoln() = 0) Then _
    MsgBox Prompt:="All combinations exhausted.", _
      Title:="No Solution"

CleanUp:
  If Err = 18 Then
    If MsgBox("Do you want to stop searching?", vbYesNo, "Quit?") = vbYes Then
      Application.StatusBar = False
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
      Application.StatusBar = False
      End
    Else
      Resume
    End If
  Else
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
  End If
End Sub

Private Function recsoln(Optional s As String)
  Const OUTPUTWSN As String = "Findsums Solutions"  'modify to taste

  Static r As Range
  Dim ws As Worksheet

  If s = "" And r Is Nothing Then
    If Not SheetExists(OUTPUTWSN, ActiveWorkbook) Then
      Application.ScreenUpdating = False
      Worksheets.Add Before:=ActiveSheet
      Set ws = ActiveSheet
      ws.Name = OUTPUTWSN
      ws.Cells.NumberFormat = "#,##0.00"
      Set r = ws.Range("A2")
    Else
      Set ws = Sheets(OUTPUTWSN)
      ws.Cells.Clear
      ws.Cells.NumberFormat = "#,##0.00"
      Set r = ws.Range("A2")
    End If
    recsoln = 0
  ElseIf s = "" Then
    recsoln = r.Row - 1
    Set r = Nothing
  Else
    Call PostAnswers(s, r)
    Set r = r.Offset(1, 0)
    recsoln = r.Row - 1
  End If
End Function

Private Sub qsortd(v As Variant, lft As Long, rgt As Long)
  'ad hoc quicksort subroutine
  'translated from Aho, Weinberger & Kernighan,
  '"The Awk Programming Language", page 161

  Dim j As Long, pvt As Long

  If (lft >= rgt) Then Exit Sub
  swap2 v, lft, lft + Int((rgt - lft + 1) * Rnd)
  pvt = lft
  For j = lft + 1 To rgt
    If v(j, 1) > v(lft, 1) Then
      pvt = pvt + 1
      swap2 v, pvt, j
    End If
  Next j

  swap2 v, lft, pvt

  qsortd v, lft, pvt - 1
  qsortd v, pvt + 1, rgt
End Sub

Private Sub swap2(v As Variant, i As Long, j As Long)
  'modified version of the swap procedure from
  'translated from Aho, Weinberger & Kernighan,
  '"The Awk Programming Language", page 161

  Dim t As Variant, k As Long

  For k = LBound(v, 2) To UBound(v, 2)
    t = v(i, k)
    v(i, k) = v(j, k)
    v(j, k) = t
  Next k
End Sub

Private Sub swapo(a As Object, b As Object)
  Dim t As Object

  Set t = a
  Set a = b
  Set b = t
End Sub

Private Sub PostAnswers(ByVal strValue As String, ByVal rng As Range)
    Dim aryCSVValues    As Variant
    Dim intCounter      As Integer
    
    aryCSVValues = Split(Mid$(strValue, 2, Len(strValue)), "+")
    For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
         rng.Value = aryCSVValues(intCounter)
         Set rng = rng.Offset(0, 1)
    Next intCounter
End Sub
-- 
HTH...

Jim Thomlinson


"dvpetta@gmail.com" wrote:

> Hello,
> 
> I have a list of numbers in a column and I need to find which numbers
> when summed together equal a figure.  I have a list of invoice amounts
> that I need to match up with payments (the payments are always made for
> several invoices so I need to come up with sums of several invoices to
> get to this payment amount).
> 
> An example would be I have this in the following section (A1:A10):
>  $17,213.82
>  $4,563.02
>  $85,693.42
>  $1,166.01
>  $725.90
>  $580.09
>  $2,243.75
>  $240.16
>  $207.70
>  $725.90
> 
> I need to find which combination of these figures would sum $1,173.76.
> 
> Thanks in Advance,
> Dza the troubled accountant
> 
> 
0
1/4/2006 5:10:04 PM
The answer is:
$725.90
$240.16
$207.70

I've written a program that applies a brute force approach to the task-
it checks every possible combination of the "pool" of numbers to arrive
at the target total.  The brute force idea works for comparatively
small pools, but since the number of possible combinations doubles with
each additional pool member the processing time increases
commensurately.  One poster to this newsgroup wanted to process a list
of 100 numbers, which amounts to
1,267,650,600,228,230,000,000,000,000,000 possible combinations and
would require the resources of a major government (or maybe just the
NSA) to process.

How many of these do you have?  I don't mind doing a few for you.

0
CycleZen (674)
1/4/2006 5:13:00 PM
dvpetta@gmail.com wrote:
> Hello,
> 
> I have a list of numbers in a column and I need to find which numbers
> when summed together equal a figure.  I have a list of invoice amounts
> that I need to match up with payments (the payments are always made for
> several invoices so I need to come up with sums of several invoices to
> get to this payment amount).
> 
> An example would be I have this in the following section (A1:A10):
>  $17,213.82
>  $4,563.02
>  $85,693.42
>  $1,166.01
>  $725.90
>  $580.09
>  $2,243.75
>  $240.16
>  $207.70
>  $725.90
> 
> I need to find which combination of these figures would sum $1,173.76.
> 
> Thanks in Advance,
> Dza the troubled accountant
> 

-----------------------------------

I don't believe there is a simple, closed form solution to this problem.  What
you have to do is to exhaustively try all possible combinations to see which one
(or *ones*) add up to what you want.  This is possible to do with small problems
like the example you've shown, but if there are a "large" number of entries it
will take computer time in excess of the age of the universe to calculate.  With
100 entries for example, the number of combinations you'd have to test 1.27
times ten to the 30th power -- a *really* big number.  With 20 entries you'd
"only" have about one million combinations to check.

What I would do is add an extra column of only 0 and 1 vales which represents a
binary word in aggregate.  Then multiply that column by your dollar values and
sum them.  This gives you the what that particular combination adds up to.  Then
you need to increment the binary word by one and do it again ... and again.
Until you've tested all combinations.

You're going to need a VBA macro to make this work.  I don't think you can do it
with simple formulas.

Good luck...

Bill
0
wylie (108)
1/4/2006 5:23:09 PM
Have you tried using Excel Solver

First a little prep work....

A1:A1 (your list of values)
B1:B10 (leave blank)
C1: =A1*B1
(copy that fomula down through C10

C11: =SUM(C1:C10)

Now to use Solver....
Tools>Solver
Set Cell: C11
Equal to the Value of: 1173.76
By Changing Cells: B1:B10
Subject to the Constraints....
(click the add button and constrain B1:B10 to Binary)
Click [OK] 
Click [Solve]

Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a 
combination that sums to 1,173.76

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dave O" wrote:

> The answer is:
> $725.90
> $240.16
> $207.70
> 
> I've written a program that applies a brute force approach to the task-
> it checks every possible combination of the "pool" of numbers to arrive
> at the target total.  The brute force idea works for comparatively
> small pools, but since the number of possible combinations doubles with
> each additional pool member the processing time increases
> commensurately.  One poster to this newsgroup wanted to process a list
> of 100 numbers, which amounts to
> 1,267,650,600,228,230,000,000,000,000,000 possible combinations and
> would require the resources of a major government (or maybe just the
> NSA) to process.
> 
> How many of these do you have?  I don't mind doing a few for you.
> 
> 
0
1/4/2006 5:32:03 PM
Another method is by using solver like in this example

http://tinyurl.com/9suck

same constraints with larger numbers




-- 

Regards,

Peo Sjoblom

"Dave O" <CycleZen@yahoo.com> wrote in message
news:1136394780.370670.160890@g47g2000cwa.googlegroups.com...
> The answer is:
> $725.90
> $240.16
> $207.70
>
> I've written a program that applies a brute force approach to the task-
> it checks every possible combination of the "pool" of numbers to arrive
> at the target total.  The brute force idea works for comparatively
> small pools, but since the number of possible combinations doubles with
> each additional pool member the processing time increases
> commensurately.  One poster to this newsgroup wanted to process a list
> of 100 numbers, which amounts to
> 1,267,650,600,228,230,000,000,000,000,000 possible combinations and
> would require the resources of a major government (or maybe just the
> NSA) to process.
>
> How many of these do you have?  I don't mind doing a few for you.
>


0
terre081 (3244)
1/4/2006 5:33:19 PM
I do like your Solver approach -- I hadn't thought of that.  Given that this is
an Accounting problem though, how would one get Solver to identify multiple
solutions to the problem when they exist?  If you're trying to match invoices
you'd like to know that you're matching them correctly -- not just *possibly*
correctly.  Which requires a person to stare at *all* the various possible
solutions and decide which one is most likely given some knowledge of the
customers involved and what they've ordered in the past, etc.

For example, take the list of 10 values that Dza provided and use them all twice
to make 20 entries.  Now there are 8 valid solutions, but Solver only seems to
find one and stops.

Personally, I think you need VBA for this problem but I'm open to education...

Bill
----------------------------------
Ron Coderre wrote:
> Have you tried using Excel Solver
> 
> First a little prep work....
> 
> A1:A1 (your list of values)
> B1:B10 (leave blank)
> C1: =A1*B1
> (copy that fomula down through C10
> 
> C11: =SUM(C1:C10)
> 
> Now to use Solver....
> Tools>Solver
> Set Cell: C11
> Equal to the Value of: 1173.76
> By Changing Cells: B1:B10
> Subject to the Constraints....
> (click the add button and constrain B1:B10 to Binary)
> Click [OK] 
> Click [Solve]
> 
> Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a 
> combination that sums to 1,173.76
> 
> Does that help?
> 
> ***********
> Regards,
> Ron
> 
> XL2002, WinXP-Pro
> 
> 
> "Dave O" wrote:
> 
> 
>>The answer is:
>>$725.90
>>$240.16
>>$207.70
>>
>>I've written a program that applies a brute force approach to the task-
>>it checks every possible combination of the "pool" of numbers to arrive
>>at the target total.  The brute force idea works for comparatively
>>small pools, but since the number of possible combinations doubles with
>>each additional pool member the processing time increases
>>commensurately.  One poster to this newsgroup wanted to process a list
>>of 100 numbers, which amounts to
>>1,267,650,600,228,230,000,000,000,000,000 possible combinations and
>>would require the resources of a major government (or maybe just the
>>NSA) to process.
>>
>>How many of these do you have?  I don't mind doing a few for you.
>>
>>

0
wylie (108)
1/4/2006 5:55:45 PM
Bill makes a good point- I wrote my original program to find "the"
solution and then stop, but one day on a whim I allowed it to cycle
through the rest of the combinations and found multiple answers to the
problem.  Since then re-wrote my program to show all possible
solutions, and frequently find more than one correct answer to the
problem.

Still haven't heard from the OP yet!

0
CycleZen (674)
1/4/2006 6:12:12 PM
I am with you on the brute force requirement, but there are a couple of 
tricks to minimize the permiutations and combinations. By sorting the list of 
input values you can determine to stop testing certain combinations knowing 
that certain solutions can not be possible because they are going to be too 
large. That is where the code that I posted is very good. I had some other 
code that did almost exactly what you were suggesting but it was far slower. 
From what I have seen Harlan's code is hard to beat. That being said the list 
you are searching should be at most 25 or 30 entries.
-- 
HTH...

Jim Thomlinson


"Bill Martin" wrote:

> dvpetta@gmail.com wrote:
> > Hello,
> > 
> > I have a list of numbers in a column and I need to find which numbers
> > when summed together equal a figure.  I have a list of invoice amounts
> > that I need to match up with payments (the payments are always made for
> > several invoices so I need to come up with sums of several invoices to
> > get to this payment amount).
> > 
> > An example would be I have this in the following section (A1:A10):
> >  $17,213.82
> >  $4,563.02
> >  $85,693.42
> >  $1,166.01
> >  $725.90
> >  $580.09
> >  $2,243.75
> >  $240.16
> >  $207.70
> >  $725.90
> > 
> > I need to find which combination of these figures would sum $1,173.76.
> > 
> > Thanks in Advance,
> > Dza the troubled accountant
> > 
> 
> -----------------------------------
> 
> I don't believe there is a simple, closed form solution to this problem.  What
> you have to do is to exhaustively try all possible combinations to see which one
> (or *ones*) add up to what you want.  This is possible to do with small problems
> like the example you've shown, but if there are a "large" number of entries it
> will take computer time in excess of the age of the universe to calculate.  With
> 100 entries for example, the number of combinations you'd have to test 1.27
> times ten to the 30th power -- a *really* big number.  With 20 entries you'd
> "only" have about one million combinations to check.
> 
> What I would do is add an extra column of only 0 and 1 vales which represents a
> binary word in aggregate.  Then multiply that column by your dollar values and
> sum them.  This gives you the what that particular combination adds up to.  Then
> you need to increment the binary word by one and do it again ... and again.
> Until you've tested all combinations.
> 
> You're going to need a VBA macro to make this work.  I don't think you can do it
> with simple formulas.
> 
> Good luck...
> 
> Bill
> 
0
1/4/2006 6:13:03 PM
Solver isn't a panacea....It's just a nice shortcut for relatively simple 
situations without having to find or write code.  However, if solver finds 
one acceptable solution....couldn't we just create another "flag" field to 
prevent the same value from being used more than once?  

Regarding professional accounting/financial environments, I would hope that 
proper internal controls would prevent the situation where a large number of 
invoices/checks/whatever would have to be matched (trial and error) against 
an amount. Of course there's always the customer who sends a massive check 
paying some unknown combination of invoices. Consequently, for those 
instances, a phone call to the payee should clear up the confusion 
definitively. You wouldn't want to just guess, right?

If a large, multi-solution, iteratave approach cannot be avoided 
though....You're right, a vba program would be the way to go.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Bill Martin" wrote:

> I do like your Solver approach -- I hadn't thought of that.  Given that this is
> an Accounting problem though, how would one get Solver to identify multiple
> solutions to the problem when they exist?  If you're trying to match invoices
> you'd like to know that you're matching them correctly -- not just *possibly*
> correctly.  Which requires a person to stare at *all* the various possible
> solutions and decide which one is most likely given some knowledge of the
> customers involved and what they've ordered in the past, etc.
> 
> For example, take the list of 10 values that Dza provided and use them all twice
> to make 20 entries.  Now there are 8 valid solutions, but Solver only seems to
> find one and stops.
> 
> Personally, I think you need VBA for this problem but I'm open to education...
> 
> Bill
> ----------------------------------
> Ron Coderre wrote:
> > Have you tried using Excel Solver
> > 
> > First a little prep work....
> > 
> > A1:A1 (your list of values)
> > B1:B10 (leave blank)
> > C1: =A1*B1
> > (copy that fomula down through C10
> > 
> > C11: =SUM(C1:C10)
> > 
> > Now to use Solver....
> > Tools>Solver
> > Set Cell: C11
> > Equal to the Value of: 1173.76
> > By Changing Cells: B1:B10
> > Subject to the Constraints....
> > (click the add button and constrain B1:B10 to Binary)
> > Click [OK] 
> > Click [Solve]
> > 
> > Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a 
> > combination that sums to 1,173.76
> > 
> > Does that help?
> > 
> > ***********
> > Regards,
> > Ron
> > 
> > XL2002, WinXP-Pro
> > 
> > 
> > "Dave O" wrote:
> > 
> > 
> >>The answer is:
> >>$725.90
> >>$240.16
> >>$207.70
> >>
> >>I've written a program that applies a brute force approach to the task-
> >>it checks every possible combination of the "pool" of numbers to arrive
> >>at the target total.  The brute force idea works for comparatively
> >>small pools, but since the number of possible combinations doubles with
> >>each additional pool member the processing time increases
> >>commensurately.  One poster to this newsgroup wanted to process a list
> >>of 100 numbers, which amounts to
> >>1,267,650,600,228,230,000,000,000,000,000 possible combinations and
> >>would require the resources of a major government (or maybe just the
> >>NSA) to process.
> >>
> >>How many of these do you have?  I don't mind doing a few for you.
> >>
> >>
> 
> 
0
1/4/2006 6:18:03 PM
They do presumably have controls.  And I don't think people routinely match
invoices like this.  However, contols fail, data gets lost, people do stupid
things.  And them somehow you've got to clean up the mess.

Bill
------------------------
Ron Coderre wrote:
> ...snip...
> Regarding professional accounting/financial environments, I would hope that 
> proper internal controls would prevent the situation where a large number of 
> invoices/checks/whatever would have to be matched (trial and error) against 
> an amount.
>
0
wylie (108)
1/4/2006 6:45:42 PM
You're right, Jim, the OP could reduce his solution space by
disregarding the numbers greater than his "target" number.  In an
accounting environment, however, debits and credits (positive as well
as negative) may need to be considered- the negative numbers may react
with the positive larger numbers to arrive at the correct solution.

0
CycleZen (674)
1/4/2006 6:46:39 PM
I do agree that Harlan's code looks good.  I haven't tried to compile and run
it, but it looks like a good approach.

Bill
------------------------------
Jim Thomlinson wrote:
> I am with you on the brute force requirement, but there are a couple of 
> tricks to minimize the permiutations and combinations. By sorting the list of 
> input values you can determine to stop testing certain combinations knowing 
> that certain solutions can not be possible because they are going to be too 
> large. That is where the code that I posted is very good. I had some other 
> code that did almost exactly what you were suggesting but it was far slower. 
> From what I have seen Harlan's code is hard to beat. That being said the list 
> you are searching should be at most 25 or 30 entries.
0
wylie (108)
1/4/2006 6:52:26 PM
Use Google's  group search:  http://groups.google.com/advanced_group_search

, look in Groups *excel*, with all of the words Add up numbers, Author 
Harlan (yes, indeed, Harlan Grove)
and you'll find a discussion and very advanced solutions about this subject

-- 
Kind regards,

Niek Otten

"Dave O" <CycleZen@yahoo.com> wrote in message 
news:1136400399.116848.41620@g44g2000cwa.googlegroups.com...
> You're right, Jim, the OP could reduce his solution space by
> disregarding the numbers greater than his "target" number.  In an
> accounting environment, however, debits and credits (positive as well
> as negative) may need to be considered- the negative numbers may react
> with the positive larger numbers to arrive at the correct solution.
> 


0
nicolaus (2022)
1/4/2006 6:52:27 PM
Ron Coderre wrote...
>Solver isn't a panacea....It's just a nice shortcut for relatively simple
>situations without having to find or write code.  However, if solver finds
>one acceptable solution....couldn't we just create another "flag" field to
>prevent the same value from being used more than once?

Perhaps. How would you do that since it's not one value but one
combination of values (OK, a vector of 1s and 0s that could be
considered a single vector value in {0,1}^N) that'd need to be
excluded. As I see it, you'd need to use a kludge like SUMPRODUCT of
the vector of 1s and 0s against 2^(ROW(INDIRECT("1:"&N))-1) to produce
unique identifiers for each solution, save them in a list, then use a
COUNTIF = 0 expression on that list with criteria equal to the current
SUMPRODUCT value. And you'd need to automate storing the idenifiers for
previous solutions, so VBA is unavoidable.

>Regarding professional accounting/financial environments, I would hope that
>proper internal controls would prevent the situation where a large number of
>invoices/checks/whatever would have to be matched (trial and error) against
>an amount. Of course there's always the customer who sends a massive check
>paying some unknown combination of invoices. Consequently, for those
>instances, a phone call to the payee should clear up the confusion
>definitively. You wouldn't want to just guess, right?
....

In the real world, reconcilliation of different data sources that
should produce the same results is an unfortunate recurring problem.
And there's often no one to call to get a quick, simple answer.

>If a large, multi-solution, iteratave approach cannot be avoided
>though....You're right, a vba program would be the way to go.

Yup.

0
hrlngrv (1990)
1/4/2006 6:53:10 PM
Sorry, I missed that it had already been mentioned here

-- 
Kind regards,

Niek Otten

"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:OGbEBAWEGHA.1088@tk2msftngp13.phx.gbl...
> Use Google's  group search: 
> http://groups.google.com/advanced_group_search
>
> , look in Groups *excel*, with all of the words Add up numbers, Author 
> Harlan (yes, indeed, Harlan Grove)
> and you'll find a discussion and very advanced solutions about this 
> subject
>
> -- 
> Kind regards,
>
> Niek Otten
>
> "Dave O" <CycleZen@yahoo.com> wrote in message 
> news:1136400399.116848.41620@g44g2000cwa.googlegroups.com...
>> You're right, Jim, the OP could reduce his solution space by
>> disregarding the numbers greater than his "target" number.  In an
>> accounting environment, however, debits and credits (positive as well
>> as negative) may need to be considered- the negative numbers may react
>> with the positive larger numbers to arrive at the correct solution.
>>
>
> 


0
nicolaus (2022)
1/4/2006 7:04:40 PM
That is not quite what the code does. What it does is it sorts the original 
values lowest to highest. Negatives will obviously be the lowest values. When 
it is doing the combinations it moves in the direction of adding the next 
highest number. If the combination exceeds the target value then it abandons 
moving to the following next highest value because it obviously is not a 
possible solution. I am not sure that I explained that very well but sufice 
it to say that it works and it speeds up the execution by potentially a few 
orders of magnitude. 
-- 
HTH...

Jim Thomlinson


"Dave O" wrote:

> You're right, Jim, the OP could reduce his solution space by
> disregarding the numbers greater than his "target" number.  In an
> accounting environment, however, debits and credits (positive as well
> as negative) may need to be considered- the negative numbers may react
> with the positive larger numbers to arrive at the correct solution.
> 
> 
0
1/4/2006 7:07:02 PM
When a customer sends you a check for $1,173.76 with no backup then you match 
it the best you can. Been there and done that. They will need all possible 
solutions because they will want to match to the oldest stuff first.

This kind of code is also very handy for doing year end working papers where 
you need to reconcile the ending amount of a Balance Sheet account. Usually 
you can match off the vast majority of the debits and credits but very often 
you end up with a few entries that (because of reversels, reclassifications 
and just plain weirdness) don't match easily. That is another place where 
this kind of thing thing is handy.
-- 
HTH...

Jim Thomlinson


"Ron Coderre" wrote:

> Solver isn't a panacea....It's just a nice shortcut for relatively simple 
> situations without having to find or write code.  However, if solver finds 
> one acceptable solution....couldn't we just create another "flag" field to 
> prevent the same value from being used more than once?  
> 
> Regarding professional accounting/financial environments, I would hope that 
> proper internal controls would prevent the situation where a large number of 
> invoices/checks/whatever would have to be matched (trial and error) against 
> an amount. Of course there's always the customer who sends a massive check 
> paying some unknown combination of invoices. Consequently, for those 
> instances, a phone call to the payee should clear up the confusion 
> definitively. You wouldn't want to just guess, right?
> 
> If a large, multi-solution, iteratave approach cannot be avoided 
> though....You're right, a vba program would be the way to go.
> 
> 
> ***********
> Regards,
> Ron
> 
> XL2002, WinXP-Pro
> 
> 
> "Bill Martin" wrote:
> 
> > I do like your Solver approach -- I hadn't thought of that.  Given that this is
> > an Accounting problem though, how would one get Solver to identify multiple
> > solutions to the problem when they exist?  If you're trying to match invoices
> > you'd like to know that you're matching them correctly -- not just *possibly*
> > correctly.  Which requires a person to stare at *all* the various possible
> > solutions and decide which one is most likely given some knowledge of the
> > customers involved and what they've ordered in the past, etc.
> > 
> > For example, take the list of 10 values that Dza provided and use them all twice
> > to make 20 entries.  Now there are 8 valid solutions, but Solver only seems to
> > find one and stops.
> > 
> > Personally, I think you need VBA for this problem but I'm open to education...
> > 
> > Bill
> > ----------------------------------
> > Ron Coderre wrote:
> > > Have you tried using Excel Solver
> > > 
> > > First a little prep work....
> > > 
> > > A1:A1 (your list of values)
> > > B1:B10 (leave blank)
> > > C1: =A1*B1
> > > (copy that fomula down through C10
> > > 
> > > C11: =SUM(C1:C10)
> > > 
> > > Now to use Solver....
> > > Tools>Solver
> > > Set Cell: C11
> > > Equal to the Value of: 1173.76
> > > By Changing Cells: B1:B10
> > > Subject to the Constraints....
> > > (click the add button and constrain B1:B10 to Binary)
> > > Click [OK] 
> > > Click [Solve]
> > > 
> > > Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a 
> > > combination that sums to 1,173.76
> > > 
> > > Does that help?
> > > 
> > > ***********
> > > Regards,
> > > Ron
> > > 
> > > XL2002, WinXP-Pro
> > > 
> > > 
> > > "Dave O" wrote:
> > > 
> > > 
> > >>The answer is:
> > >>$725.90
> > >>$240.16
> > >>$207.70
> > >>
> > >>I've written a program that applies a brute force approach to the task-
> > >>it checks every possible combination of the "pool" of numbers to arrive
> > >>at the target total.  The brute force idea works for comparatively
> > >>small pools, but since the number of possible combinations doubles with
> > >>each additional pool member the processing time increases
> > >>commensurately.  One poster to this newsgroup wanted to process a list
> > >>of 100 numbers, which amounts to
> > >>1,267,650,600,228,230,000,000,000,000,000 possible combinations and
> > >>would require the resources of a major government (or maybe just the
> > >>NSA) to process.
> > >>
> > >>How many of these do you have?  I don't mind doing a few for you.
> > >>
> > >>
> > 
> > 
0
1/4/2006 7:15:03 PM
Jim Thomlinson wrote...
....
>Private Sub PostAnswers(ByVal strValue As String, ByVal rng As Range)
>    Dim aryCSVValues    As Variant
>    Dim intCounter      As Integer
>
>    aryCSVValues = Split(Mid$(strValue, 2, Len(strValue)), "+")
>    For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
>         rng.Value = aryCSVValues(intCounter)
>         Set rng = rng.Offset(0, 1)
>    Next intCounter
>End Sub
....

This is your code. You should have indicated that. You also made a few
modifications in my original procedures. I don't have an issue with you
modifying my code, just with the lack of any way to distinguish your
code from mine.

Off-topic: I hate long variable names. There's a problematic case for
them in long, complex procedures, but other than typing exercise I
don't see the usefulness in short procedures. Ah, for programmers'
editors in which different colors could be assigned to variable tokens
of different types!

Back on-topic. My own code is at

http://groups.google.com/group/microsoft.public.excel/msg/7419858047398beb

Your comment in your other response in this thread is apt: N > 30 makes
for LONG execution times, but the macro works for larger N. I haven't
torture-tested it, but the large N with skewed values (median value
outside mean +/- 25%) will almost certainly exceed most PC's memory
resources, real and virtual. I have a test case with N=100 cells filled
with values generated by =ROUND(RAND()^-4,2), in the particular case 68
of 100 values < 100, and sought 5000 as the sum. There were 129
combinations of 1 to 6 values summing to 5000 and 464 of 7 (when I
cancelled the macro). Not sure how much information there might be if
there were more than 1 million combinations summing to 5000. How would
anyone choose which one to use?

In other words, the programming was an interesting exercise, but I
still don't believe it provides any value.

0
hrlngrv (1990)
1/4/2006 7:23:31 PM
My appologies for not documenting where I had made modifications to your 
code... As a professional courtesy I should have done that and I will 
endevour to make the necessary notations at my end. Thanks for sharing your 
work and once again I appoligize.

As for long variable names I have always favoured them purely from a 
readability standpoint. I have debugged too much code written by others that 
was almost impossible to follow. Not to mention it keeps things straight in 
my head when I am writing it. Probably more the latter than the former... :-)
-- 
HTH...

Jim Thomlinson


"Harlan Grove" wrote:

> Ron Coderre wrote...
> >Solver isn't a panacea....It's just a nice shortcut for relatively simple
> >situations without having to find or write code.  However, if solver finds
> >one acceptable solution....couldn't we just create another "flag" field to
> >prevent the same value from being used more than once?
> 
> Perhaps. How would you do that since it's not one value but one
> combination of values (OK, a vector of 1s and 0s that could be
> considered a single vector value in {0,1}^N) that'd need to be
> excluded. As I see it, you'd need to use a kludge like SUMPRODUCT of
> the vector of 1s and 0s against 2^(ROW(INDIRECT("1:"&N))-1) to produce
> unique identifiers for each solution, save them in a list, then use a
> COUNTIF = 0 expression on that list with criteria equal to the current
> SUMPRODUCT value. And you'd need to automate storing the idenifiers for
> previous solutions, so VBA is unavoidable.
> 
> >Regarding professional accounting/financial environments, I would hope that
> >proper internal controls would prevent the situation where a large number of
> >invoices/checks/whatever would have to be matched (trial and error) against
> >an amount. Of course there's always the customer who sends a massive check
> >paying some unknown combination of invoices. Consequently, for those
> >instances, a phone call to the payee should clear up the confusion
> >definitively. You wouldn't want to just guess, right?
> ....
> 
> In the real world, reconcilliation of different data sources that
> should produce the same results is an unfortunate recurring problem.
> And there's often no one to call to get a quick, simple answer.
> 
> >If a large, multi-solution, iteratave approach cannot be avoided
> >though....You're right, a vba program would be the way to go.
> 
> Yup.
> 
> 
0
1/4/2006 8:00:05 PM
My appologies for not documenting where I had made modifications to your 
code... As a professional courtesy I should have done that and I will 
endevour to make the necessary notations at my end. Thanks for sharing your 
work and once again I appoligize.

As for long variable names I have always favoured them purely from a 
readability standpoint. I have debugged too much code written by others that 
was almost impossible to follow. Not to mention it keeps things straight in 
my head when I am writing it. Probably more the latter than the former... :-)

-- 
HTH...

Jim Thomlinson


"Harlan Grove" wrote:

> Jim Thomlinson wrote...
> ....
> >Private Sub PostAnswers(ByVal strValue As String, ByVal rng As Range)
> >    Dim aryCSVValues    As Variant
> >    Dim intCounter      As Integer
> >
> >    aryCSVValues = Split(Mid$(strValue, 2, Len(strValue)), "+")
> >    For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
> >         rng.Value = aryCSVValues(intCounter)
> >         Set rng = rng.Offset(0, 1)
> >    Next intCounter
> >End Sub
> ....
> 
> This is your code. You should have indicated that. You also made a few
> modifications in my original procedures. I don't have an issue with you
> modifying my code, just with the lack of any way to distinguish your
> code from mine.
> 
> Off-topic: I hate long variable names. There's a problematic case for
> them in long, complex procedures, but other than typing exercise I
> don't see the usefulness in short procedures. Ah, for programmers'
> editors in which different colors could be assigned to variable tokens
> of different types!
> 
> Back on-topic. My own code is at
> 
> http://groups.google.com/group/microsoft.public.excel/msg/7419858047398beb
> 
> Your comment in your other response in this thread is apt: N > 30 makes
> for LONG execution times, but the macro works for larger N. I haven't
> torture-tested it, but the large N with skewed values (median value
> outside mean +/- 25%) will almost certainly exceed most PC's memory
> resources, real and virtual. I have a test case with N=100 cells filled
> with values generated by =ROUND(RAND()^-4,2), in the particular case 68
> of 100 values < 100, and sought 5000 as the sum. There were 129
> combinations of 1 to 6 values summing to 5000 and 464 of 7 (when I
> cancelled the macro). Not sure how much information there might be if
> there were more than 1 million combinations summing to 5000. How would
> anyone choose which one to use?
> 
> In other words, the programming was an interesting exercise, but I
> still don't believe it provides any value.
> 
> 
0
1/4/2006 8:05:02 PM
Hi Harlan.  I love your "FindSum" program. It's excellent!  Just for 
feedback, in a permutation timing program that I have, I was coming up 1 
number larger in the total number of solutions.  Tracing the program back, 
it appears to me that if the list is sorted, then the program misses the sum 
of the first 'n' items.  For example, if the op's data were sorted, then it 
would miss finding the sum of the first two items (207.70+240.16 = 447.86)

A more simplier test might be with the number sequence 1,2,3...10.    A 
search for 3 might miss 1+2, or a search of 6 might miss 1+2+3.
Again, only if the data is sorted.  I'm not sure at this point where in the 
program to make a recommendation.  Excellent code though.  :>)

-- 
Dana DeLouis
Win XP & Office 2003


"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1136402611.240334.248770@g43g2000cwa.googlegroups.com...
> Jim Thomlinson wrote...
> ...
>>Private Sub PostAnswers(ByVal strValue As String, ByVal rng As Range)
>>    Dim aryCSVValues    As Variant
>>    Dim intCounter      As Integer
>>
>>    aryCSVValues = Split(Mid$(strValue, 2, Len(strValue)), "+")
>>    For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
>>         rng.Value = aryCSVValues(intCounter)
>>         Set rng = rng.Offset(0, 1)
>>    Next intCounter
>>End Sub
> ...
>
> This is your code. You should have indicated that. You also made a few
> modifications in my original procedures. I don't have an issue with you
> modifying my code, just with the lack of any way to distinguish your
> code from mine.
>
> Off-topic: I hate long variable names. There's a problematic case for
> them in long, complex procedures, but other than typing exercise I
> don't see the usefulness in short procedures. Ah, for programmers'
> editors in which different colors could be assigned to variable tokens
> of different types!
>
> Back on-topic. My own code is at
>
> http://groups.google.com/group/microsoft.public.excel/msg/7419858047398beb
>
> Your comment in your other response in this thread is apt: N > 30 makes
> for LONG execution times, but the macro works for larger N. I haven't
> torture-tested it, but the large N with skewed values (median value
> outside mean +/- 25%) will almost certainly exceed most PC's memory
> resources, real and virtual. I have a test case with N=100 cells filled
> with values generated by =ROUND(RAND()^-4,2), in the particular case 68
> of 100 values < 100, and sought 5000 as the sum. There were 129
> combinations of 1 to 6 values summing to 5000 and 464 of 7 (when I
> cancelled the macro). Not sure how much information there might be if
> there were more than 1 million combinations summing to 5000. How would
> anyone choose which one to use?
>
> In other words, the programming was an interesting exercise, but I
> still don't believe it provides any value.
> 


0
delouis (422)
1/4/2006 9:58:42 PM
Just for the record, Harlan, I wholeheartedly agree with everything you et 
al have mentioned regarding cross-matching details to totals. These 
scenarios rarely have an elegant solution....having been there and done that 
during the 12 years I spent as a financial/accounting manager.  I never once 
considered Solver more than a shot-in-the-dark approach to try first, just 
in case. The basic problem is huge! After all, it took someone with your 
intellectual horsepower to finally come up with code that has a reasonable 
shot at dealing with the issue.

Unfortunately, the best defense is to try to do everything feasible to avoid 
the situation and hope the worst case never happens.

Regards,
Ron

"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1136400789.954697.53710@g49g2000cwa.googlegroups.com...
> Ron Coderre wrote...
>>Solver isn't a panacea....It's just a nice shortcut for relatively simple
>>situations without having to find or write code.  However, if solver finds
>>one acceptable solution....couldn't we just create another "flag" field to
>>prevent the same value from being used more than once?
>
> Perhaps. How would you do that since it's not one value but one
> combination of values (OK, a vector of 1s and 0s that could be
> considered a single vector value in {0,1}^N) that'd need to be
> excluded. As I see it, you'd need to use a kludge like SUMPRODUCT of
> the vector of 1s and 0s against 2^(ROW(INDIRECT("1:"&N))-1) to produce
> unique identifiers for each solution, save them in a list, then use a
> COUNTIF = 0 expression on that list with criteria equal to the current
> SUMPRODUCT value. And you'd need to automate storing the idenifiers for
> previous solutions, so VBA is unavoidable.
>
>>Regarding professional accounting/financial environments, I would hope 
>>that
>>proper internal controls would prevent the situation where a large number 
>>of
>>invoices/checks/whatever would have to be matched (trial and error) 
>>against
>>an amount. Of course there's always the customer who sends a massive check
>>paying some unknown combination of invoices. Consequently, for those
>>instances, a phone call to the payee should clear up the confusion
>>definitively. You wouldn't want to just guess, right?
> ...
>
> In the real world, reconcilliation of different data sources that
> should produce the same results is an unfortunate recurring problem.
> And there's often no one to call to get a quick, simple answer.
>
>>If a large, multi-solution, iteratave approach cannot be avoided
>>though....You're right, a vba program would be the way to go.
>
> Yup.
> 


0
1/5/2006 1:55:44 AM
Wow, thank you so very much for all the responses.  I've gone blind
looking at these amounts over the past few days so I really appreciate
the responses.  First off, amongst some of my accountant co workers I
know alot about excel but the VBE stuff is a bit shady for me.  I've
done some macros but the code Mr. Thomlinson provided is a bit out of
my league.  I'd need a more thorough explanation as to how to set up
the code for me to make that work.  I pasted it into VBE and turned on
those options but I don't know what to do thereafter.  I also want to
try out the solver option but I need to have it installed.  I'll have
to try it at work tomorrow morning, man I really didn't expect all
these responses this quickly, guess it helps posting in more than one
group.

For the record it seems that a few of the invoice amounts were missing
from my list hence the huge problem with matching them up to payments.
Even with that i still have some matching issues.  I also forgot to
mention that an invoice amount once used when matched to a payment
cannot be used again.  The combinations of course must match up to the
payments to the penny.

Again I really would like to try the VBE FindSum program but I'll need
some newbie introduction into how to write a code like that and then
how to use it.  Thanks again!

Dza the accountant

0
dvpetta (9)
1/5/2006 4:40:35 AM
Look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

-- 
Kind regards,

Niek Otten

<dvpetta@gmail.com> wrote in message 
news:1136436035.271421.303110@g43g2000cwa.googlegroups.com...
> Wow, thank you so very much for all the responses.  I've gone blind
> looking at these amounts over the past few days so I really appreciate
> the responses.  First off, amongst some of my accountant co workers I
> know alot about excel but the VBE stuff is a bit shady for me.  I've
> done some macros but the code Mr. Thomlinson provided is a bit out of
> my league.  I'd need a more thorough explanation as to how to set up
> the code for me to make that work.  I pasted it into VBE and turned on
> those options but I don't know what to do thereafter.  I also want to
> try out the solver option but I need to have it installed.  I'll have
> to try it at work tomorrow morning, man I really didn't expect all
> these responses this quickly, guess it helps posting in more than one
> group.
>
> For the record it seems that a few of the invoice amounts were missing
> from my list hence the huge problem with matching them up to payments.
> Even with that i still have some matching issues.  I also forgot to
> mention that an invoice amount once used when matched to a payment
> cannot be used again.  The combinations of course must match up to the
> payments to the penny.
>
> Again I really would like to try the VBE FindSum program but I'll need
> some newbie introduction into how to write a code like that and then
> how to use it.  Thanks again!
>
> Dza the accountant
> 


0
nicolaus (2022)
1/5/2006 8:26:08 AM
If your list only has up to 9 values, the following worksheet functions
will do the trick.

1. In range A1:A9 enter the list of amounts

2. Enter you're the figure you need to reconcile in A11

3. In the range of cells A15:I525,
 enter the formula "=VALUE(MID(DEC2BIN(ROW()-14,9),COLUMN(),1))"

4. In the range J15:J525,
 enter the array formula "{=MMULT(A15:I525,A1:A9)}"

5. In the range B1:B9,
 enter the formula "=OFFSET($A$14,MATCH($A$11,$J$15:$J$525,0),ROW()-1)"

Amounts that reconcile to the figure in A11 will have a 1 next to them
in column B

0
1/6/2006 4:25:45 PM
Reply:

Similar Artilces:

adding cells which include numbers and text
Hi, I am hugely ignorant regarding excel, no training, and apparrently no brain... What I need to do is allocate a number to a placing i.e. 1st = 100, 2nd = 99, 3rd = 98 etc. If A5 (for example) has 1st in it, I put in A6: =101-A5, but it will not add it because of the text. How do I get the formula to ignore the text in A5??? Shelley ;) Hi! One way: =101-SUBSTITUTE(A5,RIGHT(A5,2),"")*1 Biff "Shelley" <shelleymcadam@hotmail.com> wrote in message news:1130215231.476700.153960@g47g2000cwa.googlegroups.com... > Hi, I am hugely ignorant regarding excel, no trai...

Exchange fax places a '1' before the number when merge to fax
I’m trying to send a mass faxes from Word. I had to add a file to the windows directory that will enable the feature in Word to ‘merge to fax’. But the issue that I’m having is that every time I send the merge faxes internationally, it adds a ‘1’ right in front of the number. I check the console but I couldn’t find a way to take that out. Any idea? If I send it individually, by just typing the number 01133… it doesn’t put in a ‘1’ in front of the number. Please help and thanks in advance. ...

extract number of week from varchar
Hi all, If I have a field with type VarChar, contain data like 20100401, which denote a date format yyyymmdd, how can i use SQL to extract the number of week from the string (i.e., "15"th week of 2010) thanks. What version of SQL Server? How do you calculate week numbers? Week 1 is first week with 4 days in it (according to intl standard)? Or Jan 1 is always in week 1? Or something else? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "DaveHenson" <tangchihin@gmail.com> w...

Virtual list control #3
Does any one know exactly when the control is supposed to get the LVN_GETDISPINFO message? I seem to be getting too many of them. ...

Print Advanced Find Criteria
I have users who have requested that when they print the advanced find results they would like to also have the advanced find criteria printed. This would enable them to know what specifically the printout consists of when viewed at a later time. Any ideas? Hi Steve, Print screens cannot be customized. -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. uMar Khan :: CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com "Steve Bambauer" wrote: > I have users who have requested that when they pr...

Hot do I Fix the number of records displayed on a report
I have a report that needs to show the top 10 sales opportunities. I have it setup and it works fine as long as there are actually 10 opportunities that get returned from the query. The problem is that if only 5 opportunities happen to exist, it only shows the 5 lines of opportunities. I need it to show blank lines for the other 5. I have it laid out so it looks like a table. The reason this is a problem, is that I actually have 3 different tables like this on a report one right after another. If one only shows 5 lines, instead of the 10 I have the space allocated for, it leave a big ...

Non Exchange Users in Exchange Global List
Is there a way to put E-mail addresses of users in say a different company into the Exchange Global Contacts? So I guess the best example is this: I have 50 users who have local exchange email addresses on my server and are actual employees of my company. I have 20 users who are affiliated with us who work for 2 different companies that we have Joint Projects with, we would like to have these 20 users in a Global Contact list for ease of use for my exchange users Is there an easy way to do this? Create mail-enabled contacts. Set the target address to be their "real" e-mail ad...

find/replace
Win XP, Word 2003 I exported a field in FileMaker Pro. It is a repeating field with two dates in one field. I thought I could import the field into Word and find the marker that separates one date from the next and do a Replace All and put a Tab between the two dates and then put the dates into two different fields, the way they belong. I have the field in Word and there is a small rectangle between the dates. So far nothing I have used as a Find has "found" it, except for White Space (^w), but when I do a find ^w and replace ^t, Word replaces thousands of the occ...

Home Depot Removed from Bank lists?
I noticed about a week ago that my Home Depot Credit card account is no longer updating. I checked the list of financial institutions that are supported at http://www.microsoft.com/money/bankonline.aspx and it no longer appears in the list. Of course, this list is on a web page for Money Plus so I don't know if it is valid for Money 2007 Deluxe or not. I opened a support request with MS and they are telling me that the problem is with Yodlee and that Yodlee is working on the problem. I think that may be incorrect since I can log into a Yodlee account accumulation account and it ...

Copy from one list to another in different time scales. Confusing?
Would anyone know what type of code to use so that it copies 1981q1 into Jan 1981, Feb 1981, Apr 1981 and then moves on to 1981q2 and copies the value there to paste it into Apr 1981, May 1981, and Jun 1981.... This goes on till 2050 and it is a pain to continue doing this. column A are the numbers 111, 222, 333.... Column B are the months-year Column C are the numbers that are copied from Column F Column D is blank Column E are the years and quarters Column F is the GDP in that quarter. 1 Jan-1981 5,307.5 1981q1 5,307.5 1 Feb-1981 5,307.5 1981q2 5,266.1 1 Mar-1981 5,307.5 1981q3 5,3...

Extracting a 'number' from text
Hi I've been given a text file with one particular colum that is 'free text entrry' There is an 8 figure serial number that always starts with 39 (39xxxxxx). However, as this has been typed in by various people, it can appear anywhere with in the rest of the text such as: - fred blogs 39123456 12/02/04 12.02.04 blogs fred, 39123456 fred, 39123456 12-feb-04 As many people are typing information in, some people use different methods (commas, spaces, slashes, no spaces, no commas etc) The only thing that is common is that the 39 figue is always 8 characters long How can I ex...

List Box to create a Report based on crosstab query
I have a form with a list box to select a value, and a button that is pressed to create a report based on the value selected in the list box. The problem is the query used to create the report and populate the values in the list box is a crosstab query, which is not updateable of course. Here is the SQL for my query: TRANSFORM Avg(Val([tblCourseGrades].[Grade])) AS AvgOfGrade SELECT TblStudents.[ClassNumber], TblStudents.Rank, TblStudents.[Last Name], TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1 FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCours...

where to find a selfdelete program?
hello, Where can I find a selfdelete code written by vc? That means the program can delete itself. Thanks a lot. afaik, a program can't delete itself while it is running. the same effect can be done by using a batch file, however, because batch files can delete themselves. you should make your app create a batch file, then execute the batch (i think with shellexecute();). at the end of the batch file, use: del filename.bat. good luck. nicknamenickname@126.com wrote: > hello, > > Where can I find a selfdelete code written by vc? That means the > program can...

finding words
hi all this is maybe a little bit simple but.. i use an access database in an VB 6 program When i want to find a name in de table, i don't have a problem when i spell it correct but i want to find also de words when i leave the last symbols or when i'm lookin' for more possibilities like i spell 'house' but i want also find 'ourhouse' 'housebell' etc Who can help me please ...

Messy update no longer accepts serial number!
Similar problem here, only perhaps even more of a puzzle. I've just downloaded and installed the update. Upon starting Word, I'm asked to enter my name and serial number, which I do, only to have it rejected. I've very, very carefully re-entered, re-checked and re-tried the serial number that was with 10.0, but now I'm told it is incorrect. Looks like an uninstall 10.1.6 and a re-install of 10.0. Waste of time and energy (in other words, a waste of money). In article <ae9d8f91.0410260224.276db7d6@posting.google.com>, david.neale@pandora.be (David) wrote: > Similar p...

update a table based on a sum query
Hi All, I have seen the zillions of answers to this that state "never add a total into the table.." But there are cases, such as mine when it is prefered. I want to update a project table with the final invoice cost. I want it entered as a hard figure so that I can then export the table for accounting purposes. Additionally, once an invoice has been generated, there isn't any need for the total to ever, ever change! In fact it shouldn't. So the question is.... is there a way to do this? To update a table with a value from a sum query? Thanks for any help. ...

How do I find which screen my app is running in?
Hi, When my app starts it always starts up in the primary display of a multi-display setup. If the user decides to always put my app in screen 2, is there a way in VB that allows you to identify the screen you are running in? I would then check the screen I am running in as the user closes the app and store it in a registry setting. Then when the user re-opens the app make sure it comes up in the same screen (probably needs a test to ensure the user hasn't disconnected a screen since you last opened)? I have had a look at the "My" command and can find read only ...

Finding the biggest number out of 8 variables
Hi All Does anybody know of an easy way to find out which variable has the highest value out of 8 variables? For example, I have these values in 8 vars: 3 0 7 30 37 17 0 7 Although from a human's perspective it's easy to see that the 5th var has the highest value, how can I chose the appriopriate var programmatically? Thanks There are many solutions. You could put the values into an array and pass the array to a QuickSort routine. The QuickSort algorithm is difficult to understand, but you really don't need to know how it works. See: http://vbnet.mvp...

Sum of all combinations
I have 7 categories, each category has 5 possible scores. I want to calculate the sum of each possible combination. Is there a way to do this in Excel? My grid looks like this: JobKnowl 15 12 9 6 3 CallAcc 15 12 9 6 3 CallQual 15 12 9 6 3 JobAcc 5 4 3 2 1 IPSkill 10 8 6 4 2 Attend 20 16 12 8 4 Punct 20 16 12 8 4 "cursednomore" wrote: > I have 7 categories, each category has 5 possible scores. > I want to calculate the sum of each possible combination. > Is there a way to do this in Excel? I would write a VBA subroutine. But .... > My grid looks like this: > JobK...

Collections Management -add Customer PO Number to collection lette
I work with several installations that need to reference their customer's PO number when talking with their customers. One of these installations recently decided to add Collections Management. We're disappointed that we cannot pull the Customer PO Number onto their collection letters. What they're doing for now is always sending the customer statement (which has been modified to show the Customer PO Number from their SOP invoices) everytime they send a collection letter. Our suggestion would be to make the Customer PO Number be available to use on collection letters. ----...

workflow e-mail template dropdown list is empty
hello i want to create a rule for object (case ) like: When case is created e-mail to: (account); .... and here I want to put an e-mail template, but the dropdown list is empty. I have created all kind of e-mail templates but... the list is still empty. can anybody tell me what can i do to see my templates there thankyou Have you entered an e-mail template for Case objects? Templates are stored by object type. Did you publish the email template as public? E-mail templates to be used in workflow, must be published as public, not private. Hope this helps. Rich Elliott Associat...

Mix of numbers/letters in same column
Hi, I've got a problem with using Excel 2003 data in other programmes (either Access or BarTender label software), in the sense that it will not display certain items of a particular column. The 'Size' column of the worksheet consists of a mixture of numbers (8,10, . .. 20) and letters (S,M,L,XL). When trying to import the data into other programmes it recognises the numbers but leaves out the letters, ie returning blank cells. Is there a way that the mixed information can be formatted without me having to list letters and numbers in separate columns? (Format Cells > e...

m2 behinde a number and I want to sum
5 m=B2 3 m=B2 7 m=B2 2 m=B2 2m=B2 12m=B2 How can I get the sum without clearing the m2 in the field? Let's say the first on in in A1 Insert a new column B In B1 enter =--LEFT(A1,LEN(A1)-3); this will retunr the number 5 (if you have 5 m� in A1) Copy down the column and add Now you have real numbers that can be added For a permanent solution: after you have done the above Select all the new B values and give them the custom format: 0 "m�" Select all the B values and Copy, with them still selected use Edit | Paste Special -> Values to turn the formulas to nu...

random generated order numbers
In terms of ordering. If a customer places an order, traditionally they are given an order number for reference (eg. 12345) Are those numbers generated sequentially per order completed or through order started? Is it possible to have a random generated (within confines) order #? Reason I ask is there are some companies have a guy place an order then go on Canada post and sequentially put in order numbers So they can steal customer lists. Any help you can give me on this would be greatly appreciated. Hi Angelique The only way to enter random numbers is to do so manually. Within the...

drop down list showing only 1 of everything
Hi everyone, basically on one sheet( called data) i have a list of all the data, the first column is the date. On a separate sheet i want to have a drop down list in the cell (A1 for example). however i want the drop down list to show only 1 of each date that was in column A of the data sheet. for example say column A had the folowing dates: 1/1/04 ; 2/1/04 ; 2/1/04 ; 3/1/04 ; 4/1/04 ; 4/1/04 the drop down list on sheet 2 would show the values: 1/1/04 ; 2/1/04 ; 3/4/04 ; 4/4/04 i need some help in doing this and would appreciate any ideas. thank you.... Hi do you need this formul...