ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Setting up and Configuration of Excel (https://www.excelbanter.com/setting-up-configuration-excel/)
-   -   Snaking columns in Excel (https://www.excelbanter.com/setting-up-configuration-excel/219686-snaking-columns-excel.html)

Mac
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?

Per Jessen
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?


Gord Dibben
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?

AFSSkier
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?

Gord Dibben
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.

AFSSkier
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.

craig m
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

Gord Dibben
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.

[email protected]
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


All times are GMT +1. The time now is 06:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com