- HubPages»
- Technology»
- Computers & Software»
- Computer Software
How to Condense Data to a Single Column in Excel
There are times when it is beneficial to condense data from a large spread into a single column. This can take quite some time when done by hand. Even moving whole columns over can use up a whole lot of time. This can be done simply and easily using a macro in Excel. The directions provided here are for Microsoft Excel 2010 but can be generalized for other versions of Excel.
Instructions for Creating a Macro
Each step will be accompanied by a screenshot of Excel below it.
Step 1
Click View along the top
Click the arrow below Macros on the right
Click Record Macro...
Step 2
Type in the Macro name as desired (Here I chose "ColumnCondense")
Decide on a Shortcut key (Here I chose "w")
Click OK
Step 3
Click the arrow below Macros
Click Stop Recording
Step 4
Click Macros
Click Edit in the window that pops up
Step 5
A new screen comes up
Delete the contents of the module
Step 6
Paste the desired text into the module (Copy this text from the next part of this article depending on your needs)
Step 7: Block Data
When data is arranged in even rows and columns, make use of the following macro:
Sub ColumnCondense()
'
' ColumnCondense Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer
Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1
For iCol = 2 To rng.Columns.Count
Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
ActiveSheet.Paste Destination:=Cells(lastCell, 1)
lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub
Step 7: Unaligned Data
When the data is not in even columns and rows, use the following text. If this is used with data in even columns and rows, the condensing will occur, but an error message will be displayed. Click End.
Sub ColumnCondense()
'
' ColumnCondense Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer
Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1
For iCol = 2 To rng.Columns.Count
Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
ActiveSheet.Paste Destination:=Cells(lastCell, 1)
lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub
Step 8
Click the x in the top right corner to close out of the Macro editor.
Click Macros
Click Options...
Make sure a letter is displayed in the Shortcut key
Close the Options and the Macros pop ups
Step 9
Click cell A1
Press Shortcut key (In this case Ctrl + w)
Example with Unaligned Data
Before the Macro
After the Macro
Saving the File
When saving, change Save as type to Excel Macro-enabled Workbook
The Macro can only be used in this file
When the file is opened in the future, a yellow bar will pop up at the top of the screen stating that Macros are disabled, and Enable Macros should be clicked
