ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Condense Data to a Single Column in Excel

Updated on May 6, 2013

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article