Combine Multiple Columns Of Data Into One Column |
Combine Multiple Columns Of Data Into One Column - Excel |
|
Hi,
I'm looking for a way to combine (not concatenate) the data from 5 columns in a single column. In other words, if I had 5 rows of data in column A, 3 rows of data in column B, 11 rows of data in column C, 8 rows of data in column D and 9 rows of data in column E, I want column F to have the 36 values (5+3+11+8+9 = 36) from the other columns.
The data has significantly more rows than I've used in my example and cutting and pasting is getting a little tedious every time I need to combine the data.
The data starts in row 3 for each column and any given row has no more than 258 values.
There are no intermingling blank cells in a given column; the blanks are at the end of each column.
The data is text, not numeric
(Back story: The data in columns A-E is generated using array formulas that pulls data from other worksheets. I need to combine those columns in to one, then remove duplicate values and sort the resulting combined column. The removing duplicates and sorting part I can handle; it's the combining part I'm having problems with. I was about to drop into VBA to solve this but since when it comes to writing formulas I'm a noob, I thought that I check here first. I saw this thread but no one solved it.)
Any help would greatly appreciated!
Thanks!
Paul
Similar Topics
I want to combine data from several worksheets into one worksheet.
For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).
I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.
I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?
Any help will be beautiful.
Cheers
I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.
However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.
I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?
Any advice is higly appreciated.
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.
Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.
What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.
The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.
Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?
Many thanks.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help
Thanks
Added example spreadsheet to aid assistance.
I have a column with a set of duplicate values. I need to remove the duplicates while leaving the original data and keeping the blanks. The Remove Duplicates function comprises the data and this does not work for me. Help!
I am brand new to Mr. Excel and would love some advice.
I searched the boards pretty extensively but could not find what I am looking for...I apologize if this is a duplicate.
I am using Excel 2007
How do you automatically add rows and update values for cells to a linked worksheet in which rows have been added? For example: Sheet 1, columns A & B are linked to Sheet 2, columns A & B. Sheet 2 has values in A1:A5 & B1:B5 and Sheet 1, since it is linked, has the same info. I want to add a row in between 3 & 4 on Sheet 2 and want Sheet 1 to automatically add the same row and update the value of the cell in column A & B.
Any help is greatly appreciated!
John
display rows that have values greater than 0 in any one of those three
columns. So far I can only filter progressively. If I filter column E and
then filter column G, the second filter is only applied to the results of the
first filter.
Does anybody know if it's even possible to do what I'm trying to do?
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.
Any help anyone could provide would be WONDERFUL. Thanks!
I need to insert 5 blank rows repeatedly between every existing data rows
for approximately 300 rows.
If I go about doing the repeat short-cut "Control+Y", it just repeats
inserting ONE row only between the consecutive data row.
Is there some command, which helps me highlight all the rows & allows me to
insert 5 blank rows between every consecutive existing data row?
Thanks in advance for your kind advice.
Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...
I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values
Thanks
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
Thanks in advance.
I am entering lots of family history data into a spreadsheet. At the simplest I have columns (in cells A1 B1 C1) the headings, Surname, Forename, Year. Right now, I enter in cells A2 B2 and C2 say: Smith <Tab> John <Tab> 1555 <Enter, move mouse to the A column in the next row down). What I want to happen is when I have entered the last data in a row and pressed <Enter> I move automatically to the A-column in the next row down.
Is this possible?
Regards and a Merry Christmas to all
Wibs
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin