Merge Multiple column into new column

G'Day to the list.

Have Columns J-O. Need to copy the data in each to these column to Column P
Need to do this to multiple rows. Data needs to be separated by commas.
J. CEPP OEM K.POC L. OEM M. OEM Assistant N.1. Den-Bldr Metro O. Private Result in Column P "CEPP OEM, POC, OEM, OEM Assistant, 1. Den-Bldr Metro, Private"
New User of Excel 2010. Tried to select rows and use the marge function, however it deletes my source data and copies only 1st data column into result column. Have looked at several tutorials (Google Search) but none of them achieve desired result.

I've attached a small spreadsheet and a jpeg of the spreadsheet for your review.

Thanks in advance.
dana

try this in Cell F2 and copy down

Please Login or Register  to view this content.

Thanks for the help.
This has to be my inexperience
Attached is the result I get
I copies the formula into the formula bar and it edid row 2 correctly, however when i copies forkula to destination cellin rows 3-6 I get the same result as row 2, and each of these rows if unique.

attached is spreadsheet of my results
Again thanks for your assistance
dana

Check out Col G.....I'm not sure what you were trying in column H

beginning to doubt myself, Im doing something fundamentally wrong.
When I copy formula CONCATENATE(A2,",",B2,",",C2,",",D2,",",E2) into rows of Column G, and hit enter all I see is the formula.
I may be doing this completely incorrect. Your help is greatly appreciated.
See attached.
d

Enter this in F1 and copy down the column:

Formula:

Please Login or Register  to view this content.

When I open this attachment it looks fine....what you might try is copy the formula (make sure the = comes with it) into G2 press enter....if that works okay then click back into G2, if you hover your mouse over the lower right corner it will change to a little square, double click on that and it will copy down the column for you...

This is great, Words with one detail inaccurate, it repeats the data for row 2 into row 3, and each column is push one row down from it's correct date. i.e. row's correct data is in row 4, and row 4's correct data appears in row 5 etc.
See attached.
Appreciate everyone's help this is a large project with 815 rows to merge.
dana

Really appreciate your efforts. Feeling a little stupid here!
If I copy the formula into row 2, and put mouse in lower right hand corned and left click and drag down, only the 2nd row populates.
If I copy formula into each cell, the formula gives only the response for row 2 and not the individual response (unique list) for other rows.
Attached is my result
d

Faraz,
Thanks much. Just noticed
Formula is: =CONCATENATE(A2,","," ",B2,","," ",C2,","," ",D2,","," ",E2,","," ",F2)
When I put formula into row 3, and change the last digit of formula from 2 to a 3 then it calculates that cell correctly.

