Awkward autofilter

[AudiaDefila]

Hi there I am abit of a beginner.
A B
C D E F
company 1 Traffic Street Dundee 1 11/11/2006 822.03
company 1 Traffic Street Dundee 2 11/11/2006 1092.29
company 1 Traffic Street Dundee 3 11/11/2006 94.14
company 1 Traffic Street Dundee 4 10/11/2006 298.29
company 1 Traffic Street Dundee 5 10/11/2006 191.33
company 1 Traffic Street Dundee 6 10/11/2006 1145.8
company 1 Traffic Street Dundee 7 04/11/2006 9760.44
company 1 Traffic Street Dundee 8 04/11/2006 888.01
company 1 Traffic Street Dundee 9 29/10/2006 181.76
company 1 Traffic Street Dundee 10 30/10/2006 182.76
C0 2 Car Road London 1 11/11/2006
179.19
C0 2 Car Road London 2 05/11/2006
212.85
C0 2 Car Road London 3 29/10/2006
2208.91
C0 2 Car Road London 4 29/10/2006 74.75
C0 2 Car Road London 5 27/10/2006
2567.61
C0 2 Car Road London 6 30/09/2006
74.75
C0 3 Long Road Crewe 7 13/09/2006
2208.91
C0 3 Long Road Crewe 8 26/08/2006 150.4
C0 3 Long Road Crewe 9 16/08/2006 179.19
C0 3 Long Road Crewe 10 13/08/2006
307.51


I have an excel file of invoice lines from different companies.
I have written a macro to filter the duplicate details into a new
sheet.
However I need to get the invoice lines from their vertical position
into the horizontal format below. The maximum my sheet will hold is 10
invoice details.

Therefore I need an if statement which will check if the client ref in
the row below is the same. as the row above. If it is then it copies
the columns D, E,F to the row with the same client reference on
columns.


Many thanks

Mazzi

1 Ref, 1 Date, 1 Amount, 2 Ref, 2
Date, 2 Amount, ....UP TO TEN
company 1 1 11/11/2006 822.03 2 11/12/2006
1092.29
C0 2 1 16/11/2006 82.03 2 11/12/2006 1092.29

 

[Bob Phillips]

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

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 3 Step -1
If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
.Cells(i, "D").Resize(, iLastCol - 3).Copy .Cells(i - 1,
"G")
.Rows(i).Delete
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AudiaDefila]

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

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 3 Step -1
If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
.Cells(i, "D").Resize(, iLastCol - 3).Copy .Cells(i - 1,
"G")
.Rows(i).Delete
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)









1 Ref, 1 Date, 1 Amount, 2 Ref, 2 Date, 2 Amount, (UP TO TEN)
Co 1 1 11/11/2006 822.03 2 11/12/2006 1092.29
Co 2 1 16/11/2006 82.03 2 11/12/2006 102.29

- Show quoted text -

Hey thanks very much for your help on autofilter. Just to clarify I am
running Excel 2003.

I receive an error 1004 when I run this code which I assume was
concerning this -
http://support.microsoft.com/kb/210684.

It occurs at this line of code:-

I tried to save the workbook in the macro which ran 424 Error.

Also I would like the macro to paste into specific columns after this
line of code.

Many thanks again!

Marian