If cell contains text, copy text from another cell over a range of cells

Hi All,

Here is what I am trying to do.

I have a list of text quotes in column A. I then have column B which will have a Y entered if the quote is used in a presentation. I currently have around 100 quotes.

On a separate sheet, I want to be able to effectively say in one cell - If column B has text in it, copy the quote from column A. But the difficult part is how do I make this happen so that I can have multiple quotes being pulled into one cell?

I have tried concatenation, but I am struggling.

Would really appreciate your help guys,

Thanks so much

hi

can you share sample file??

Cheers!!

Hi - thanks Elliot - I have attached the file

That is so awesome! Thank you so so much! Can I simply change the formula for it to work in other sheets?

Yes you can.. but you need to copy macro also..

Thanks Elliot. I have now tried to do this by opening up the VBA and copy and pasting your code into the sheet I am working in. Should this work if I have also altered the cell values that you entered on the test sheet? I have attached a screen shot. Thank you so so much for your help.

Hi you need to put code in separate module, you can insert module by pressing Alt + F11 when you are in excel workbook, then select Insert & then module...

Paste your code & its done.. I was thinking to make a new function to suit your need, will give you that soon to make it more dynamic...


Cheers!!

Use below code:

Syntex is =xlJoin("Y",Range,n)

here Y is character which you are matching, Your range is like "A3:B7", where Col A contains proverb & Col B contains "Y", n is distance no. of column away from proverb column, in this case it is 2.

Please Login or Register  to view this content.

Also refer attachment..

Cheers!!

Hi Elliot,

Thanks so much for your help. I'm having difficulty in using the Macro's, so I have attached a file with three sheets in. I have explained what I would like to happen in each sheet and I would really appreciate your help if you can work these our for me.

Thank you so so much!

Check this...

Cheers!!

Thanks so much elliot - sheet 5 works great and concatinates exactly as I need, but unfortunately sheet 4 comes up with 'VALUE' when you put a Y in other field in that column. Unfortuantely sheet 6 does also not have code in, as I don't think I made this clear in my last post. Thanks so much!