A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Setting up and Configuration of Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Snaking columns in Excel



 
 
Thread Tools Display Modes
  #1  
Old February 8th 09, 08:43 PM posted to microsoft.public.excel.setup
Mac
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?
Ads
  #2  
Old February 8th 09, 09:20 PM posted to microsoft.public.excel.setup
Per Jessen
external usenet poster
 
Posts: 1,533
Default 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  
Old February 8th 09, 10:42 PM posted to microsoft.public.excel.setup
Gord Dibben
external usenet poster
 
Posts: 22,909
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  
Old March 2nd 09, 07:57 PM posted to microsoft.public.excel.setup
AFSSkier
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  
Old March 2nd 09, 09:26 PM posted to microsoft.public.excel.setup
Gord Dibben
external usenet poster
 
Posts: 22,909
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  
Old March 3rd 09, 01:02 AM posted to microsoft.public.excel.setup
AFSSkier
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  
Old November 4th 09, 09:36 PM posted to microsoft.public.excel.setup
craig m
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 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  
Old November 4th 09, 09:45 PM posted to microsoft.public.excel.setup
Gord Dibben
external usenet poster
 
Posts: 22,909
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 F5>Special>Blanks>OK>Edit>Delete>Entire 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.


  #9  
Old March 31st 14, 04:36 AM 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("A11").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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 11:03 PM.


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