Compare columns on one sheet to another and delete if negative values found
Hello,
I am trying to create a code to compare Column A and Column B on sheet "Removal of Offsets" to Column J on "GL Activity" - if the string is found I need to compare on sheet "GL Activity" - column F "Account Name", column G "Account Identifier" are the same and Column V - "Base Transaction Amount" to see if there is a negative value for the corresponding entry exists and if all the criteria are met then delete both line as they should net out.
Example:
Sheet - Removal of Offsets
Column A Column B
IMS_TAXPRP+ IMS_TAXPR+
Sheet GL Activity
Column F Column G Column K Column V
ABC Bank 123456 IMS_TAXPRP+ 276.25
ABC Bank 123456 IMS_TAXPR+ -276.25
Both of these entries would be deleted off of the GL Activity Sheet since they offset one another value wise.
Re: Compare columns on one sheet to another and delete if negative values found
Never mind, I see that it is more complex than I orginally thought.
Last edited by JLGWhiz; 09-04-2019 at 03:47 PM.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Re: Compare columns on one sheet to another and delete if negative values found
Originally Posted by mikey3580
Appreciate the effort! If you have any ideas, on how to accomplish this, I'd appreciate the help once again! Thanks JLG!
Yeah, I am still looking at the layout and trying to come up with an efficient way of making sure that all the credits and debits are matched up. I wrote the other code assuming that there would only be one ledger entry per account, then when I looked and saw it was more like analog journal entries, that code went out the window, so to speak.
Re: Compare columns on one sheet to another and delete if negative values found
Thanks Marc!
Rule 1
If Column A on sheet "Removal of Offsets" is found in column Column J on sheet "GL Activity" then look column B on sheet "Removal of Offsets" and see if then entry is that is referenced from column A on "Removal of Offsets" exists on in column Column J on sheet "GL Activity" also
If Rule 1 is met then Rule 2
Compare on sheet "GL Activity" - column F "Account Name", column G "Account Identifier" are the same for each of the Column A from sheet "Removal of Offsets" and Column B from sheet "Removal of Offsets".
If Rule 2 is met then Rule 3
Compare on sheet "GL Activity" - Column V - "Base Transaction Amount" to see if there is a negative value for each of the Column A from sheet "Removal of Offsets" and Column B from sheet "Removal of Offsets"
Finally - if all the criteria are met then delete both lines
Example:
Sheet - "Removal of Offsets"
Column A Column B
IMS_TAXPRP+ IMS_TAXPR+
Sheet "GL Activity"
Column F Column G Column K Column V
ABC Bank 123456 IMS_TAXPRP+ 276.25
ABC Bank 123456 IMS_TAXPR+ -276.25
Both of these entries would be deleted off of the "GL Activity" Sheet since Column K matches the code from Columns A and B from "Removal of Offsets". Additionally when compared to each other from the "GL Activity" Sheet they have the same Column F, same Column G, and are recipricals of one another in value and offset each other.
File attached can show you what it is and what it should look like end result.
Re: Compare columns on one sheet to another and delete if negative values found
This made me use brain cells I haven't used for years, but I think it will now work correctly. It might take a few seconds to run in your actual file if it has a lot of data in the two sheets.
A starter demonstration to paste to the GL Activity worksheet module :
PHP Code:
Sub Demo1b() Dim V, C&, W, Y%(), L&, R& V = Sheets("Removal of Offsets").UsedRange.Value2 C = Me.UsedRange.Rows.Count W = Application.Index(Me.UsedRange, Evaluate("ROW(1:" & C & ")"), [{7,10,22}]) ReDim Y(1 To C, 0) With CreateObject("Scripting.Dictionary") For L = 2 To UBound(V): .Item(V(L, 1)) = V(L, 2): Next For L = 2 To C - 1 If .Exists(W(L, 2)) And Y(L, 0) = 0 Then V = .Item(W(L, 2)) For R = L + 1 To C If W(R, 1) = W(L, 1) And W(R, 2) = V And W(R, 3) = -W(L, 3) And Y(R, 0) = 0 Then Y(L, 0) = 1 Y(R, 0) = 1 Exit For End If Next End If Next .RemoveAll End With If IsNumeric(Application.Match(1, Y, 0)) Then Application.ScreenUpdating = False Me.UsedRange.Columns(25).Value2 = Y With Me.UsedRange .Sort [Y1], xlAscending, Header:=xlYes .Rows(Application.Match(1, .Columns(25), 0) & ":" & C).Clear .Columns(25).Clear End With Application.ScreenUpdating = True End If End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 09-04-2019 at 10:15 PM.
Reason: optimization if executed twice …
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by mikey3580
Bookmarks