• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

getting concatenate results to stack vertically

S

skinpup

New Member
Hello


Had a look around web for answer to this and they all seem to rapidly become discussions about vba and long strings of formulas I don't understand, still very new to excel, but this site has been a great help.


I have a table used to record audit checks and across the table for each item checked there is a comment box and I want to collect each of these comments and arrange them vertically in one 'actions box'.


I can get the text in to the box using concatenate, my question is how do I get it to arrange vertically in one cell?


So across the table I have


comment1 comment2 comment3 etc


and in the summary box i want to have them arranged in a vertical column in the one cell


comment1

comment2

comment3

etc


If you start to talk about macros or vba please be gentle!


skinpup
 
Hi skinpup,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended( Please see http://chandoo.org/forums/topic/posting-a-sample-workbook), and somebody surely will read your post and help you.


Now regarding your question, can you please download the workbook from here and check if this is what you are looking for.


https://hotfile.com/dl/173263538/60dfca2/GetComment.xlsm.xlsm.html


I have mentioned all the instructions in the file for your understanding.


Please let us know in case you need any further assistance regarding this.


Kaushik
 
cheers kaushik


thanks for the tips on getting started and the speedy reply.


yeah that file you sent looks like what i need i'll play around with it and see if i get it to work, like i said in post as soon as i see vba i start to worry :) but that one looks simple enough.


i'll let you know how i get on...
 
hello


i get the data to collect in the one cell. what i need it to do is arrange the different comments from each cell one above the other e.g.


first cell might have a comment this long second cell might be short third cell might be blank fourth cell my be very very very very very very long


if I was creating a list in one cell i would press alt - return to arrange them vertically.


first cell might have a comment this long

second cell might be short

third cell might be blank

fourth cell my be very very very very very very long


how do i get the macros to do this? the text in each cell will vary in length and often my be blank.


skinpup
 
Hi skinpup,


Are you getting the text from comment box or from a cell itself?


If from cell, then using Char(10) (the way I showed in the excel) you can do the job


If from an inserted comment box then that UDF would do the job.


And no matter how big the text length is as long as you wrap the cell where you run the formula (Char(10) or UDF)


However, if you still face any challenge, please consider uploading a sample workbook, I will try to provide you a best possible approach. To do this, please see the link below:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Kaushik
 
hello kaushik


here is a sample of the sheet with comments alongside the cells concerned


https://hotfile.com/dl/173279571/5481959/arrange_vertical.xlsx.html


i'm sure i'm missing something very obvious :)


skinpup
 
Hi skinpup,


This is with refrence to your uploaded sheet, you can use:


Code: 
=CONCATENATE(E3,CHAR(10),G3,CHAR(10),I3,CHAR(10),K3)


...and so on...and you should enable wrap text option for the cell. Hope that works!!!!


Regards,
 
thanks faseeh


=CONCATENATE(E3,CHAR(10),G3,CHAR(10),I3,CHAR(10),K3)


gets the text from each cell in to one, what i can't workout is getting it to form a list with the text from each cell on a new line inside the one cell? i've made sure the cell is wrapping text and tried resizing the cell but just doesn't work for me.


apologies if the answer is staring me in the face!
 
Hi,


I just openned a new sheet and tried this once again n it is working! Is there a small question mark placed within a small box apparent on the cell that contains this formula? This will be when u unwrap that cell. Adjust the width of the cell to fit the largest cell and hopefully it will work.


Faseeh
 
mmm no don't see the little ?


i'm working on excel for mac 2011 will this make a difference? sorry should have mentioned this earlier.


skinpup
 
yep. the mac version work differently. thanks for the replies.


apologies for not spotting my error sooner.


skinpup
 
You must log in or register to reply here.
Back
Top