Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 25th 12, 05:37 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2012
Posts: 4
Default Collating columns and rows to one columns

I have data in columns A through to J (i.e. 10 columns) and rows 1 to 400. So there is a huge block of data. I need to purely move the data in columns B to J (in sequence) so they sit under just one column A.

In other words I will have a long column A comprising of the same data but all in one column, which will end up being 4000 rows long.

I wondered if there was a quick way to do this? I have no experience of scripts or anything like that so would appreciate a dummies approach to this.

Thanks

  #2   Report Post  
Old June 25th 12, 06:47 PM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by Dougie13 View Post
I have data in columns A through to J (i.e. 10 columns) and rows 1 to 400. So there is a huge block of data. I need to purely move the data in columns B to J (in sequence) so they sit under just one column A.

In other words I will have a long column A comprising of the same data but all in one column, which will end up being 4000 rows long.

I wondered if there was a quick way to do this? I have no experience of scripts or anything like that so would appreciate a dummies approach to this.

Thanks
Hi,

4000 rows of data would probably be quicker to manipulate in this way manually than it would be to write some kind of formula/VBA.

How often do you need to do this?
  #3   Report Post  
Old June 25th 12, 10:35 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2012
Posts: 4
Default

Quote:
Originally Posted by Spencer101 View Post
Hi,

4000 rows of data would probably be quicker to manipulate in this way manually than it would be to write some kind of formula/VBA.

How often do you need to do this?
Hi, well I forgot to mention I have about 20 worksheets of the same type which all need collating into one column in each worksheet. To add, I get this about twice a month so a formula would be of great help, doing in manually is undesirable really.

Thanks
  #4   Report Post  
Old June 26th 12, 04:39 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default Collating columns and rows to one columns

A macro could be much faster when doing that many sheets.

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim myCell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each myCell In myRng
If myCell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next myCell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").entirerow.Delete

ws.Activate
End Sub


Gord

On Mon, 25 Jun 2012 21:35:40 +0000, Dougie13
wrote:


Spencer101;1603115 Wrote:
Hi,

4000 rows of data would probably be quicker to manipulate in this way
manually than it would be to write some kind of formula/VBA.

How often do you need to do this?


Hi, well I forgot to mention I have about 20 worksheets of the same type
which all need collating into one column in each worksheet. To add, I
get this about twice a month so a formula would be of great help, doing
in manually is undesirable really.

Thanks

  #5   Report Post  
Old June 26th 12, 01:19 PM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2012
Posts: 4
Default

Quote:
Originally Posted by Gord Dibben[_2_] View Post
A macro could be much faster when doing that many sheets.

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim myCell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each myCell In myRng
If myCell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next myCell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").entirerow.Delete

ws.Activate
End Sub


Gord

On Mon, 25 Jun 2012 21:35:40 +0000, Dougie13
wrote:


Spencer101;1603115 Wrote:
Hi,

4000 rows of data would probably be quicker to manipulate in this way
manually than it would be to write some kind of formula/VBA.

How often do you need to do this?


Hi, well I forgot to mention I have about 20 worksheets of the same type
which all need collating into one column in each worksheet. To add, I
get this about twice a month so a formula would be of great help, doing
in manually is undesirable really.

Thanks
Thanks Gord for your work here, it's much appreciated but I'm afraid I've no idea what to do with this? Do I have to type it out somewhere. Apologies but I'm very inexperienced in excel.


  #6   Report Post  
Old June 26th 12, 02:56 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default Collating columns and rows to one columns

If you're not familiar with VBA and macros, see David McRitchie's site
for more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic
Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord




On Tue, 26 Jun 2012 12:19:12 +0000, Dougie13
wrote:


'Gord Dibben[_2_ Wrote:
;1603147']A macro could be much faster when doing that many sheets.

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by Bernie Dietrick
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim myCell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each myCell In myRng
If myCell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next myCell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").entirerow.Delete

ws.Activate
End Sub


Gord

On Mon, 25 Jun 2012 21:35:40 +0000, Dougie13
wrote:
-

Spencer101;1603115 Wrote: -
Hi,

4000 rows of data would probably be quicker to manipulate in this

way
manually than it would be to write some kind of formula/VBA.

How often do you need to do this?-

Hi, well I forgot to mention I have about 20 worksheets of the same

type
which all need collating into one column in each worksheet. To add, I
get this about twice a month so a formula would be of great help,

doing
in manually is undesirable really.

Thanks-


Thanks Gord for your work here, it's much appreciated but I'm afraid
I've no idea what to do with this? Do I have to type it out somewhere.
Apologies but I'm very inexperienced in excel.



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
how do i paste rows/columns avoiding hidden rows/columns perezli Excel Discussion (Misc queries) 1 January 30th 09 03:58 PM
Excel 2003 - change columns to rows and rows to columns Trish Excel Discussion (Misc queries) 0 August 17th 07 02:22 AM
Excel 2003 - change columns to rows and rows to columns JLatham Excel Discussion (Misc queries) 0 August 17th 07 02:05 AM
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 2 July 31st 06 09:45 PM
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 0 July 31st 06 05:07 PM


All times are GMT +1. The time now is 10:55 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017