Excel audits

[jezzebelld]

Many client payments satisfy two or more invoices. From a list of outstanding invoice amounts, is there a way to find which combination of values equal a particular remittance value? Ex: 15 outstanding invoices totaling $23,598.24, client sends a remittance for $13,283.12. Since this imbursement could apply to 1-15 invoices, which invoices did they intend to pay? Choices are to call the client to inquire their intent, or hit and miss clicks to find the right combinations. There’s GOT to be a better way! Advice???

 

[Crittar]

Jez,

Is this off topic or are you looking for a solution in Progress?

 

[Norman Biggar]

With 3 invoices, you could have 7 combinations - a,b,c,ab,ac,bc,abc

With 4 invoices, you would have 15 combinations

5 - 31
6 - 63

See the pattern? (2 to the power n) - 1, (I studied Statistics - how sad) where n is the number of invoices.

So for 15 invoices, you would have (2 to the power 15) - 1 possible combinations 32,767!. You could also find more than one combination that matches the total.

Unfortuneately, Progress is no more a mind reader than I or Yuri Geller. And performing 32,767 mathematical calculations is going to be slow.

However, the code below might be useful

DEF VAR fVals AS DEC NO-UNDO EXTENT 4 INIT [1,10,100,1000].

RUN p-Total(0,0).

PROCEDURE p-Total:
  DEF INPUT PARAM ipExt AS INT NO-UNDO. 
  DEF INPUT PARAM fpAccum AS DEC NO-UNDO.
  DEF VAR fTot AS DEC NO-UNDO.
  DEF VAR iLoop AS INT NO-UNDO.
  DO iLoop = ipExt + 1 TO EXTENT(fVals):
    ASSIGN fTot = fpAccum + fVals[iLoop].
    MESSAGE fTot VIEW-AS ALERT-BOX.
    IF iLoop GT ipExt THEN
      RUN p-Total(iLoop,fTot).
  END.
END PROCEDURE.