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/3/2010 12:20:03 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 51

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.
2/3/2010 12:47:01 PM
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
    Cells((cntJ - 1) * TotalRows + 1, 1).Select
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". 

or Ron de De Bruin's site on where to store macros. 

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

On Wed, 3 Feb 2010 04:20:03 -0800, 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.

2/3/2010 6:57:30 PM
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
- 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.

2/22/2010 9:19:02 PM

Similar Artilces:

Hidden data when pasting from html page into Excel?
I have a situation where I copy a report, which is generated as an html page, into Excel for analysis. When a new copy/version of the report is available, I copy and paste the new data over top of the old in Excel. I've noticed that the size of my Excel file is growing after each copy/paste situation. To manage this, I can delete the worksheet where I paste the data and reconstruct it. After doing nothing else, saving the workbook results in an immediate reduction of file size. If I simply select everything on the sheet and delete, the file size does NOT go down; I have to delet...

Outlook 2002 and Retrieving Data Error Message
OS is Windows XP SP1 Email - Outlook 2002 SP3 Error Message: Outlook is retrieving data from the Microsoft Exchange Server <server name>. You can cancel the request or minimize this message to the Windows taskbar until Outlook closes the message automatically. Other Related Issues: At times, the message displays the correct server name and sometime it does not. At times, it displays the name of an active directory server. This only happens to workstations located in an remote office connecting to the exchange server via ADSL\VPN connection. We have one exchange 2003 server - public...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: View this thread: You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Search Multiple Worksheets #2
Is there a way to search trough multiple worksheets for a specific value? Other posts have mentioned to use VBA, but I have never used that before. If anyone can give me some advice on using that or a type of formula to perform that can search multiple worksheets. Thank You There may be other ways but, while holding down the ctrl key select each of the worksheet tabs you want to search in then select Edit|Find from the menu. Type in the value you want and it will go to the first instance of that value. Now if you are wanting to preserve a specifice value for reference or ???, then...

map data (x-y axis) with diffent series name
I have a set of x-y coordinate data set and i would like to plot them showing the respective series name. Is there an automatic way of doing this without haing to plot the data points separately. eg Name x y A 1 6 B 2 3 C 3 4 D 4 2 E 5 5 .. . . .. . . .. . . I will like to have a plot of x and y with A, B, C as series name without having to plot the points one after the other. Hi, Here are a couple of free addins that allow you to link data labels to cells. Rob Bovey's Char...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <> wrote in message > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

creating a function for dividing two columns
I am trying to keep track of softball stats. I trying to make a column of batting averages. I have at bats in column b, hits in column c with the results in column d. I cant get the function right so it will display the correct result. use this =SUM(C1/B1) in cell D1. Format D1 as a number with 3 decimal places. Steve Hubbard "cflan" <> wrote in message > I am trying to keep track of softball stats. I trying to make a column of > batting averages. I have at bats in column b,...

Multiple AND OR functions
Is it possible to make this function work? =IF(AND(AND($B9="Z",AE9=35),OR(J9=1,J9="M",J9="C")),1000,IF(AND(AND($B9="A+",AE9=35),OR(J9=1,J9="M",J9="C")),750)) I need to find out if Cell B9 = Z and if Cell AE9 = 35, if this true then check cell J9 and check if it equals 1, M or C then return the value as 1000. (that bit works OK) I also want it to check if an alternative statement is true if the first is false whereby it checks the the same set of cells but this time, check if B9 =A+, if Cell AE9 = 35, if this true then...

Offline Address Book, Multiple Administrative Groups
Hello, We recently deployed an additional Exchange server in our organization and placed it into its own administrative group, now users on the new server are getting 8004010F errors in Outlook when attempting to download the offline addresss book. The users who are on the original server do not receive these errrors when downloading the address book. If anyone can provide some assitance it would be greatly appreciated. It's look like this;en-us;162703 "jballin" <> wrote in message...

Multiple copies of E-Mail messages
I am using Outlook 2002 (10.4219.4219) SP2 with a Windows XP Professional operating system. Just in the last couple days I've started to experience a problem with incoming e-mail messages. I use Outlook to retrieve e-mail from at least four different accounts, from at least two different servers. When I receive a new e-mail message that is addressed to one of these e-mail accounts, I get two extra copies of that message, and each of these extra copies are addressed to two of my other accounts. So what I end up with is three copies with three different TO: addresses. This only happens ...

Outlook is trying to retrieve data from the Microsoft Exchange Ser
Hi I keep getting a msg saying "Outlook is trying to retrieve data from the Microsoft Exchange" everytime i try and send a mail... the mail then gets stuck in my outbox. I still receive all email and this only happends over one particular broadband connection??? If i use dial up it works and if i go to some other broadband connection it works... any ideas? _VERITAS_ wrote: > Hi > I keep getting a msg saying "Outlook is trying to retrieve data from > the Microsoft Exchange" everytime i try and send a mail... the mail > then gets stuck in my outbox. I s...

