#1   Report Post  
Posted to microsoft.public.excel.setup
Mac Mac is offline
external usenet poster
 
Posts: 213
Default 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 1,533
Default Snaking columns in Excel

Hi

Select columns A:D 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 94
Default 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 94
Default 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 1
Default 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:D 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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
Default 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:D 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.


  #9   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 1
Default Snaking columns in Excel

On Monday, 9 February 2009 06:43:01 UTC+11, 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?


This solution is fabulous. I changed a couple of things to make it 4 column for 7,300 rows and removed the blank row. Absolutely brilliant.
Ian

viz:
Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long
iSource = 2
iTarget = 2
Range("A1:D1").Copy Range("E1:H1")
Do
Cells(iSource, "A").Resize(54, 4).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 54, "A").Resize(54, 4).Cut _
Destination:=Cells(iTarget, "E")
iSource = iSource + 108
iTarget = iTarget + 54

Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
putting 2 long columns into multiple columns in excel page and sor bob_mhc Excel Discussion (Misc queries) 1 April 25th 08 07:51 AM
Excel 2003 - change columns to rows and rows to columns JLatham Excel Discussion (Misc queries) 0 August 17th 07 02:05 AM
unable to insert columns in excel, insert- columns (disabled) iam_leearner Excel Discussion (Misc queries) 1 August 13th 06 02:26 PM
column snaking print format Montrose Lyle Excel Discussion (Misc queries) 2 April 28th 05 12:02 AM
column snaking print format Duke Carey Excel Discussion (Misc queries) 1 April 27th 05 08:49 PM


All times are GMT +1. The time now is 06:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"