Looping and deleting thru cells which values offset
 

I have a large file, part number in column A and dollars in column B. I need
to delete all the transactions related to the same part which value is an
offset of another transaction.

When I sort it by Absolute number, itll put the negatives quantities first
and then the positive. When there are single occurrences for both cases
(+/-), I can delete them easily. The problem arises when I have lets say
groups of 3 negatives and 3 positives or even worse 3 negatives and 4
positives. There is no other field to associate them for sorting.

Any thoughts?







Sub offset()

For j = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row To 2 Step -1

If Cells(j,1).Value = Cells(j - 1, 1).Value Then

If Cells(j, 2).Value = -1 * (Cells(j - 1, 2).Value) Then

Cells(j, 1).Rows.EntireRow.Delete

End If

End If

Next j

End Su

Looping and deleting thru cells which values offset
 

=IF(B1<0,1,2) in Col C will mark all negative numbers as 1 and positives as
2. A sort on col C will put all negatives first, bundled together. Could
that help you?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"LuisE" wrote:

I have a large file, part number in column A and dollars in column B. I need
to delete all the transactions related to the same part which value is an
offset of another transaction.

When I sort it by Absolute number, itll put the negatives quantities first
and then the positive. When there are single occurrences for both cases
(+/-), I can delete them easily. The problem arises when I have lets say
groups of 3 negatives and 3 positives or even worse 3 negatives and 4
positives. There is no other field to associate them for sorting.

Any thoughts?







Sub offset()

For j = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row To 2 Step -1

If Cells(j,1).Value = Cells(j - 1, 1).Value Then

If Cells(j, 2).Value = -1 * (Cells(j - 1, 2).Value) Then

Cells(j, 1).Rows.EntireRow.Delete

End If

End If

Next j

End Su