New Formula: =CONCATENATE(A2,","," ",B2,","," ",C2,","," ",D2,","," ",E2,","," ",F3

Is there an addition to the formula to have it calculate rows 2 to End of sheet. (My sheet is 817 rows for full sheet? Ior If needed I can put in # of rows such as rows 2-7
Thanks much.
d

Just noticed that calculations are not correct by row, see attached, in one place it brings FEMA from row above but omits information from that row.
I have to be doing something terribly wrong?
d
see attached

Here is your file back with the formula in column G. I don't see anything being repeated that shouldn't be. The second file has spaces after the commas.

Newdoverman,
That it Works perfectly first time, then when I went to replicate it, it won't populate each line at same time.

Any Ideal, think I'm doing a simple task incorrectly.
When the formula into the formula bar, and select rows 3-8, only row two shows the combined data,
Even when i change the last number on the formula
=A2&", "&B2&", "&C2&", "&D2&", "&E2&", "&F2
From F2 to Fe, F4, etc. it still only combines data from Row two and not the other rows.
I really appreciate your help,

WOuld appreciate it if you could show me where I'm making my error Why does it seem to work in your examples, yet when I try to work with it, somehow I do something which makes the formulas not work?
dana

The way that I understand your description of the problem, you are not entering the formula and copying it correctly.

Click in cell G2 to make it the active cell. Enter the formula and hit enter.

Formula:

Please Login or Register  to view this content.

To copy the formula down the column, click on G2 then left click and hold on the little square on the bottom right corner of G2. The cursor will change to a big +. The formatting that you have make seeing the square difficult to see so when the cursor changes to the big + left click and hold and drag down the column. The formula will be copied correctly updating the cell references in the formula to combine the cells that you want combined.

Newdoverman,
Doing something wrong here was able to get formula to work only once. Sure it's something I am doing.
a. Place cursor in G2, selecting and cell and then typing "=A2&", "&B2&", "&C2&", "&D2&", "&E2&", "&F2" into the formula bar.
c. Place cursor on right hand side of G2 and it changes to a small fat+ symbol, left click and drag it down all the rows. I see the rows selected they
turn blue, and then i hit enter, nothing happens.
Please let me know if you can see where my error is occurring. Thanks much for the extra help.
dana

Don't hit enter. You are just copying the formula down the column. Just click in another cell after the copy. If you click back in the last cell where you dragged down, you should see the formula updated to its position. If you are still having problems, upload your file again stripped of sensitive data and I will try and figure out what is going wrong.

Was able to do it successfully only once then could not repeat it. attached sample spreadsheets, one shows what happens after I paste the formula into the bar after selecting G2 and copying down, the other shows view before copying. Really appreciate this help This is a function I'm going to frequently repeat on spreadsheet with 815-840 rows.
dana

None of your "formulae" start with an = sign. All formulae start with =. See the enclosed picture of the formula bar with the formula in it. Note the = sign.

In the Excel file enclosed, click one of the cells that should have the formula and look in the formula bar and you will see how the formula should be entered. Click in each cell down the column and note how the formula changes as you go down the column. This is due to having RELATIVE references. Relative references will change as you copy down a column or along a row.

Newdoverman.

Your help and sage advice are greatly appreciated. I must have a problem with how I clck tog o down and pupulate additional rows.
Put the courser into Column G Row 2, and pasted the formula into the formula bar:
=A2&","&B2&","&C2&","&D2&","&E2&","&F3

G2 correctly populated 833 Records Correctly After right clicking in the lower right hand corner.
Fantastic.
Any suggestions other than to do a find and replace, to eliminate the extra " and commas for the rows A-F which happen not to be populated?

I cannot do a find and replae, as it does not recognize Find ,, and relace with ,
This is the result trying to fix ",No,,OEM Assistant,Municipal,2. Southern Colo."
Apology this took so long to get back. to you. This will be an incredible tool.Thanks again dana

This appears to work for missing data.

Formula:

Please Login or Register  to view this content.

Going back to the Information Well one more time.
WhenI import sample list of 10 individuals, I need to delete Columns A-F, as the Alert system we are using does not accent columns that are not in their application.
a. Tried to delete columns A-F, and then Column G appears with an error, as all the formulas are lost.
b. Made a new file with Just TAGS Column, and tried to do a copy and paste, and same result the Tags are gone and "#REF!" now appears in every row.
Any suggestions?
Thanks again.
dana

Issue temporarily solved
a. Set print area excluding columns A-F and printed spreadsheet to PDF using full version of Acrobat 9. Then saved pdf as a .xml spreadsheet, then saved spreadsheet as Excel workbook file .xlsx, I was then able to import into the Alert System successfully.
Even better, their search for Tags doesn't seem to be affected by all the extra commas for un-populated fields.
Again Thanks for a great job.
dana

If you simply delete the CONTENTS of columns A:F, that should not affect the formulae in column G. If however you actually delete the columns, column G then becomes column A and everything is messed up.

By simply deleting the contents of columns A:F, you should be able to replace the contents with new data.

To refresh:
Mr. Doverman solved the issue of combining Column Information from Columns A-E and display the information in Column F
That formula was "=A2&","&B2&","&C2&","&D2&","&E2&","&F3

Haven't worked with this formula for several weeks, but went to use it today, instead of copying Column A-E into Column F, it appears to copy every row of data multiple times. Please see attached spreadsheet.
Sure this is an inexperienced user error on my part.
Thanks in advance d

Please Login or Register  to view this content.

Remove &","&F3

popipipo,
Nice work, Your formula was successful.
d

Two things.

1. Popipipo is correct for the formula correction.

2. The original formula should have ended with F2 and NOT F3 this was a typo that was missed for quite a while.

I'm glad that you got it sorted out.

newdoverman,
Thanks, for your comment and all the time you spent on this project. Just so you know how much I appreciate everyone's input this data conversion will be done to over 800 records, doing about 50 at any given time.

You're welcome. Good luck with your project.

I apologize for not catching the error in the original formula. That was just plainly dumb.

This formula is really quite simple and if you need to adjust it, just follow the pattern in the formula. If you need help this is a great place to get it.