# Knock off Single amounts against multiple amount (Similar and dissimilar)

1. ## Knock off Single amounts against multiple amount (Similar and dissimilar)

I need a formula to knock off a single amount against multiple amounts so that they net to zero. The amounts are usually netted off against multiple similar amounts for eg. 55,000 against 5 amounts of 11,000 . However they might also be cases where dissimilar amounts might net to zero. Please provide a formula to identify and highlight these amounts. Sample excel sheet attached.

2. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi. I would appreciate if someone replies to my problem. Thanks in advance

3. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

This will, most likely, require VBA - I will move the thread to that section, where it may get some traction.

4. ## Try this …

As a VBA beginner starter :

PHP Code:
``` Sub Demo1()         Dim V, W, R&, A\$, C@, M&, S%, N&, X%         Application.ScreenUpdating = False     With Range("D8", [D7].End(xlDown))            .Interior.ColorIndex = xlNone             V = .Value2             W = Evaluate("""D""&ROW(8:" & .Count + 7 & ")")         For R = 1 To .Count - 1             If .Item(R).Interior.ColorIndex = xlNone Then                     A = W(R, 1)                     C = V(R, 1)                     M = R + 1                     S = -Sgn(C)                 For N = M To .Count                     If .Item(N).Interior.ColorIndex = xlNone Then                         If Sgn(V(N, 1)) = S Then                                A = A & "," & W(N, 1)                                C = C + V(N, 1)                             If C = 0 Then                                 X = (X + 1) Mod 7                                 Range(A).Interior.ColorIndex = 34 + X                                 Exit For                             ElseIf Sgn(C) = S Then                                 A = W(R, 1)                                 C = V(R, 1)                                 N = M                                 M = M + 1                             End If                         End If                     End If                 Next             End If         Next     End With         Application.ScreenUpdating = True End Sub  ```
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

5. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hello Marc,

Thank you so much for the code. It helped me to sort the file that I have enclosed. However over a range of 2000 numbers in another file, the code was only able to identify a number and its opposites for eg. +10000 & -10000 .

I have enclosed the scrubbed file again for your reference. Items in column "F" marked in yellow were the ones that were highlighted using the macro above. Combinations of green and blue were not identified by the macro. Please could you help me further on this

7. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

My demonstration was made upon your original attachment so only for close / following numbers to link (avoids time execution lasting too long)
but in your last attachment it can't find any link when there is a gap between …

With a smart journal it's easier with at least an operation ID, an invoice #, … whatever but clever than just numbers alone !

8. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Alan,

The solver did not work over a 2000 line item data . Anyway thanks for helping me out.

Regards,
Sujit

9. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc,
Yes there are gaps in between the data. Also as you see there are no clear invoice numbers in the sheet. Hence we have a tedious job of doing it manually. Thank you for your help. If you have an idea on how to further segregate the data, please let me know.

10. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Some ways exist on web but under Excel the more data, the more time consuming,
it can need dozens of minutes, even an hour or more …
As the time execution can be reduced if at least the maximum possible numbers in a sum is well known.

