Combining multiple cells from two columns

[roshanakhtar]

I'm trying to combine the contents of multiple cells in one cell. One column has reference no. and the other one has multiple cells containing related information. I want to combine these multiple cells containing related information in one cell, in another column. I tried Concatenate formula, but auto fill option would caused problem as any given reference number has random number of related cells even though limited to a maximum of six cells. Sheet is attached here:-

Please help!

[NBVC]

Add a helper column first in C2 enter formula:

=IF(B2="","",LOOKUP(9.999E+307,A$2:A2))

copied down.

Then add this UDF in your VB Editor (Alt+F11, Insert|Module)

Please Login or Register  to view this content.

Then with your unique list of codes in F4 down, add formula to G4:

=SUBSTITUTE(SUBSTITUTE(TRIM(aconcat(IF($C$2:$C$26=F4,SUBSTITUTE($B$2:$B$26," ","^^"),"")," "))," ",", "),"^^"," ")

confirm it with CTRL+SHIFT+ENTER not just ENTER and copy it down.

[roshanakhtar]

Thank you very much NBVC

I tried this, but as I am not used to to modules and macros, I have added that UDF in VB editor but do not know how to save this and use this. You are really a great guy and I hope you will take some pain to explain it for me.

Thanking you again...

[NBVC]

If you put it in the VB editor, then saving the sheet, saves the macro too.

After you entered the code, go back to the main sheet, go to cell F4, enter my formula in that cell, and before exiting the cell, hold the cTRL+SHIFT keys down. Then hit ENTER. You will see { } brackets appear around the formula, then you can copy it down.

Note: If you need to change the ranges in the formula for a bigger database, then you will need to reconfirm with CTRL+SHIFT+ENTER and copy down again.

I have attached your workbook with updates.

[roshanakhtar]

Thank you again NBVC

It really did nice as I needed and saved my time a lot. I am really thankful to you.