Complex Macro request, can someone please help?

[fawnlemur]

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

 

[fawnlemur]

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.

 

[fawnlemur]

anyone

 

[offthelip]

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:

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

 

[fawnlemur]

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:

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