How to merge multiple columns of data in an Excel sheet into one[Edit.] From the conversation in comments, it sounds as if you just need to convert an n x m table into a single column of values, or possibly a list (i.e., with one or two classifier columns corresponding to the original row and column titles).
There are many resources available on line to do this using VBA, which you should be able to discover by a web search. Here is just one reference I found, but I am not specifically recommending or endorsing this page, it just looked like a useful starting point as I skimmed through it:
[Taking a closer look, this actually saves you the trouble of VBA, and gives you formulae for unfolding the table, either row-by-row or column-by-column.]
The answer probably depends upon what type of data you have and why you want to do it in the first place.
To begin with, is it numeric data or text or both. If the former, do you want a sum, an average or some other numerical function?
If only text, do you want to concatenate all the values, and if so, do you want separators (spaces, commas, comma-space, etc.) between values, and how do you want to handle cases where you have missing values?
And if you have a mixture of numeric and text data, what then? Do you treat all values as strings, or ignore text data and only look for numeric data, perhaps?
Next, you have to consider if this is to be a one-time operation, and how reversible or repeatable you need it to be.
My preference would typically be to keep the parsed data for ease of use, though that is not always the case.
All these factors duly considered, I would highly recommend looking first at data outlining (levels). This permits you to nest rows (and or columns) of data in a very convenient fashion, completely reversibly, and with optional subtotal columns (or rows) to the left or the right (above or below) the nested data.
For text data, you will need to write your own concatenation formula, because of the complexities of how to deal with missing values, doubled spacer strings and terminal values.
Next you could consider just hiding columns, after creating the appropriate column with the concatenation or numeric summary formula you require. This is also reversible and can be done by unhiding or simply resetting the column widths to something non-zero.
Lastly, if you really want this to be a one-time event, you would create the summary column the same as before, but then copy and paste-special Values only in place over your formulae, before deleting the source columns. If later you need to reverse this, you can use the Text-to-column command, but as the name suggests, this will only work for text values.
I hope this helps!
Basis Andrew's detailed and very informative response below, and further comments in that, below is my understanding of your requirement:
- You have multiple excel sheets each with many rows and columns of data
- The data is all numeric
- Task is to be repeated later multiple times (daily, weekly, monthly etc.)
Basis the above requirement, one solution I can recommend is as below:
- First merge all the excel files together. Consider some VBA code for this (refer this link for a start)
- Create a named range where you have your data (e.g. named range is Data_Range)
- Identify the number of rows and columns in your data using the below formulas
- r = ROWS(Data_Range)
- c = COLUMNS(Data_Range)
- In the column where you want all the values (say you want it in column A) type the below formula in first cell (i.e. cell A1)
- Copy this formulas down to as many rows as (r * c)
You can use the Concatenate formula to merge multiple columns in MS Excel.
To learn more formulas click here