Combine multiple columns of data into one column???

**seedman76** · 03-06-2013, 04:30 PM

sample1.xlsx

Sample file attached. How do I combine the data in columns B-E into column A? Obviously in my sample file you'd use a simple cut/paste but my actual file has hundreds of columns and rows so cut/paste isn't an option. I need to move all the data into column A. Maybe this is simple but I can't figure it out nor find a similar post as I'm not sure the best way to search for this topic. Thanks for your help.

**JBeaucaire** · 03-06-2013, 06:09 PM

Your column A does not represent what you have described, the attached file has another separate entry in Column A.

**seedman76** · 03-06-2013, 10:59 PM

Ok, I corrected my file and re-attached it. Basically I want to know how to move the data from columns B-E into column A. If it can be done without a macro or VBA that would be great as I know nothing about either. Thanks.

sample1.xlsx

**Munch5** · 03-07-2013, 12:08 AM

I don't quite understand the question based on what you posted, but if you want to simply have the contents of columns B-E appear in column A, then use:

=CONCATENATE(B1," ",C1," ",D1," ",E1)

For your attachment, I get:

JON SELLENRICK AR1111955 MASON BENNETT AR1111955 SCOTT DESOTEL AR1111955 STEVE PIGG AR1111955

I added spaces to make it easier to read, but it doesn't really make much of a difference with that data.

Hope that helps.

rky.

**JBeaucaire** · 03-07-2013, 02:20 AM

I would definitely use a macro to do this.

Option Explicit

Sub TransposeData()
Dim LR As Long, Rw As Long

With ActiveSheet
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    Sheets.Add
    For Rw = 1 To LR
        .Range("A" & Rw).Resize(, 5).Copy
        Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll, Transpose:=True
    Next Rw
End With

End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use.

Bring the sheet with the data onscreen, then press Alt-F8 and select TransposeData from the macro list.