need help eliminating lines where positive/negative values match but other columns are same

[jhmayr]

please help with a macro or VBA to help me process the attached file...it could have 10,000 lines+ in it but just giving this as an example

what I need to do is create another column at the end where I can easily filter out the lines that match based on the following criteria

Here is the basic logic:
Create Column AO and put a "X" in each row where:
Column AJ has a positive/negative # match (IE 353.7 & -353.7)
and
Column A,C,D,E,F are the same

If this worked I would expect the attached file to have an "X" only in rows 2, 3, 10 & 11 - even though rows 4 & 6 match on the $$ they dont match on the date and even though rows 5 & 9 match on the $$ they have a mismatched cost center

Would really appreciate any help here!!

[TABLE="width: 5496"]
<tbody>[TR]
[TD]Period Name[/TD]
[TD]Posted Date[/TD]
[TD]Company[/TD]
[TD]Cost Center[/TD]
[TD]Accounting Code[/TD]
[TD]Description[/TD]
[TD]Product Code[/TD]
[TD]Gl Account String[/TD]
[TD]Je Source[/TD]
[TD]Je Category[/TD]
[TD]JE #[/TD]
[TD]Batch Name[/TD]
[TD]Third Party Number[/TD]
[TD]Third Party Name[/TD]
[TD]Employee Name[/TD]
[TD]Employee Number[/TD]
[TD]Person Type[/TD]
[TD]Po Number[/TD]
[TD]Invoice Num[/TD]
[TD]Transaction Num[/TD]
[TD]Project Number[/TD]
[TD]Task Number[/TD]
[TD]Exp Comments[/TD]
[TD]Expenditure Type[/TD]
[TD]Payment Status[/TD]
[TD]Line Descr[/TD]
[TD]Transaction Date[/TD]
[TD]Ledger Name[/TD]
[TD]Debit Amount[/TD]
[TD]Credit Amount[/TD]
[TD]Net Amount[/TD]
[TD]Currency Code[/TD]
[TD]Sla Line Entered Cr[/TD]
[TD]Sla Line Entered Dr[/TD]
[TD]Sla Line Entered Net[/TD]
[TD]In USD[/TD]
[TD]Functions[/TD]
[TD]Rollups[/TD]
[TD]Sub rollups[/TD]
[TD]BU[/TD]
[/TR]
[TR]
[TD]Mar-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]353.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]308.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40206[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]450[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40206[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]479.34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-450[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]288.46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40202[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-479.34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-308.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar-15[/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40207[/TD]
[TD="align: right"]60600[/TD]
[TD]Air Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-353.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[mole999]

? if Minus 1 and plus 1 cancel each other out, why not just do MATH then deal with the ZEROS

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG04Sep29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oTxt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
oTxt = Join(Application.Index(Dn.Resize(, 6).Value, 0, Array(1, 3, 4, 5, 6)), ",")
[COLOR="Navy"]If[/COLOR] Not .Exists(oTxt) [COLOR="Navy"]Then[/COLOR]
    .Add oTxt, Dn.Offset(, 35)
[COLOR="Navy"]Else[/COLOR]
    [COLOR="Navy"]If[/COLOR] .Item(oTxt) + Dn.Offset(, 35) = 0 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 40) = "x"
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[JLGWhiz]

Try this

Sub Xfiles()
Dim sh As Worksheet, lr As Long, c As Range, rng As Range, fn As Range, rAry As Variant
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, "AJ").End(xlUp).Row
Set rng = sh.Range("AJ2:AJ" & lr)
rAry = Array("A", "C", "D", "E", "F")
    With sh
        For Each c In rng
            Set fn = .Range(c.Offset(1, 0), .Cells(lr, "AJ")).Find(-c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    r1 = c.Row
                    r2 = fn.Row
                    For i = LBound(rAry) To UBound(rAry)
                        If .Cells(r1, rAry(i)) <> .Cells(r2, rAry(i)) Then GoTo SKIP
                    Next
                    c.Offset(0, 5) = "X"
                    fn.Offset(0, 5) = "X"
                End If
SKIP:
        Next
    End With
End Sub