How to concatenate mutiple rows and show it in a single Cell Conditionally

Team,

I have a requirement in Excel do concatenate multiple rows of column D (D2 to D100) one below the other and show it in a single cell E2. So for that I have applied the formula in E2 as below.

Formula

=D2&"
"&D3&"
"&D4&"
....
....

"&D99&"
"&D100&""

But my requirement is not fixed i.e (1 to 100). it is conditionally based if we have upto 60 rows, we have to write the above formula upto D60.

Is there anyway we can write a function to concatenate mutiple rows (D2 to D1000) upto a fixed number 1000 and if the text do not appear in the Cells we should not show it in Cell E2 conditionally.

Thanks!
Kiran

Hi Kandavalli,

This code shall suffice your requirements... No changes are required.

Just paste this in a standard module and assign it to a button.

Rename the Sheet name if your sheet name isn't Sheet1.

Please Login or Register  to view this content.

Hi Mike,

I almost there, The result should come one below the other.

Instead of like this

Result 1, Result 2, Result 3.....

I need the output like this in a single Cell E2.

Result 1
Result 2
Result 3
....
...

1) Vertical concatenate
=Vconc(A1:A1000,CHAR(10))

Please Login or Register  to view this content.

2) Horizontal Concatenane
=HConc(A12:Z12,CHAR(10))

Please Login or Register  to view this content.

Thanks a lot Mike. The solution got worked.

Thanks Again

Can I have your gmail ID please.