transfer data from multiple columns to singlr column

  • Thread starter Thread starter lc85
  • Start date Start date
L

lc85

I have data in form a d g
b e h
c f i (but larger scale)
and I need it in a single column going a to z.
 
Hi,
highlight you data, copy, go to the column where you want to see the data,
paste special, transpose
 
You up for using a macro?

Sub ToOneColumn()
'dantuck Mar 7, 2007
'multi columns to one
'all columns must be same length with no blanks
Dim cntI As Integer
Dim cntJ As Integer
Dim TotalRows As Integer
Dim TotalCols As Integer
TotalRows = ActiveSheet.UsedRange.Rows.Count
TotalCols = ActiveSheet.UsedRange.Columns.Count
For cntJ = 2 To TotalCols
Cells(1, cntJ).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Cells((cntJ - 1) * TotalRows + 1, 1).Select
ActiveSheet.Paste
Next cntJ
Cells(1, 1).Select
End Sub


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 Insert>Module. 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 Tool>Macro>Macros.

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


Gord Dibben MS Excel MVP
 
Gord's VBA solution is certainly the most efficient and flexible if you're
going to have to do this lots of time with different sized sets of data. If
you only have to do it a few times or if you're not comfortable with VBA, you
can get the job done without it:

- Assume your 3 columns of data are in columns A:C on Sheet1.
- Enter the following formula in A1 on Sheet2
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3))
- Copy it down as far as necessary to capture all the data from Sheet1; i.e.,
3 times as many rows as there are in Sheet1. (It may be easiest to just
keep copying it down until you start getting zeros and then delete the zeros.)

In case you're not familiar with
OFFSET(LeftUpperCornerCell, OffsetRows, OffsetColumns),
it returns a reference to the cell thats OffsetRows and OffsetColumns from
the LeftUpperCornerCell. (It has some other arguments but they're optional
and you don't need them for this.) The INT and MOD expressions convert the
number of the row the formula is in to row and column numbers in Sheet1. (If
you ever have more or less than 3 columns in Sheet1, just change the "3"s in
the formula to how ever many columns there are.)

I gather you want to sort them alphabetically. In order to do that, you need
to convert the formulas to values:

- Select the column
- Ctrl/C or right click > Copy to copy it
- right click > Paste Special > Values to replace the formulas with values

Then sort the column.

If you're going to have to do it more than once, you don't want to overwrite
the formulas with text. Instead, select another column - maybe on another
worksheet - before you Paste Special > Values and then sort that column.
 
Back
Top