| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Awkward autofilter
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Awkward autofilter
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) wrote in message ups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Awkward autofilter
On May 24, 5:32 pm, "Bob Phillips" wrote:
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) wrote in message ups.com... 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) 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:- Cells(i, "D").Resize(, iLastCol - 3).Copy .Cells(i - 1,"G") 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
| 2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| 2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| Awkward sumif/sumproduct with three criteria over two ranges | Excel Worksheet Functions | |||
| Awkward autofilter question (URGENT!) | Excel Programming | |||
Hybrid Mode
