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.

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

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

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 !

Hi Alan,

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

Regards,
Sujit

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.

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 …

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

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 …

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.

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.

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, …

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.

Hi Marc,

Please could you provide the solution for the above.

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.

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 !
  You will have more information in my next post, maybe after answering to your questions if any …
  
  

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 » ! ◄ ◄

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

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 …

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.

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 !

So your salvation leads to ask to the people creating the data extraction for at least
a smarter data set with some additional column(s) in order to reduce the time execution …

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

Warm Regards,
Sujit