Saving data #2
Hi all, I need to save data (results) from a base spread sheet program that i use on a weekly basis. i am in the middle of building this program, and have just discovered macros, but this, along with links is about my current knowledge of excel how can i automate to accumulate data from the base spread sheet (program) when i clear all data from the program to produce fresh results the following week, and to keep past data up to date and available for further use. Any help would be appreciated. legepe With a combination of formulas and dynamic named ranges, it is possible to just add the ...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from ~~View and post usenet messages directly from debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

Multiple SMTP Address
If a user is set up to have multiple SMTP address setup in active directory. When they send an email in outlook how can they chose which one it is from. You can only do this, in the setup you describe, by using a 3rd party app such as ChooseFrom from -- Mark Arnold. "Shane" wrote: > If a user is set up to have multiple SMTP address setup in active directory. > When they send an email in outlook how can they chose which one it is from. On Tue, 31 May 2005 06:41:03 -0700, "Shane" <> wrote: >If a user is s...

Printing a worksheet in two (or more) columns
Hi, I've got an extremely long spreadsheet table comprising two columns of data. I'd like to print these data in a more compact form - in the same way that the entries are organised in a telephone directory. Say on average my page is 50 rows high - my first two columns on page 1 would appear on the left hand side and be rows 1 through 50, row 51 would continue at the top of the page, but on the right-hand side. Row 101 would appear at the top left hand side of page 2, etc. I'd like to keep the orientation of the columns in portrait (I did find a printer-associ...

how can i relate between two columns in two sheets?
Question no too clear - you can use this space to give a detailed question. But here goes: On Sheet1 I can type formulas such as =Sheet2!A1 =SUM(Sheet2!A:A10) If I type = and then click on the cell A1 of a worksheet called My Yearly Totals, I will get the formula ='My Yearly Totals'!A1 Note that a sheet name having spaces get surrounded by single quotes. Does this answer your question? best wishes -- Bernard Liengme Microsoft Excel MVP email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all me...

Get Access Data into Excel
Hi All, I am using excel macro to get data from access database. My sql query gives me 5 records or more than that. I am able to pull it different cells. But I want all the 5 results in single cell. Please help im not exactly sure what your trying to do, but maybe something like this will do the trick dim accval as string accval=rs!:XXX rs.movenext accval=accval & " " & rs!:xxx loop it till rs.eof=true hth dmoney "" wrote: > Hi All, > > I am using excel macro to get data from access database. >...

Cut'n'Pasting data
Greetings ! I have a CSV data file wot looks something like this - "1529.17698720957","133.597550559965" "1685.21901149326","132.817184396522" "1900.00000000000","130.300887834893" "2193.34485206410","121.730777157435" "2363.17295960769","114.937652855693" "2523.01169878763","108.544103288496" "3240.77088467590","87.805735336415" "3590.69860622591","81.680775462264" "4229.19543928027","78.487652800160" This data i...

Transfer Email address?
I have a user, say user1, with email setup in exchange 2003, as I now want to delete user1, but keep his email and assign it to user2, so user2's email will be (will not have a email) How do I go about doing this?? Change User1@email,com to something else, like Add address to user2 as an SMTP address. Make it default. Delete user1 account when ready.... "James" <> wrote in message news:uhtkSuMBGHA.2512@TK2MSFTNGP09.phx.gbl... >I have a user, say user1, with...

How to access data buffers in a completion routine for a KMDF driv
I'm porting a WDM upper filter driver to KMDF version. I have two questions about data accessing in a completion routine. 1. In the completion routine of my WDM upper filter driver, it was able to retrieve data from Irp->AssociatedIrp.SystemBuffer( The IRP is using Method Neither I/O, and the data was set by function driver). How to do this for my KMDF driver? 2. In the completion routine of my WDM upper filter driver, it was able to retrieve data by calling MmGetSystemAddressForMdlSafe(Irp->MdlAddress, NormalPagePriority)( The IRP is using Method Neither I/O, and t...

Automatically moving data #4
Thanx for all the help. I'm going to make the file smaller first. Where do I email it to -- multipla ----------------------------------------------------------------------- multiplan's Profile: View this thread: Thought I gave you this reply earlier ?? Either to: xdemechanik <at>yahoo<dot>com or demechanik <at>yahoo<dot>com (both valid) -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>...

Making sure data is saved on closing application
Hi, I have built myself the following application from 'Teach Yourself Visual C++ in 21 Days' chapter 'Day 14 - Retrieving Data from an ODBC Database' The application lets you edit and add records to a database (as well as just retreive them). The application uses recordset flags in code to make sure that data is saved if you edit one record and move to another. However, there is no code to make sure new or edited data is saved when the application is exited. After having poked about, I have...

refer to data on multiple worksheets using hlookup/look up data on many worksheets?
Hi I have data on many worksheets in the same workbook. Is it possible to look up data on multiple worksheets using Hlookup, which means can the second argument in the formula refer to multiple sources? If it is not possible, is there any other way I can do so? I would like to select and display data according to a fixed order, whereby the data is located in one of many worksheets in the same workbook. For eg, I have stock returns of many firms in many worksheets. Say I would like to display in a single worksheet the returns of Firm D, Firm Z, Firm R; whereby the data of these 3 fir...