Hi,

I am working on the following file in excel.


A D
1 -40.07 49085
2 -13,320.00 49086
3 13,320.00 49086
4 -2,000.00 49089
5 -50.00 49091
6 50.00 49091
7 -4,154.40 49092
8 -65.00 49093


I am trying to create a macro that will delete two lines if thet have
the same batch number (column D) and opposite amount in column A.


For example my lines 2 and 3 should be deleted and my lines 5 and 6
should be deleted too.


Thank you for your help


Virginie

Im at work and can't really test it, but this should get you on the
right track...


Sub DeleteRows()

'declare variables
Dim r As Range
Dim rngD As Range

Set rngD = Range("D:D")

For Each r In rngD
'check to see if we have hit the end of
' the list and exit gracefully
If Len(r.Value) = 0 Then Exit For

'See if the current cell value matches
' the cell above's value
If r.Value = ActiveCell.Offset(-1, 0).Value Then
'begin checking to see if the value of
' the cell in the A column have
' similar values
If Abs(ActiveCell.Offset(0, -3).Value) =
Abs(ActiveCell.Offset(-1, -3).Value) Then
'the cells in A have the same or opposite values
ActiveCell.EntireRow.Delete
End If
End If
Next r

End Sub


Hope that helps.

theSquirrel




On Dec 12, 1:04 pm, "Virginie" wrote:
Hi,

I am working on the following file in excel.

A D
1 -40.07 49085
2 -13,320.00 49086
3 13,320.00 49086
4 -2,000.00 49089
5 -50.00 49091
6 50.00 49091
7 -4,154.40 49092
8 -65.00 49093

I am trying to create a macro that will delete two lines if thet have
the same batch number (column D) and opposite amount in column A.

For example my lines 2 and 3 should be deleted and my lines 5 and 6
should be deleted too.

Thank you for your help

Virginie

Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, TEST_COLUMN).Value = Cells(i - 1, TEST_COLUMN).Value
Then
Cells(i, TEST_COLUMN).EntireRow.Delete
End If
Next i

End With

End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
ps.com...
Hi,

I am working on the following file in excel.


A D
1 -40.07 49085
2 -13,320.00 49086
3 13,320.00 49086
4 -2,000.00 49089
5 -50.00 49091
6 50.00 49091
7 -4,154.40 49092
8 -65.00 49093


I am trying to create a macro that will delete two lines if thet have
the same batch number (column D) and opposite amount in column A.


For example my lines 2 and 3 should be deleted and my lines 5 and 6
should be deleted too.


Thank you for your help


Virginie

Hi

none of them seam to work (for the 2 scenarios) nothing is happening.
(I do not know much about macros). I do not have any errors (I debugged
a couple) but my excel sheet is not changing.

Could you help?

Virginie

Can you post a workbook somewhere, one of the free web posting facilities?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
oups.com...
Hi

none of them seam to work (for the 2 scenarios) nothing is happening.
(I do not know much about macros). I do not have any errors (I debugged
a couple) but my excel sheet is not changing.

Could you help?

Virginie

Hi,

the excel file is posted here

www.talmard.com/Virginie/fichier.xls

Virginie

Hi Virginie,

In your example, the data was sorted. In the workbook it wasn't, hence why
it did nothing.

Also, on the duplicate batch numbers, the descriptions are not the same. Is
it okay just to delete the second (third, etc.) that it comes across? If
not, what rule do we apply?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
oups.com...
Hi,

the excel file is posted here

www.talmard.com/Virginie/fichier.xls

Virginie

I have also found some duplicate batches where the amounts were different.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
oups.com...
Hi,

the excel file is posted here

www.talmard.com/Virginie/fichier.xls

Virginie

Hi,

it is normal that the descriptions are not the same.

What I am trying to do here is a conciliation which is basically to
delete the line that are complementary two by two. Meaning that if I
have a debit and a credit in column A in the same batch in column D it
means that the account is balanced for this transaction so the 2 lines
can be deleted.

If I do not have a credit and a debit of the same amount in the same
bacth, then it is not balanced and the lines should not be deleted.

I do not know if I am clear enough.

Virginie

I tried again with my data sorted and it looks like it work.

I now need to make some verifications (I will do it by hand and see if
I have the same results).

I'll let you know.

Virginie

We can do the deletion without the sort if we have the rules. You seem to be
saying now that we should delete the pair of duplicates, I thought it was
only one of the duplicates (or two of triplicates, etc.).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
ps.com...
I tried again with my data sorted and it looks like it work.

I now need to make some verifications (I will do it by hand and see if
I have the same results).

I'll let you know.

Virginie

Hi

Yes I need to have two of duplicates deleted (we do not delete
triplicates as everything should work by pair).

Another problem in the current macro is that it deletes lines that
should not for example the following three lines

165.00 2006-10-31 SIS - 49114 30740101 49114
2,617.82 2006-10-31 SIS - 49114 30740100 49114
-2,782.82 2006-10-03 200610023010578 SIS 74 INTERAC 49114

none of them should be deleted because I do not have opposite amount in
the same batch number (we need to look at two lines at the same time,
not three). The current macro deletes the second and third line.

For the sorting I basically sort by batch number (column D)

Virginie