Using Excel formula to combine a few rows into a rows and put them in a single cell

Question

I have the following rows in my excel spreadsheet.

============================================
| Male    | Jack    | 8765    | India      |
============================================
| Female  | Jill    | 5425    | Japan      |
============================================
| Male    | Pet     | 8541    | China      |
============================================
| Female  | Joan    | 2156    | Australia  |  
============================================

How do I use excel formula to combine the above rows into a single cell. (because the data above might change quite frequently and I do not wish to do it manually.)

======================================
| Male    Jack    8765    India      |
| Female  Jill    5425    Japan      |
| Male    Pet     8541    China      |
| Female  Joan    2156    Australia  |  
======================================

(For your info - I check that Microsoft are able to combine the same content in a row

============================================
| Male    | Jack    | 8765    | India      |
============================================

to a single row

======================================
| Male    Jack    8765    India      |
======================================

using the formula CONCATENATE or & as mention in http://office.microsoft.com/en-us/excel-help/combine-the-contents-of-multiple-cells-HA010248390.aspx?CTT=5&origin=HA010250379.

but I want to use a excel formula to multiple rows into a single cell. Possible?

Answer 1

Use CONCATENATE and/or & to combine the data within a row and use ...&CHAR(10)&... to insert the line break for the row. Turn on Wrap text for the cell holding the result so that the line break is displayed properly.

Your columns will probably not line up correctly because the cells' contents have variable lengths will probably be displayed with a proportional font. For best results you should use a fixed width font and use the LEN and REPT functions to determine and insert the appropriate amount of spaces between your data. For example, to make sure the data in cell A1 is padded out with spaces to make a column 20 characters wide, ... A1 & REPT(" ", 20-LEN(A1)) & ...