Using CONCATENATE

[venuswilliams]

I have several worksheets that i want to combine into one with the data. I am using CONCATENATE to combine them, but they are all merging together as one paragraph instead of line by line from the other sheets.

Can you help me?

 

[Peter_SSs]

venuswilliams

Welcome to the Mr Excel board!

You would need to give some more detail about what you have and where and what results you expect and where. Give some sample data and sample results.

 

[venuswilliams]

I have several worksheets with the same information. I want to combine them on one worksheet. On each sheet, the column may say in worksheet1 (clean towels), the worksheet, same column will say (dirty tiles), and the third worksheet may say (soap scum in sink). I want to join those columns together into one worksheet so that all 3 of those items will be in the same column, but they are joining together as a paragraph, i want for instance the first one that says (clean towels) to be on a line by itself, then under it will be the next one (dirty tiles), etc., instead i am using CONCATENATE and they are running into each other with a space inbetween (ex. clean towels dirty tiles soap scum).

 

[Peter_SSs]

venuswilliams

Thanks, that gives a bit better idea. Could you also paste in the CONCATENATE formula that you currently have (even though it doesn't give the result you want)?

Can you also confirm that in your example, you would want:
'clean towels' in cell A1
'dirty tiles' in cell A2
'soap scum in sink' in cell A3?

 

[venuswilliams]

That is wrong (dirty towels-worksheet1-cell A1) (dirtyt tiles-worksheet2-cell A1) (soap scum in sink-worksheet3-cell A1). They are the same cells in different worksheets that i want jointed together in another worksheet showing all of them together. But mine are joined as a paragraph instead of one under the other. Here is the formula (=CONCATENATE('1N'!F4,'2S'!F4,'4th fl'!F4)

 

[Peter_SSs]

OK, I still not absolutely sure what you mean by 'showing them together' but try this.
1. Formula in cell F4 of my 'Concatenate' sheet below is:
='1N'!F4&CHAR(10)&'2S'!F4&CHAR(10)&'4th fl'!F4
2. Select cell F4 then Format|Cells...|Alignment|Wrap Text|OK
3. With the cell still selected: Format|Row|AutoFit

Mr Excel.xls
	A	B	C	D	E	F	G
1
2
3
4						clean towels dirty tiles soap scum in sink
5
Concatenate

 

[venuswilliams]

Peter,

You are the greatest! I was trying to figure this out for a couple of weeks. Now thanks to you i can get my project under way. I really, really appreciate it.

Thank you,
Venus

 

[Peter_SSs]

Peter,

You are the greatest! I was trying to figure this out for a couple of weeks. Now thanks to you i can get my project under way. I really, really appreciate it.

Thank you,
Venus

No problem, glad to help.

Looking back on it you might see that it was a little difficult to determine exactly what you wanted because in Excel when people refer to 'lines' they often really mean 'rows' or 'cells'. You didn't actually say that you wanted the results on different lines IN THE SAME CELL.

Anyway, glad you got what you wanted and trust that if you have any more difficulties, the Mr Excel board will help you resolve them.