If data come from an export, ask to Dumb or Dumber - the ones managing the original data - to add more
relevant columns (ID, Invoice #, …) obviously just in order to respect the logic of accountancy / financial rules …

11. I found something in one of my old VBA procedures which may be adapted to your need
but I won't waste time without knowing the accurate context …

For example, as your attachments are very different, I must have the real workbook layout
'cause if you change anything like the starting row or the used columns it couldn't work anymore
and as some free cells are necessary to prepare the calculation …

In your last attachment, your green example with both -375000 amounts can be each in other sums combinations
if the priority is the Cr column E for multiple amounts in Dr column D in order to reduce the time execution
as in the other way it may need more than one hour with too much amounts to combine in column E.

After the way to reconciliate the Dr amounts with a Cr sum, the other way should run faster for the Level 2
- Level 2 means 2 Cr amounts to reconciliate a Dr sum -
but for Level 3 it should need between half an hour and an hour (I did quick tests on my slowest computer)
so as your blue example is at Level 5 the execution time may not be reasonable
- the reason why my process uses a max Level for each column -
or if you can let the computer working during the night hoping Excel or Windows won't crash !

Or maybe you may think about a better algorithm or find one on Web …

12. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc,

I have scrubbed and changed the data. But I have kept the integrity with the real workbook layout. I do understand the concern about the longer times required to solve such problems. However having a solution at hand will help me work with an alternative. Can you please provide me with the alternate code which I might be able to apply.

The solution is to be applied on Column L where highlighting needs to be done on offset amounts . I have highlighted (in blue and green) some of the amounts in Column L that were setting off just like the previous example for your reference. Moreover once the solution is applied I would not mind if the amounts are all highlighted in a single color as my idea is to obtain the maximum amounts that net to zero

13. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

I have to proceed to some tests (when I have some time free) as some variations exist,
to see which one could be faster according to your too big amounts # to reconciliate.

Have you an idea of the maximum level, what is the maximum amounts # to sum for an amount of the opposite column ?

Actually I have to enter manually the level for each column.
Starting with column Cr as sum, the level is 7 and after treating the column Dr the level is only 2,
all this process runs in less than 7 minutes on my slowest laptop but if I raise the Dr level to 3,
it needs more than half an hour to find only 8 more combinations !

Maybe it could be turn to an automatic stop by testing if the calculation time is above a limit …

The ultimate way is without any limit but it can take several hours and I won't do this on an old laptop as it may burn !
It's better to have a powerful desktop computer with an efficient cooling system …

14. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc,

The maximum amount in the debit column is 21,00,000. They are made up of 7 amounts of 3 lakh.
The maximum number of values in the credit side (the count) that net off for a single debit amount is 12.
Further apart from the values that knock off each other (equal and opposite individual numbers). The total combinations that knock off each other could be upto 45.

I hope this is what you were asking for. Does this help ? Please let me know.

15. For example I was asking for the maximum number of amounts to sum an amount in the opposite column
as according to your post #5 attachment for the green sample it's 2 and for the blue sample it's 5
so Level 2 & 5, both for the column Dr but on my side I have to limit the Dr column to Level 2
or it takes forever (Excel or Windows crashes before the process is achieved).
The reason why I give the priority to the Cr column first as they are less amounts in column Dr …

So the questions are what is the kind of computer you use, can the procedure gives first the priority to column Cr,
do you want a reasonable time execution - so not performing deeper combinations then the result is partial -
or if you have an enough well cooling powerful desktop computer you want a process performing deeper levels combinations
but it may need hours to achieve the calculation ?

16. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc,

I see a Level 7 item in the list for eg. 300,000*7= 2,100,000. I believe this is the highest level in the sheet, though I am not sure.

On the systems front I have a Core i7, 8th Gen Laptop. I don't have a desktop though.

17. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

I can't have this level 7 on column Dr as sum 'cause it's limited to Level 2 on my side
and as I wrote the priority must be obviously on column Cr as sum 'cause column Dr has less amounts to combine …

I can go deep to level 7 on column Cr as sum only …

As I have differents ways, so it depends on the cooling efficiency of your laptop, so about what you prefer
and the answers to previous questions I'm still expecting, …

18. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc. The level 7 item is on the credit side. Not on the debit side. The debit side can be limited to Level 2 as you said.

19. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc,

Please could you provide the solution for the above.

20. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Originally Posted by sujit.anusha.uchil
The level 7 item is on the credit side. Not on the debit side. The debit side can be limited to Level 2 as you said.
Wrong ! So you misunderstood as you just misread posts #11, 15 & 17, read them again …

Originally Posted by sujit.anusha.uchil
Please could you provide the solution for the above.
I just finish the first tests bunch according to your post #5 attachment.
Once the process parts removed for a smart worksheet
- as yours is very not one, see this more recent thread Macro to Match Items in Col C -
I earn one level for a reasonable calculation time for the column Dr so now it's level 3 meaning 3 Cr amounts for a Dr sum,
without any limit now for column Cr as sum, all this according to your post #5 attachment on an old slow laptop …

Before to fit it to your real workbook like in the post #12 attachment,
I'm still expecting answers to my questions, wanna know your preferences / choices
'cause I can't waste time, once posted I won't revamped it if necessary …

Or if you are enough confident with your Excel / VBA skills
I can post the actual code for post #5 attachment as it is then you are on your own …

21. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi Marc,

Really sorry that I was not able to address your doubts. Maybe my skills in VBA/ programming is not adequate and hence am unable to get your point in particular. Let me try my best again.
1) Sheet in Post #12 is the actual layout of the sheet. I would like the results to appear in Column L (Net Entered)
2) There are several Credit amounts (Column K) for individual Debit amounts (in Column J) as per sheet in Post#12
3) For an individual Debit amount in Column J there could be as much as 7 credit amounts (sum) in Column K. However I believe you said this seems unfeasible on an old laptop or on a slow one. Also this probably leads to a Level 7 item on your debit side while you can go up to a maximum of only level 2 in your system.

