Returning multiple rows of data in one cell

[Rachael000]

Hi Chandoo


I have a large spreadsheet which holds all my client data and i want to link it to another spreadsheet to turn it into more of a report.


worksheet 1 - called "Formatted"

Worksheet 2 - Called "list 2012"


in "Formatted", columns L to P are entitled "Address 1", "address2", "Address 3", "Address 4", and "post code", respectively.


In "list 2012", i just have one column, entitled "Address", which I want to return all of the above data in one cell. ie:


=Formatted!L3

Formatted!M3

Formatted!N3

Formatted!O3

Formatted!P3


Is there any way to do this? I've tried using Alt+return in the bar at the top when typing but it keeps returning as "false". (I'm also using IF functions, so my formula at the moment reads:


=IF(Formatted!L3="","",""&Formatted!L3)

=IF(Formatted!M3="","",""&Formatted!M3)

=IF(Formatted!N3="","",""&Formatted!N3)

=IF(Formatted!O3="","",""&Formatted!O3)

=IF(Formatted!P3="","",""&Formatted!P3)


I've also tried:


=(IF(Formatted!L3="","",""&Formatted!L3)) AND (IF(Formatted!M3="","",""&Formatted!M3)) AND (IF(Formatted!N3="","",""&Formatted!N3)) AND (IF(Formatted!O3="","",""&Formatted!O3)) AND (IF(Formatted!P3="","",""&Formatted!P3))


which just returns "Value".


Any ideas? Your help would be most appreciated!!


Thanks

 

[Hui]

I think you want:

=IF(Formatted!L3="","",Formatted!L3)&IF(Formatted!M3="","",Formatted!M3)&IF(Formatted!N3="","",Formatted!N3)&IF(Formatted!O3="","",Formatted!O3)&IF(Formatted!P3="","",Formatted!PL3)


or


=IF(Formatted!L3="","",Formatted!L3)&char(10)&IF(Formatted!M3="","",Formatted!M3)&char(10)&IF(Formatted!N3="","",Formatted!N3)&char(10)&IF(Formatted!O3="","",Formatted!O3)&char(10)&IF(Formatted!P3="","",Formatted!PL3)

Which will put each entry on a separate row within the cell, if word wrap is enabled

 

[Rachael000]

Hi


Thanks for your answer, but neither of those forumla seems to work on my spreadsheet unfortunately. The first formula returns them all in one line with no spaces and the second formula comes up with #NAME?.


I worked it out in the end and came up with:


=CONCATENATE((IF(Formatted!M2="","",""&Formatted!M2)), (IF(Formatted!M2="","",", ")), (IF(Formatted!N2="","",""&Formatted!N2)), (IF(Formatted!N2="","",", ")), " ", (IF(Formatted!O2="","",""&Formatted!O2)), (IF(Formatted!O2="","",", "))," ",(IF(Formatted!P2="","",""&Formatted!P2)), (IF(Formatted!P2="","",", ")), " ",(IF(Formatted!Q2="","",Formatted!Q2)), ".")


which returns items in one line with commas separating them, but it's not ideal...


Can you explain the &chr(10)& command in your formula? Is that a normal way of separating lines within a cell? I haven't come across this before!


Thanks

 

[nazmul_muneer]

Rachael000,

Have you tried Hui's first solution? it is working.


Muneer

 

[nazmul_muneer]

Dear Rachael,

Hui's 2nd formula is the solution as you required. Write CHAR instead of chr. Copy the formula below.


=IF(Formatted!L3="","",Formatted!L3)&CHAR(10)&IF(Formatted!M3="","",Formatted!M3)&CHAR(10)&IF(Formatted!N3="","",Formatted!N3)&CHAR(10)&IF(Formatted!O3="","",Formatted!O3)&CHAR(10)&IF(Formatted!P3="","",Formatted!PL3)


And format the cell as Wrap Text


Regards,


Muneer

 

[Luke M]

To add to Muneer's post, the CHAR(10) is to generate the line feed (like hitting Alt+Enter when typing) into the text. Note if you don't have wrap text enabled, it will just show up as a square symbol.

 

[nazmul_muneer]

Yes Luke M,

we must format the cell as wrap text. For wrapping cell....

1. Select the cell

2. Press Alt+H+W


--Muneer

 

[Debraj]

Hi Rachael000,


* Select Cell "Address 1" to "PostCode"

* Copy all Data.. . for example B1 to F100..

* Copy the selection. and paste the same in Notepad, (it will delete the Table Structure)

* Copy all data from Notepad to WinWord (Word).

* In Word. Select All Cell.

* Find - ^t & Replace with - ^32

* Copy all the Data from Word and paste the same in Excel. for example in G1..


More Info.. In Word.

^p = Paragraph Change

^t = Tab Change Mark. what is in Excel as Next Cell

^32 is Enter and ^10 is CHAR(10)

You can View all Character Map in Home > Paragraph > Show / Hide ¶ (or Alt + H + 8)


Play with it.. You will find it use full in case of Long Data Need to Concatenate..


Regards,

Deb