Results 1 to 5 of 5

Thread: Complex Macro request, can someone please help?

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex Macro request, can someone please help?

    Hi guys

    This is a bit too complex for me and I’m not too sure if it’spossible using excel.

    I have 3 columns I want to compare

    Column A = Clients
    Column G = Amounts
    Column N = Case ID


    So let’s say

    Client | Amount | Case ID
    Mike | 50|123
    John | -50 |123
    Mike | 50 |123
    Mike | -50 |123
    John | 20 |456
    John | -20 |456
    Mike| -20 |456


    I want the macro to:


    • Find debits and credits that match with the sameclient and case ID e.g. Mike has go a debit and a credit for 50.00 and john hasgot a credit and debit for 20.00 under the same case ID (Macro is to deletethese transactions (Row))
    • Remove any transactions that don’t have an contra, e.g. once matchinghas been deleted Mike will be left with a credit for 20.00 and the case ID andamount doesn’t match any other transactions (I need this to be deleted)
    • Then I should be left with debits and credits thatmatch for different clients for example John has a credit for 50 and mike has adebit for 50 under the same case ID, so I should be left with:


    Client | Amount | Case ID
    Mike | 50|123
    John | -50 |123

    I’d really appreciate it if someone could help me write a macro to do this, please use Column A, G and N as stated above.

    Thank you
    Last edited by fawnlemur; Jul 10th, 2019 at 01:25 PM.

  2. #2
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Macro request, can someone please help?

    just to add to the above you if 2 debit and 1 credit under the same client and 1 credit under another client for the same case ID, it would remove a debit and a credit that have the same client and leave 1 debit with the first client and 1 credit with the second client.

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Macro request, can someone please help?

    anyone

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    898
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex Macro request, can someone please help?

    your requirements are inconsistent:
    Find debits and credits that match with the sameclient and case ID ..... (Macro is to deletethese transactions
    Remove any transactions that don’t have an contra,
    This would appear to be asking for all transactions to be deleted. However I don't believe this so here is some code that meets the first requirement i.e it will delete matching positive an negative Amounts provided the name and Case Id are the same:
    Code:
    Sub test()
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(1, 1), Cells(lastrow, 14))
    For i = 2 To lastrow
     For j = i To lastrow
      ' check if all three conditions apply to these two rows
      If inarr(i, 1) = inarr(j, 1) And inarr(i, 7) = -inarr(j, 7) And inarr(i, 14) = inarr(j, 14) Then
       Range(Cells(i, 1), Cells(i, 14)) = ""
       Range(Cells(j, 1), Cells(j, 14)) = ""
       ' reload the array because of the deleted data
       inarr = Range(Cells(1, 1), Cells(lastrow, 14))
       Exit For
      End If
     Next j
    Next i
    
    
    End Sub
    Last edited by offthelip; Jul 16th, 2019 at 06:47 PM.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  5. #5
    New Member
    Join Date
    Dec 2018
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Macro request, can someone please help?

    Quote Originally Posted by offthelip View Post
    your requirements are inconsistent:


    This would appear to be asking for all transactions to be deleted. However I don't believe this so here is some code that meets the first requirement i.e it will delete matching positive an negative Amounts provided the name and Case Id are the same:
    Code:
    Sub test()
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(1, 1), Cells(lastrow, 14))
    For i = 2 To lastrow
     For j = i To lastrow
      ' check if all three conditions apply to these two rows
      If inarr(i, 1) = inarr(j, 1) And inarr(i, 7) = -inarr(j, 7) And inarr(i, 14) = inarr(j, 14) Then
       Range(Cells(i, 1), Cells(i, 14)) = ""
       Range(Cells(j, 1), Cells(j, 14)) = ""
       ' reload the array because of the deleted data
       inarr = Range(Cells(1, 1), Cells(lastrow, 14))
       Exit For
      End If
     Next j
    Next i
    
    
    End Sub

    Thank you for your help this takes care of the first part

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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