Deleting rows that are equal amts but different signs
 

Hi,

i have a long list of data where there a SOME rows with equal amounts but
with opposite signs, ie, row 3 may have amount 1234 and row 14 is -1234. I
want to have these equal but opposite amounts deleted from my list. The rows
may or may not have same invoice number, ie, row 3 may have invoice number
321 but row 14 may have invoice number 321A (or 03210 etc).

What is the fastest way to do this?

Thanks,
Dolphin

Deleting rows that are equal amts but different signs
 

This will take some doing, because of the mix of numeric entries and
alphanumeric entries. Step one will be: make a backup copy of your
file so you can practice this and potentially make mistakes without
fear of a catastrophic data loss.

Let's call the column with the numbers you want to compare the
"comparison column". Insert a "helper" column and number each row
starting at 1 with a different number in each row. There are several
ways to do this, but when you're finished each entry in this column
should be static- that is to say, NOT a formula. After you perform the
row deletes this column will allow you to sort the data back into its
original order. If this is not important you can skip this step.

Next, insert another helper column, and copy and paste as values the
entries in the comparison column. Highlight this column, and run this
macro (which I found in a previous post from Gord Dibbon- many thanks,
Gord!). This will remove the alpha characters, leaving only numeric
entries.

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
' if don't want decimals, delete the decimal pt. from "[0-9.]" befroe
running
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Next, insert another helper column; in the first row enter this
formula:
=ABS(cell reference)
where cell reference is the column you just removed the alpha
characters from. The ABS function returns the absolute value of each
entry, and will allow you to compare -1234 and 1234. Copy and paste
this column over itself as values; highlight all rows and sort on this
column.

Next, insert a new column and enter this formula in the first row, then
copy and paste into remaining rows:
=IF(OR(B2=B1,B2=B3),"xxx","")
You'll need to adjust the cell references. This formula returns the
entry xxx if a cell matches the cell above or below it. This is how
you'll find repeat entries. Copy and paste this column over itself as
values; highlight all rows and sort on this column.

All the xxx rows should now be grouped together: delete the xxx rows.
Note that this will delete all the repeated entries, as your post
stated; I'm bringing this up because a lot of times people want to
leave only one instance of repeated numbers.

Finally, sort on the column of numbers from step one to return the data
to its original order, and delete all the helper columns you added.