If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
transfer data from multiple columns to singlr column
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. |
#2
|
|||
|
|||
transfer data from multiple columns to singlr column
Hi,
highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: 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. |
#3
|
|||
|
|||
transfer data from multiple columns to singlr column
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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Wed, 3 Feb 2010 04:20:03 -0800, lc85 wrote: 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. |
#4
|
|||
|
|||
transfer data from multiple columns to singlr column
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. |
Thread Tools | |
Display Modes | |
|
|