I'm not familiar with either of those applications, but based on what you've described that seems like a significant issue. The first link here, I believe, mentions the same problem, and apparently there are differences between importing a transaction and manually entering one...I don't know. The second describes some type of work-around about halfway down the page, but I have no idea whether that applies in your case:
Engage with experts and peers in the Dynamics 365 community forums
community.dynamics.com
Engage with experts and peers in the Dynamics 365 community forums
community.dynamics.com
I found another option for you to consider. In 2014, Tony Dallimore contributed several suggestions for tackling this type of problem at:
Ok, I´ve found similar questions but none of them solve this problem so here I go: I´ve a list of individuals (col. "A"), and each of them has a value assigned for a determined parameter (col. "B"...
stackoverflow.com
Importantly, the objective is to determine which combinations of numbers produce a sum that is either closest to a target value or within some range around a target value. The user supplies the list of starting values and the target or range. Tony describes his three offerings in terms of complexity, limitations, and general strategies for searching/solving. I am most interested in the more complex offering he describes as "Approach 3", but I have been unable to determine why it will not run for me. I also ran into difficulties with "Approach 2", although I was able to get the most basic approach working. This basic approach uses a brute-force method to determine sums of all combinations of receipts, so a major issue with it is the runtime required and whether limitations will prevent the computation of sums for tens or hundreds of millions of combinations. For that reason, if you can reduce the number of inputs, such as not using $0 receipts, that will help. In your posted example, there were 18 receipts, 2 of which were $0. I eliminated those and ran the program with 16 receipts, targeting a range of 1425.00 to 1425.20. The default setting of the program expects the initial data to be on a sheet named Source. A sheet named Results also needs to be available, as the results are written to it. I did not change the default settings, which expect the receipt data to be listed on the Source sheet in B2 and down, and in A2 and down, a unique letter code is assigned to each receipt amount. The code ran for about 2:15 (m:ss) and then returned 6 combinations satisfying the sum range condition. I then added 4 fictitious receipts (total of 20) and returned 23 minutes later to discover that it had completed, returning 22 combinations satisfying the sum range criteria. You can pick whichever combination is closest to the target. I would have guessed that reasonable estimates for runtimes would double for every receipt added: so a known 2:15 for 16 receipts...then guesses of 4:30 for 17, 9:00 for 18, 18:00 for 19, and 36:00 for 20. The fact that 20 ran in less than 23 minutes is encouraging because it may be tenable for your occasional use.
The Source sheet I used is here. Copy the block of data from your table to the white portion of the Source table, then separately copy the KeyCodes and the total Charge data into A2 and B2 (and down), respectively. Then delete any receipts/keys in the A:B columns if the value is 0...then tighten up the input data column to eliminate gaps produced by the deletions. Next, execute from the Developer menu > Macros ...the "Control" macro. You may have to add Developer to your menu bar if it is not shown. I would suggest trying this out with a very small set of receipts at first...perhaps 5 or 6, where you manually add 2 or 3 of them and establish a small range around that sum (the min and max of the range are use inputs in C3:D3). That will greatly reduce runtime and confirm that the code works on your system if it returns a combination matching your preselected receipts. I added something to the Source sheet to facilitate post-processing. After reviewing the Results sheet to identify the specific combination that you would like to use, copy the combination from the B column and paste it into G1 on the Source sheet. A formula will extract the individual letter key codes and insert 1's and 0's next to your original source data table indicating which days/vendors are part of that combination. The total shown in D13 will update based on that specific combination.
| MrExcel_20220531_CombinatoricsSum.xlsm |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
|---|
| 1 | Key | Value | Target | 1425.10 | | Paste Choice | A+D+E+G+I+J | | | | | | | |
|---|
| 2 | A | 200.88 | Min | Max | | A | | | | | | | | |
|---|
| 3 | B | 275.84 | 1425.00 | 1425.20 | | D | | | | | | | | |
|---|
| 4 | C | 255.60 | | | | E | | | | | | | | |
|---|
| 5 | D | 267.11 | | | | G | | | | | | | | |
|---|
| 6 | E | 187.06 | | | | I | | | | | | | | |
|---|
| 7 | F | 250.04 | | | | J | | | | | | | | |
|---|
| 8 | G | 319.09 | | | | | | | | | | | | |
|---|
| 9 | H | 325.41 | | | | | | | | | | | | |
|---|
| 10 | I | 224.46 | | | | | | | | | | | | |
|---|
| 11 | J | 226.50 | | | | | | | | | | | | |
|---|
| 12 | K | 281.71 | | Total | | | | | 365 | 123 | 242 | 4135.16 | 1425.10 | 2710.06 |
|---|
| 13 | L | 256.29 | | 1425.10 | Use | KeyCodes | Date | Vendor | # Meals | A | D | Charge | A Charge | D Charge |
|---|
| 14 | M | 222.94 | | | 1 | A | 04/04/22 (Mon) | Zaxbys | 21 | 7 | 14 | 200.88 | 66.96 | 133.92 |
|---|
| 15 | N | 260.39 | | | 0 | B | 04/06/22 (Wed) | Romeros Las Brazas | 18 | 6 | 12 | 275.84 | 91.95 | 183.89 |
|---|
| 16 | P | 353.41 | | | 0 | C | 04/07/22 (Thu) | First Class BBQ | 21 | 6 | 15 | 255.60 | 73.03 | 182.57 |
|---|
| 17 | Q | 228.43 | | | 1 | D | 04/08/22 (Fri) | Every Bellies | 19 | 7 | 12 | 267.11 | 98.41 | 168.70 |
|---|
| 18 | R | 225 | | | 1 | E | 04/11/22 (Mon) | Chick-fil-A | 16 | 5 | 11 | 187.06 | 58.46 | 128.60 |
|---|
| 19 | S | 200 | | | 0 | F | 04/12/22 (Tue) | Schlotzskys | 19 | 7 | 12 | 250.04 | 92.12 | 157.92 |
|---|
| 20 | T | 250 | | | 1 | G | 04/13/22 (Wed) | Joes Italian Grill | 23 | 9 | 14 | 319.09 | 124.86 | 194.23 |
|---|
| 21 | U | 275 | | | 0 | H | 04/14/22 (Thu) | French Quarter | 20 | 7 | 13 | 325.41 | 113.89 | 211.52 |
|---|
| 22 | | | | | 1 | I | 04/18/22 (Mon) | Backyard Grill | 16 | 3 | 13 | 224.46 | 42.09 | 182.37 |
|---|
| 23 | | | | | 1 | J | 04/19/22 (Tue) | McAlisters Deli | 19 | 6 | 13 | 226.50 | 71.53 | 154.97 |
|---|
| 24 | | | | | 0 | K | 04/20/22 (Wed) | Yangs Kitchen | 27 | 11 | 16 | 281.71 | 114.77 | 166.94 |
|---|
| 25 | | | | | 0 | L | 04/21/22 (Thu) | Classic Events Cafe | 20 | 6 | 14 | 256.29 | 76.89 | 179.40 |
|---|
| 26 | | | | | 0 | M | 04/22/22 (Fri) | Smashburger | 17 | 6 | 11 | 222.94 | 78.68 | 144.26 |
|---|
| 27 | | | | | 0 | N | 04/25/22 (Mon) | Zaxbys | 26 | 14 | 12 | 260.39 | 140.21 | 120.18 |
|---|
| 28 | | | | | 0 | O | 04/26/22 (Tue) | Jersey Mikes | 21 | 4 | 17 | 0.00 | 0.00 | 0.00 |
|---|
| 29 | | | | | 0 | P | 04/27/22 (Wed) | Romeros Las Brazas | 24 | 8 | 16 | 353.41 | 117.80 | 235.61 |
|---|
| 30 | | | | | 0 | Q | 04/28/22 (Thu) | First Class BBQ | 18 | 5 | 13 | 228.43 | 63.45 | 164.98 |
|---|
| 31 | | | | | 0 | R | 04/29/22 (Fri) | Every Bellies | 20 | 6 | 14 | 0.00 | 0.00 | 0.00 |
|---|
| 32 | | | | | 0 | S | | | | | | | | |
|---|
| 33 | | | | | 0 | T | | | | | | | | |
|---|
| 34 | | | | | 0 | U | | | | | | | | |
|---|
| 35 | | | | | 0 | V | | | | | | | | |
|---|
| 36 | | | | | 0 | W | | | | | | | | |
|---|
| 37 | | | | | 0 | X | | | | | | | | |
|---|
| 38 | | | | | 0 | Y | | | | | | | | |
|---|
| 39 | | | | | 0 | Z | | | | | | | | |
|---|
|
|---|
The Results sheet looks like this after running:
| MrExcel_20220531_CombinatoricsSum.xlsm |
|---|
|
|---|
| A | B |
|---|
| 1 | Total | Key Expn |
|---|
| 2 | 1,425.10 | A+D+E+G+I+J |
|---|
| 3 | 1,425.16 | D+H+L+M+P |
|---|
| 4 | 1,425.00 | A+C+D+F+M+Q |
|---|
| 5 | 1,425.04 | C+D+I+J+M+Q |
|---|
| 6 | 1,425.09 | C+D+E+J+N+Q |
|---|
| 7 | 1,425.11 | A+E+H+M+N+Q |
|---|
| 8 | 1,425.13 | A+C+D+F+J+R |
|---|
| 9 | 1,425.19 | A+H+I+J+M+R |
|---|
| 10 | 1,425.00 | D+G+N+P+R |
|---|
| 11 | 1,425.01 | A+C+G+J+M+S |
|---|
| 12 | 1,425.08 | A+C+J+K+N+S |
|---|
| 13 | 1,425.17 | C+I+L+N+Q+S |
|---|
| 14 | 1,425.03 | A+C+G+I+R+S |
|---|
| 15 | 1,425.12 | A+B+D+L+R+S |
|---|
| 16 | 1,425.19 | D+K+M+Q+R+S |
|---|
| 17 | 1,425.09 | A+C+D+J+R+T |
|---|
| 18 | 1,425.10 | E+F+K+L+S+T |
|---|
| 19 | 1,425.14 | C+F+G+H+U |
|---|
| 20 | 1,425.07 | B+D+H+K+U |
|---|
| 21 | 1,425.09 | A+C+D+J+S+U |
|---|
| 22 | 1,425.06 | E+K+L+R+S+U |
|---|
| 23 | 1,425.10 | C+G+H+T+U |
|---|
|
|---|
Tony's code, modified only by changing variable types in two places based on his post advising of this correction is listed here:
VBA Code:
Option Explicit
Sub Control()
' Using constants instead of literals has the following effects:
' 1) It takes longer to type the code. For example:
' ValueMin = .Range(CellSrcMin).Value takes longer to type than
' ValueMin = .Range("C3").Value
' 2) The code is self-documenting. The purpose of ".Range(CellSrcMin).Value"
' is a lot more obvious than the purpose of ".Range("C3").Value". This may
' not matter today but, when you return to this macro in 6 months, self-
' documenting code is a real help.
' 3) If a cell address, a column code or a worksheet name changes, all you
' have to do is change the value of the constant and the code is fixed.
' Scanning you code for every occurance of a literal and deciding if it
' one that needs to change is a nightmare.
Const CellSrcMin As String = "C3"
Const CellSrcMax As String = "D3"
Const ColRsltValue As String = "A"
Const ColRsltKeyExpn As String = "B"
Const ColSrcKey As String = "A"
Const ColSrcValue As String = "B"
Const RowSrcDataFirst As Long = 2
Const WshtNameRslt As String = "Result"
Const WshtNameSrc As String = "Source"
Dim InxResultCrnt As Long
Dim InxResultPartCrnt As Long
Dim InxSrcRowCrnt As Long
Dim RowRsltCrnt As Long
Dim RowSrcCrnt As Long
Dim RowSrcDataLast As Long
Dim SrcRows() As String
Dim Result() As String
Dim ResultPart() As String
Dim ValueCrnt As Double
Dim ValueKey As String
Dim ValueMin As Double
Dim ValueMax As Double
' Find last row containing data
With Worksheets(WshtNameSrc)
RowSrcDataLast = .Cells(Rows.Count, ColSrcKey).End(xlUp).Row
End With
' Rows RowSrcDataFirst to RowSrcDataLast contain data.
' Size SrcRows so it can hold each value in this range
ReDim SrcRows(1 To RowSrcDataLast - RowSrcDataFirst + 1)
' Fill SrcRows with every row that contains data
RowSrcCrnt = RowSrcDataFirst
For InxSrcRowCrnt = 1 To UBound(SrcRows)
SrcRows(InxSrcRowCrnt) = RowSrcCrnt
RowSrcCrnt = RowSrcCrnt + 1
Next
' Generate every possible combination
Call GenerateCombinations(SrcRows, Result, "|")
' Output contents of Result to Immediate Window.
' Delete or comment out once you fully understand what
' GenerateCombinations is doing.
Debug.Print "Inx Combination"
For InxResultCrnt = 0 To UBound(Result)
Debug.Print Right(" " & InxResultCrnt, 3) & " " & Result(InxResultCrnt)
Next
' Get the minimum and maximum values
With Worksheets(WshtNameSrc)
ValueMin = .Range(CellSrcMin).Value
ValueMax = .Range(CellSrcMax).Value
End With
' Initialise result worksheet
With Worksheets(WshtNameRslt)
.Cells.EntireRow.Delete
With .Range("A1")
.Value = "Total"
.HorizontalAlignment = xlRight
End With
.Range("B1").Value = "Key Expn"
.Range("A1:B1").Font.Bold = True
' This value will be overwritten if any combination gives an acceptable value
.Range("A2").Value = "No combination gives a value in the range " & _
ValueMin & " to " & ValueMax
End With
RowRsltCrnt = 2
With Worksheets(WshtNameSrc)
' Get the minimum and maximum values
ValueMin = .Range(CellSrcMin).Value
ValueMax = .Range(CellSrcMax).Value
' For each result except first which is no row selected
For InxResultCrnt = 1 To UBound(Result)
ResultPart = Split(Result(InxResultCrnt), "|")
ValueCrnt = 0#
For InxResultPartCrnt = 0 To UBound(ResultPart)
ValueCrnt = ValueCrnt + .Cells(ResultPart(InxResultPartCrnt), ColSrcValue).Value
Next
If ValueMin <= ValueCrnt And ValueMax >= ValueCrnt Then
' This value within acceptable range
Worksheets(WshtNameRslt).Cells(RowRsltCrnt, ColRsltValue) = ValueCrnt
' Create key string
ValueKey = .Cells(ResultPart(0), ColSrcKey).Value
For InxResultPartCrnt = 1 To UBound(ResultPart)
ValueKey = ValueKey & "+" & .Cells(ResultPart(InxResultPartCrnt), ColSrcKey).Value
Next
Worksheets(WshtNameRslt).Cells(RowRsltCrnt, ColRsltKeyExpn) = ValueKey
RowRsltCrnt = RowRsltCrnt + 1
End If
Next
End With
End Sub
Sub GenerateCombinations(ByRef Value() As String, ByRef Result() As String, _
ByVal Sep As String)
' * On entry, array Value contains values. For example: A, B, C.
' * On exit, array Result contains one entry for every possible combination
' of values from Value. For example, if Sep = "|":
' 0) ' None of the values is an allowable combination
' 1) A
' 2) B
' 3) A|B
' 4) C
' 5) A|C
' 6) B|C
' 7) A|B|C
' * The bounds of Value can be any valid range,
' * The lower bound of Result will be zero. The upper bound of Result
' will be as required to hold all combinations.
Dim InxRMax As Long ' Maximum used entry in array Result
Dim InxVRCrnt As Long ' Working index into arrays Value and InxResultCrnt
Dim NumValues As Long ' Number of values
Dim InxResultCrnt() As Long ' Entry = 1 if corresponding value
' selected for this combination
NumValues = UBound(Value) - LBound(Value) + 1
ReDim Result(0 To 2 ^ NumValues - 1) ' One entry per combination
ReDim InxResultCrnt(LBound(Value) To UBound(Value)) ' One entry per value
' Initialise InxResultCrnt for no values selected
For InxVRCrnt = LBound(Value) To UBound(Value)
InxResultCrnt(InxVRCrnt) = 0
Next
InxRMax = -1
Do While True
' Output current result
InxRMax = InxRMax + 1
If InxRMax > UBound(Result) Then
' There are no more combinations to output
Exit Sub
End If
Result(InxRMax) = ""
For InxVRCrnt = LBound(Value) To UBound(Value)
If InxResultCrnt(InxVRCrnt) = 1 Then
' This value selected
If Result(InxRMax) <> "" Then
Result(InxRMax) = Result(InxRMax) & Sep
End If
Result(InxRMax) = Result(InxRMax) & Value(InxVRCrnt)
End If
Next
' Treat InxResultCrnt as a little endian binary number
' and step its value by 1. Ignore overflow.
' Values will be:
' 000000000
' 100000000
' 010000000
' 110000000
' 001000000
' etc
For InxVRCrnt = LBound(Value) To UBound(Value)
If InxResultCrnt(InxVRCrnt) = 0 Then
InxResultCrnt(InxVRCrnt) = 1
Exit For
Else
InxResultCrnt(InxVRCrnt) = 0
End If
Next
Loop
End Sub
Finally, a link to the version of the file I used is here if you want to start with it:
Shared with Dropbox
www.dropbox.com