I am okay if you are able to give a solution up to level 2 on the debit side. We will do it manually from thereon. So if you can provide a code based on the Sheet in Post #12 it will be helpful.

22. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Originally Posted by sujit.anusha.uchil
Also this probably leads to a Level 7 item on your debit side while you can go up to a maximum of only level 2 in your system.
As I wrote in my previous post I can go now up to level 3 for column Dr as sum, level 4 is so long to combinate …

Ok, I will fit it - later when I could grab the tests laptop - according to post #12 attachment with an automatic way set to level 3
but you may change each column level and give the priority to column Cr just using the top columns cells …

• Important note to readers :

if you need the same, ask first to Dumb or Dumber
- the one who created the data breaking the accoutancy / financial rules -
if some obvious missing column(s) can be added in order to find out the best combination
like in the post #20 link, just warming a couple of neurons according to the job …
So if it's not your job, ask those whose it's theirs !

'Cause as the data are in this thread, the 'combinatorics engine' returns the first matching combination
which has few chance to be the appropriate expected one according to these rules !

As this engine was made to search all combinations for an unique sum among amounts less than a hundred
'cause it may need some time to achieve, so imagine here for searching hundreds of sums among more
than a thousand of amounts, the time necessary can be insane 'cause VBA is very not the best for this !
As any modern accountancy application can do this in a snap, Excel is very not necessary …

So avoiding this kind of engine or with a lower number of amounts according to proper criteria
the execution time can be very reduced, often you do not have the time to totally
release the mouse button when you launch the process, the result already appears !

