|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 8th, 2009, 07:43 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
Snaking columns in Excel
I wish to prepare a book inventory. Column A is the book title, Column B is
the author. I will sort alphabetically by column B (author), then alphabetically by column A (books will be in alphabetical order for that author). I wish to have a total of four columns on each page, so will add Columns C and D. I want the first two columns to SNAKE to the third and fourth columns. Then, continue in that same format to page 2. Can you help? 
|
|
#2
February 8th, 2009, 08:20 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
Snaking columns in Excel
Hi
Select columns A  and goto Data Sort Set up the sort as desired.Regards, Per "Mac" skrev i meddelelsen ... I wish to prepare a book inventory. Column A is the book title, Column B is the author. I will sort alphabetically by column B (author), then alphabetically by column A (books will be in alphabetical order for that author). I wish to have a total of four columns on each page, so will add Columns C and D. I want the first two columns to SNAKE to the third and fourth columns. Then, continue in that same format to page 2. Can you help?
|
|
#3
February 8th, 2009, 09:42 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
Snaking columns in Excel
After sorting the columns as you like.....................
Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 50, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "C") iSource = iSource + 100 iTarget = iTarget + 51 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord Dibben MS Excel MVP On Sun, 8 Feb 2009 11:43:01 -0800, Mac wrote: I wish to prepare a book inventory. Column A is the book title, Column B is the author. I will sort alphabetically by column B (author), then alphabetically by column A (books will be in alphabetical order for that author). I wish to have a total of four columns on each page, so will add Columns C and D. I want the first two columns to SNAKE to the third and fourth columns. Then, continue in that same format to page 2. Can you help?
|
|
#4
March 2nd, 2009, 06:57 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
Snaking columns in Excel
Gord,
Your macro for snaking columns works great. However, I need the column headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet. Also have a different 2 column I need to snake to 8 columns (4 sets) in the same manner. Each page is 54 rows at the page break. -- Thanks, Kevin "Gord Dibben" wrote: After sorting the columns as you like..................... Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 50, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "C") iSource = iSource + 100 iTarget = iTarget + 51 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord Dibben MS Excel MVP On Sun, 8 Feb 2009 11:43:01 -0800, Mac wrote: I wish to prepare a book inventory. Column A is the book title, Column B is the author. I will sort alphabetically by column B (author), then alphabetically by column A (books will be in alphabetical order for that author). I wish to have a total of four columns on each page, so will add Columns C and D. I want the first two columns to SNAKE to the third and fourth columns. Then, continue in that same format to page 2. Can you help?
|
|
#5
March 2nd, 2009, 08:26 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
Snaking columns in Excel
Revised.................you do the math if number of rows per set is not
correct Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 2 iTarget = 2 Range("A1:B1").Copy Range("C1:H1") Do Cells(iSource, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 54, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "C") Cells(iSource + 108, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "E") Cells(iSource + 162, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 216 iTarget = iTarget + 55 'insert a blank row Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier wrote: Gord, Your macro for snaking columns works great. However, I need the column headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet. Also have a different 2 column I need to snake to 8 columns (4 sets) in the same manner. Each page is 54 rows at the page break.
|
|
#6
March 3rd, 2009, 12:02 AM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
Snaking columns in Excel
Assume, works great!
Thank you very much, Kevin "Gord Dibben" wrote: Revised.................you do the math if number of rows per set is not correct Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 2 iTarget = 2 Range("A1:B1").Copy Range("C1:H1") Do Cells(iSource, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 54, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "C") Cells(iSource + 108, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "E") Cells(iSource + 162, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 216 iTarget = iTarget + 55 'insert a blank row Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier wrote: Gord, Your macro for snaking columns works great. However, I need the column headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet. Also have a different 2 column I need to snake to 8 columns (4 sets) in the same manner. Each page is 54 rows at the page break.
|
|
#7
November 4th, 2009, 08:36 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
code
how do you get rid of the blank row?
Gord Dibben wrote: Revised................. 02-Mar-09 Revised.................you do the math if number of rows per set is not correct Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 2 iTarget = 2 Range("A1:B1").Copy Range("C1:H1") Do Cells(iSource, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 54, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "C") Cells(iSource + 108, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "E") Cells(iSource + 162, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 216 iTarget = iTarget + 55 'insert a blank row Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier wrote: Previous Posts In This Thread: On Sunday, February 08, 2009 2:43 PM Ma wrote: Snaking columns in Excel I wish to prepare a book inventory. Column A is the book title, Column B is the author. I will sort alphabetically by column B (author), then alphabetically by column A (books will be in alphabetical order for that author). I wish to have a total of four columns on each page, so will add Columns C and D. I want the first two columns to SNAKE to the third and fourth columns. Then, continue in that same format to page 2. Can you help? On Sunday, February 08, 2009 3:20 PM Per Jessen wrote: Snaking columns in Excel Hi Select columns A and goto Data Sort Set up the sort as desired.Regards, Per On Sunday, February 08, 2009 4:42 PM Gord Dibben wrote: After sorting the columns as you like..................... After sorting the columns as you like..................... Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 50, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "C") iSource = iSource + 100 iTarget = iTarget + 51 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord Dibben MS Excel MVP On Sun, 8 Feb 2009 11:43:01 -0800, Mac wrote: On Monday, March 02, 2009 1:57 PM AFSSkie wrote: Gord,Your macro for snaking columns works great. Gord, Your macro for snaking columns works great. However, I need the column headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet. Also have a different 2 column I need to snake to 8 columns (4 sets) in the same manner. Each page is 54 rows at the page break. -- Thanks, Kevin "Gord Dibben" wrote: On Monday, March 02, 2009 3:26 PM Gord Dibben wrote: Revised................. Revised.................you do the math if number of rows per set is not correct Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 2 iTarget = 2 Range("A1:B1").Copy Range("C1:H1") Do Cells(iSource, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 54, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "C") Cells(iSource + 108, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "E") Cells(iSource + 162, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 216 iTarget = iTarget + 55 'insert a blank row Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier wrote: On Monday, March 02, 2009 7:02 PM AFSSkie wrote: Snaking columns in Excel Assume, works great! Thank you very much, Kevin "Gord Dibben" wrote: EggHeadCafe - Software Developer Portal of Choice Seamless Data Compression in .NET http://www.eggheadcafe.com/tutorials...mpression.aspx
|
|
#8
November 4th, 2009, 08:45 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
code
Before or after?
To not have a blank row inserted change this line iTarget = iTarget + 55 'insert a blank row iTarget = iTarget + 54 If you have already run the macro and have the blank lines........... Select a column then F5SpecialBlanksOKEditDeleteEntire row. Gord On Wed, 04 Nov 2009 12:36:18 -0800, craig m wrote: how do you get rid of the blank row? Gord Dibben wrote: Revised................. 02-Mar-09 Revised.................you do the math if number of rows per set is not correct Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 2 iTarget = 2 Range("A1:B1").Copy Range("C1:H1") Do Cells(iSource, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 54, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "C") Cells(iSource + 108, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "E") Cells(iSource + 162, "A").Resize(54, 2).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 216 iTarget = iTarget + 55 'insert a blank row Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord On Mon, 2 Mar 2009 10:57:03 -0800, AFSSkier wrote: Previous Posts In This Thread: On Sunday, February 08, 2009 2:43 PM Ma wrote: Snaking columns in Excel I wish to prepare a book inventory. Column A is the book title, Column B is the author. I will sort alphabetically by column B (author), then alphabetically by column A (books will be in alphabetical order for that author). I wish to have a total of four columns on each page, so will add Columns C and D. I want the first two columns to SNAKE to the third and fourth columns. Then, continue in that same format to page 2. Can you help? On Sunday, February 08, 2009 3:20 PM Per Jessen wrote: Snaking columns in Excel Hi Select columns A and goto Data Sort Set up the sort as desired.Regards, Per On Sunday, February 08, 2009 4:42 PM Gord Dibben wrote: After sorting the columns as you like..................... After sorting the columns as you like..................... Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 50, "A").Resize(50, 2).Cut _ Destination:=Cells(iTarget, "C") iSource = iSource + 100 iTarget = iTarget + 51 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub Gord Dibben MS Excel MVP On Sun, 8 Feb 2009 11:43:01 -0800, Mac wrote: On Monday, March 02, 2009 1:57 PM AFSSkie wrote: Gord,Your macro for snaking columns works great. Gord, Your macro for snaking columns works great. However, I need the column headers in A1 & B1 repeated to C1 & D1 and throughout the spreadsheet. Also have a different 2 column I need to snake to 8 columns (4 sets) in the same manner. Each page is 54 rows at the page break.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
