I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

On May 4, 10:55 am, Sriram wrote:
I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Example:
A1 = "All we need"
B1 = "is love"

C1 = concatenate ( SUBSTITUTE(A1;" ";"") ; SUBSTITUTE(B1;" ";"") )

OK? Check Helop on TRIM, CLEAN, SUBSTITUTE functions.
Mitja

PS: You might need to change ; to , depending on regional settings.

Thanks a lot for coming forward to help Mitja... I came to learn about new
Functions but that is not really what I wanted...

I will elaborate my question for you...

Say I have columns like

A B C

1 4 6
2 3 4
3 2
1

Now I want to bring them all under a Single Column D by using a function
where multiple Columns are copied to a single Column eliminating Blanks

A B C D

1 4 6 1 {From Column A}
2 3 4 2
3 2 3
1 4 {From Column B}
3
2
1
6 {From Column C}
4

I apologise if i was being very vague the last time. Thanks in Advance!!!

Sriram

" wrote:

On May 4, 10:55 am, Sriram wrote:
I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Example:
A1 = "All we need"
B1 = "is love"

C1 = concatenate ( SUBSTITUTE(A1;" ";"") ; SUBSTITUTE(B1;" ";"") )

OK? Check Helop on TRIM, CLEAN, SUBSTITUTE functions.
Mitja

PS: You might need to change ; to , depending on regional settings.

Can you handle a macro to add a worksheet and place all the columns into one
single column?

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 Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

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 Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

And thanks to Bernie for the code.


Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

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 Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center




These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!



"Gord Dibben" wrote:

And thanks to Bernie for the code.


Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

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 Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Ooops - that didn't format correctly when I posted but I think you get the
idea - I hope.

"CathyH" wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center




These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!



"Gord Dibben" wrote:

And thanks to Bernie for the code.


Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

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 Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Cathy

Line wrap caught your example data so is too disjointed to get a clear fix on.

You mean you have one worksheet with 40 columns?

4 columns per set?

1 2 3 4, 1 2 3 4, 1 2 3 4 for 10 sets?

You state you want to paste into a single set of 4 columns on a separate sheet
but you also state "These all have to be placed in one long column without
blanks "

You want one column on one sheet from these 40 columns?

Or 4 columns each on 10 sheets?

Or 1 column of 4 sets on 10 sheets?


Gord

On Tue, 15 May 2007 11:39:01 -0700, CathyH
wrote:

This is great information for someone who knows absolutely nothing about
writing code. I'm hoping someone can help me take this a little further.

I have a spreadsheet with multiple sets (10 or more) , 4 columns each that I
want to paste (on a seperate sheet) into a single set of 4 columns.

An example:

Paint
Tools
Acct. Amt. Category Cost Center Acct. Amt. Category
Cost Center




These all have to be placedin one long column without blanks for uploading
into our accounting system.

Please help a code newbie!



"Gord Dibben" wrote:

And thanks to Bernie for the code.


Gord

On Fri, 4 May 2007 22:36:01 -0700, Sriram
wrote:

That Precisely Addressed my Problem Mr. Gord Dibben.

I THANK YOU from the bottom of my heart!!!

"Gord Dibben" wrote:

Can you handle a macro to add a worksheet and place all the columns into one
single column?

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 Dibben MS Excel MVP

On Fri, 4 May 2007 01:55:01 -0700, Sriram
wrote:

I want to know whether we can copy multiple columns, and paste them into a
Single column, where all the copied values appear one below the other/
eliminating the Blanks...

Yes, you can definitely copy multiple columns and paste them under a single column in Excel. Here's how you can do it:

1. Select the columns that you want to copy. You can do this by clicking on the column header of the first column, and then dragging your mouse to select the other columns.
2. Once you have selected the columns, right-click on any of the selected columns and choose "Copy" from the context menu. Alternatively, you can use the keyboard shortcut "Ctrl+C" to copy the columns.
3. Now, select the cell where you want to paste the data. This will be the top-left cell of the single column where you want to paste the data.
4. Right-click on the selected cell and choose "Paste Special" from the context menu.
5. In the "Paste Special" dialog box, select "Transpose" and click on "OK". This will paste the data from the multiple columns into a single column, with all the values appearing one below the other.
6. If there were any blank cells in the original data, they will also be pasted into the single column. To eliminate the blank cells, you can use the "Go To Special" feature. Select the column where you pasted the data, and press "Ctrl+G" to open the "Go To" dialog box. Click on the "Special" button, and then select "Blanks" from the options. Click on "OK" to select all the blank cells in the column. Now, right-click on any of the selected cells and choose "Delete" from the context menu. In the "Delete" dialog box, select "Entire row" and click on "OK". This will delete all the blank cells from the column, leaving only the values that you copied from the multiple columns.

That's it! You have now successfully copied multiple columns and pasted them into a single column, eliminating the blank cells.