+ Reply to Thread
Results 1 to 28 of 28

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

  1. #1
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    Exclamation 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.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this …


    As a VBA beginner starter :

    PHP Code: 
    Sub Demo1()
            
    Dim VWR&, 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 ")")
            For 
    1 To .Count 1
                
    If .Item(R).Interior.ColorIndex xlNone Then
                        A 
    W(R1)
                        
    V(R1)
                        
    1
                        S 
    = -Sgn(C)
                    For 
    M To .Count
                        
    If .Item(N).Interior.ColorIndex xlNone Then
                            
    If Sgn(V(N1)) = S Then
                                   A 
    "," W(N1)
                                   
    V(N1)
                                If 
    0 Then
                                    X 
    = (1Mod 7
                                    Range
    (A).Interior.ColorIndex 34 X
                                    
    Exit For
                                ElseIf 
    Sgn(C) = S Then
                                    A 
    W(R1)
                                    
    V(R1)
                                    
    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 » !
    Last edited by Marc L; 04-17-2020 at 08:26 AM. Reason: optimization …

  5. #5
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    Smile 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
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

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

    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow 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. #8
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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. #9
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow 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 …
    Last edited by Marc L; 04-23-2020 at 10:23 AM. Reason: typo …

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question


    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. #12
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question 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. #14
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    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. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question

    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 ?
    Last edited by Marc L; 04-28-2020 at 10:52 AM.

  16. #16
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow 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. #18
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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. #19
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

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

    Hi Marc,

    Please could you provide the solution for the above.

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Knock off Single amounts against multiple amount (Similar and dissimilar)


    Quote Originally Posted by sujit.anusha.uchil View Post
    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 …


    Quote Originally Posted by sujit.anusha.uchil View Post
    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 …
    Last edited by Marc L; 05-04-2020 at 01:26 PM.

  21. #21
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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. #22
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Knock off Single amounts against multiple amount (Similar and dissimilar)


    Quote Originally Posted by sujit.anusha.uchil View Post
    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 …

  23. #23
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool


    • 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 
    F To UBound(A(K))
               
    A(K)(R)
            If 
    0 Then
               S 
    "," A(2)(R):  A(K)(R) = False:  A(2)(R) = False:  Exit For
            ElseIf 
    0 Then
                
    If M Then Exit For
                
    ZSum KSZM11
                
    If "" Then S "," A(2)(R): A(K)(R) = FalseA(2)(R) = False: Exit For
            
    End If
        
    Next
    End Sub

    Sub Demo2
    ()
      Const 
    12
        Dim T
    !, R&, D%(1), F(), C(), U&, K%, S$, N&, X(), L%, E(), M&, P$, B$, Z!(3)
            
    Timer
        With Range
    ("A8", [B7].End(xlDown)).Columns
                  R 
    Application.CountIf(.Item(H), "<0"):  If Or = .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), xlAscendingHeader:=xlNo
            With 
    .Rows("1:" R).Columns
                  A
    (1) = Filter(Application.Transpose(Evaluate("-" & .Item(1).Address)), FalseFalse)
                  
    A(3) = Filter(Application.Transpose(.Item(2)), FalseFalse)
            
    End With
            With 
    .Rows(":" & .Rows.Count).Columns
                 
    .Sort .Item(1), xlDescending
                  A
    (0) = Filter(Application.Transpose(.Item(1)), FalseFalse)
                  
    A(2) = Filter(Application.Transpose(.Item(2)), FalseFalse)
            
    End With
                 
    .Clear
            End With
                F 
    = Array("* @_w""_W@ ""0.000s")
                
    = Array(4715817352236374038424443244539)
                
    UBound(C)
                
    = -(UBound(A(1)) > UBound(A(0)))
            For 
    0 To UBound(A(K))
                    
    ZSum 1 KSA(K)(R), 1
                
    If "" Then
                   
    .Range(A(2)(R) & S).Interior.ColorIndex C(U)
                    
    A(K)(R) = False:  A(2)(R) = False:  1:  ""
                    
    A(K) = Filter(A(K), FalseFalse):  A(K) = Filter(A(K), FalseFalse)
                
    End If
            
    Next
                A
    (K) = Filter(A(K), FalseFalse):  A(2) = Filter(A(2), FalseFalse)
                
    Evaluate(Replace("IF(ISNUMBER(#),#,3)""#"Cells(2).Resize(, 2).Address))
                
    = -(Cells(H) = "-"):  1
            
    For K To 1 K Step Sgn(0.1 K)
                    If 
    UBound(A(0)) < Or UBound(A(1)) < 0 Then Exit For Else 1
                
    If X(1) <> 1 Then
                    E 
    = Array(Chr(67 K), Chr(68 K)):  0:  ""
                    
    "       Phase " "   -   " UBound(A(K)) + " " E(0) & _
                        
    "r amounts to reconciliate " UBound(A(K)) + " " E(1) & "r sums   -   #"
                
    For 0 To UBound(A(K))
                        
    Application.StatusBar P
                        Z
    (1) = Z(2):  Z(2) = Z(3):  Z(3) = Timer
                        ZSum 1 
    KSA(K)(R), X(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 "   -   Count average  :  " Format(Z(0) / (1), F(2))
                    If 
    "" Then
                              M 
    1:  1
                        With 
    .Range(A(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(00)
                        
    End With
                            A
    (K)(R) = False:  A(2)(R) = False:  ""
                            
    A(K) = Filter(A(K), FalseFalse):  A(K) = Filter(A(K), FalseFalse)
                    
    End If
                
    Next
                    A
    (K) = Filter(A(K), FalseFalse):  A(2) = Filter(A(2), FalseFalse):  Erase Z
                End 
    If
            
    Next
        End With
            T 
    Timer T:  If 0 Then T 86400
            With Application
    :  .StatusBar False:  .ScreenUpdating True:  End With
            S 
    Format(TF(2)):  UBound(A(0)) + 1:  UBound(A(1)) + 1
            MsgBox 
    "Reconciliation #  :  " vbLf vbLf " amounts left  :" vbLf vbLf _
                   
    "Dr #  :  " vbLf vbLf "Cr #  :  " UvbInformation"Done in " S
            Erase A
    :  Debug.Print SX(1); "/"X(2); IIf(Cells(H) = "-"" C"""), NURU
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄

  24. #24
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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

  25. #25
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    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 …

  26. #26
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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.

  27. #27
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow 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 !

    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 …

  28. #28
    Registered User
    Join Date
    04-01-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    14

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Subtracting amounts from one column from single amount
    By lavr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2016, 06:39 PM
  2. Replies: 2
    Last Post: 11-27-2013, 12:06 PM
  3. [SOLVED] Generate unique id for similar amounts
    By tanmanoj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-27-2013, 07:19 PM
  4. [SOLVED] Calculating amounts in a single currency from a list of multiple currency amounts
    By Romsky in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-03-2013, 12:22 PM
  5. Replies: 3
    Last Post: 01-30-2013, 07:59 PM
  6. Multiple lines of similar data to one single line
    By T_Van in forum Excel General
    Replies: 7
    Last Post: 02-27-2012, 06:03 PM
  7. Add similar name amounts
    By georgei in forum Excel General
    Replies: 3
    Last Post: 11-05-2009, 10:14 AM

Tags for this Thread

Bookmarks

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