Here with such data it requires several seconds, minutes or hours (then hoping Excel or Windows won't crash !)
as it depends on how deep the search is done …

Don't post anything here as I won't answer according to forum rules,
neither waste time to send a personal message as my message box is often full
and as I never answer when the message must be in a thread,
so create your own thread with an explanation & an attachment, both smart enough !

• To sujit :

I moved on another computer much more powerful but with older Excel & Windows versions :
it runs slower than the previous tests laptop …

Paste this new VBA demonstration to the worksheet module of your post #12 attachment, try it, like it !

PHP Code:
``` Dim A(3) Sub ZSum(K%, S\$, ByVal C@, Optional M%, Optional ByVal F&, Optional ByVal L% = 1)     Dim R&, Z@     For R = F To UBound(A(K))            Z = C - A(K)(R)         If Z = 0 Then            S = "," & A(K + 2)(R):  A(K)(R) = False:  A(K + 2)(R) = False:  Exit For         ElseIf Z > 0 Then             If L = M Then Exit For             ZSum K, S, Z, M, R + 1, L + 1             If S > "" Then S = S & "," & A(K + 2)(R): A(K)(R) = False: A(K + 2)(R) = False: Exit For         End If     Next End Sub Sub Demo2()   Const H = 12     Dim T!, R&, D%(1), F(), C(), U&, K%, S\$, N&, X(), L%, E(), M&, P\$, B\$, Z!(3)         T = Timer     With Range("A8", [B7].End(xlDown)).Columns               R = Application.CountIf(.Item(H), "<0"):  If R = 0 Or R = .Rows.Count Then Beep: Exit Sub               D(0) = .Cells(2)(0).End(xlToRight).Column + 1:  D(1) = D(0) - H               If .Parent.FilterMode Then .Parent.ShowAllData              .Item(H).Interior.ColorIndex = xlNone              .Item(D(0)).Clear               With Application:  .StatusBar = "       Phase 1   …":  .ScreenUpdating = False:  End With              .Item(D(0)) = .Item(H).Value2         With .Item(D(0)).Resize(, 2)              .Item(2) = Evaluate("""" & Chr(64 + H) & """&ROW(1:" & .Rows.Count & ")")              .Sort .Item(1), xlAscending, Header:=xlNo         With .Rows("1:" & R).Columns               A(1) = Filter(Application.Transpose(Evaluate("-" & .Item(1).Address)), False, False)               A(3) = Filter(Application.Transpose(.Item(2)), False, False)         End With         With .Rows(R + 1 & ":" & .Rows.Count).Columns              .Sort .Item(1), xlDescending               A(0) = Filter(Application.Transpose(.Item(1)), False, False)               A(2) = Filter(Application.Transpose(.Item(2)), False, False)         End With              .Clear         End With             F = Array("* @_w", "_W@ ", "0.000s")             C = Array(4, 7, 15, 8, 17, 35, 22, 36, 37, 40, 38, 42, 44, 43, 24, 45, 39)             U = UBound(C)             K = -(UBound(A(1)) > UBound(A(0)))         For R = 0 To UBound(A(K))                 ZSum 1 - K, S, A(K)(R), 1             If S > "" Then                .Range(A(K + 2)(R) & S).Interior.ColorIndex = C(U)                 A(K)(R) = False:  A(K + 2)(R) = False:  N = N + 1:  S = ""                 A(1 - K) = Filter(A(1 - K), False, False):  A(3 - K) = Filter(A(3 - K), False, False)             End If         Next             A(K) = Filter(A(K), False, False):  A(K + 2) = Filter(A(K + 2), False, False)             X = Evaluate(Replace("IF(ISNUMBER(#),#,3)", "#", Cells(H - 2).Resize(, 2).Address))             K = -(Cells(H) = "-"):  L = 1         For K = K To 1 - K Step Sgn(0.1 - K)                 If UBound(A(0)) < 0 Or UBound(A(1)) < 0 Then Exit For Else L = L + 1             If X(K + 1) <> 1 Then                 E = Array(Chr(67 + K), Chr(68 - K)):  M = 0:  P = ""                 B = "       Phase " & L & "   -   " & UBound(A(1 - K)) + 1 & " " & E(0) & _                     "r amounts to reconciliate " & UBound(A(K)) + 1 & " " & E(1) & "r sums   -   #"             For R = 0 To UBound(A(K))                     Application.StatusBar = B & R + 1 & P                     Z(1) = Z(2):  Z(2) = Z(3):  Z(3) = Timer                     ZSum 1 - K, S, A(K)(R), X(K + 1) * 1                     Z(3) = Timer - Z(3):  If Z(3) < 0 Then Z(3) = Z(3) + 86400                     Z(0) = Z(0) + Z(3):   DoEvents                     If Z(1) + Z(2) + Z(3) < 0.5 Then _                           P = "" Else P = "   -   Count average  :  " & Format(Z(0) / (R + 1), F(2))                 If S > "" Then                           M = M + 1:  N = N + 1                     With .Range(A(K + 2)(R) & S)                          .Interior.ColorIndex = C(N Mod U)                          .Offset(, D(1)).NumberFormat = F(N Mod 2)                          .Offset(, D(1)) = E(1) & Cells(M, .Areas.Count - 1).Address(0, 0)                     End With                         A(K)(R) = False:  A(K + 2)(R) = False:  S = ""                         A(1 - K) = Filter(A(1 - K), False, False):  A(3 - K) = Filter(A(3 - K), False, False)                 End If             Next                 A(K) = Filter(A(K), False, False):  A(K + 2) = Filter(A(K + 2), False, False):  Erase Z             End If         Next     End With         T = Timer - T:  If T < 0 Then T = T + 86400         With Application:  .StatusBar = False:  .ScreenUpdating = True:  End With         S = Format(T, F(2)):  R = UBound(A(0)) + 1:  U = UBound(A(1)) + 1         MsgBox "Reconciliation #  :  " & N & vbLf & vbLf & R + U & " amounts left  :" & vbLf & vbLf & _                "Dr #  :  " & R & vbLf & vbLf & "Cr #  :  " & U, vbInformation, "Done in " & S         Erase A:  Debug.Print S, X(1); "/"; X(2); IIf(Cells(H) = "-", " C", ""), N, R + U, R, U End Sub  ```
► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄

23. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hello Marc,

You are awesome. Thank you so much for the code . It worked marvelously and in a neat 8 minutes. I was able to reconcile 304 more amounts in pretty quick time.

I have just one query. How to change each column level and give the priority to column Cr by using the top columns cells (as mentioned in post 22).

However I am eternally thankful to you Marc for the wonderful piece of code. It could not have been better

24. Thanks for the rep' and your kind appreciation !

8 minutes … With your attachment it's less than 3 on my side but you are lucky
as from the first tests I divided the calculation time by a factor of 4 !
I was wrong yesterday for the more powerful computer as it was on 'silent mode',
now in 'turbo mode' so full power the calculation time is a little bit faster but less than I thought …

• I keep the bottom status bar display from the original process in order to see the execution progress.

• Enlarge the column S at least to 15.

This column contains a reconciliation reference for level 2 and upper combinations.
The first letter is always D for Debit as sum versus C for Credit as sum.
The second letter is the level # aka B for level 2 or J for level 10 for example …

So if you need to see only the rows for a reconciliation just right click on a reference and choose 'Filter this value' …

• The top cells columns are J1:L1. When empty, the default combinations level is 3.

For example 2 in cell J1 limits the column Dr to level 2 (aka a Dr sum = a maximum of 2 Cr amounts)
and zero in cell K1 means no limit for column Cr, on my side this 2 & 0 setup needs 31.6s …

To give the priority to column Cr, enter in cell L1 the minus sign (for negatives).
The 2 & 0 & - setup requires on my side 36.3s …

Obviously, as that's just combinatorics, results are different depending on the calculation setup.

• You can retrieve the message box statistics on VBE side in the Immediate windows (CTRL G) :
the execution time, Dr level / Cr level - if 'C' follows that means priority to column Cr - ,
reconciliation #, amounts left # - maybe the more important to note - , Dr amounts left, Cr amounts left …

The best result on my side is 3 & 0 in 179s.
I tried level 4 for column Dr but the calculation was so long, the laptop so hot so I stopped the process …

• To stop the procedure execution, if the Excel top title bar is normal, without 'do not respond',
you can try to keep a finger on ESC key until the message box appears but sometimes
it appears on VBE side instead of Excel side …

When Excel is on 'do not respond' mode, ESC key can't work
but you can use the usual Windows tasks manager to kill the process …

25. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Hi,

Thanks for the detailed procedure. The 3rd level works like a charm.
I tried my best to work at the 4th Level on Debit side. However it would not work on my system. I guess I need will try on a powerful desktop
Thank you again for all the help. If you have any additional instructions please let me know . Otherwise I guess I can go ahead and close this thread.

26. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

I have nothing more to add about how to use my VBA procedure …

As a reminder, here you are in a dumb mode, the worst case according to the big number of amounts to check,
a no sense according to accountancy / financial rules ! Worse 'cause under Excel, the slowest application I know for this task …
I just apply the same 'combinatorics engine' in a more recent thread but
as an advanced filter reduces first the number of amounts to combinate the result is instant in less than 0.1 second !

a smarter data set with some additional column(s) in order to reduce the time execution …

27. ## Re: Knock off Single amounts against multiple amount (Similar and dissimilar)

Thank you for your valuable time Marc. Hope to get back to you with another query.

Warm Regards,
Sujit

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1