Combine text from multiple cell with some condition

[rockybell_77]

Hello,
I have excel sheet where i have huge data. Column A contains vendor code (a number) and column B contains Name of vendor in first cell and Address in subsequent cells in same column B. Number of cells occupied in column B for address is not fixed it can be 2,3 or more.

I want to merge name and address in column B for each vendor code in column A

Example

Cell Column A Column B

<tbody>
</tbody>

Vendor Code	Vendor Name and address
1234	Asia Private Limited -Goa
	Plot No. M - 14 to M- 18,Verna Industrial estate
	Goa
91264	Technologies Inc, TX
	4501 Attwa, 477590,United States of America

<tbody>
</tbody>

I want to combine B2,b3,b4 in C1, same way B5,B6 in C2 and so on
Can somebody suggest formula to do this task

Thanks in Advance

 

[rpaulson]

Is a Macro an Option?

 

[GR00007]

Give this a try in cell C2, copied down, you could then filter column A to exclude null/blank:

=IF(A3="",B2 & "; " & C3,B2)

Vendor Code	Vendor Name and address	CalcAddress
1234	Asia Private Limited -Goa	Asia Private Limited -Goa; Plot No. M - 14 to M- 18,Verna Industrial estate; Goa
	Plot No. M - 14 to M- 18,Verna Industrial estate	Plot No. M - 14 to M- 18,Verna Industrial estate; Goa
	Goa	Goa
91264	Technologies Inc, TX	Technologies Inc, TX; 4501 Attwa, 477590,United States of America
	4501 Attwa, 477590,United States of America	4501 Attwa, 477590,United States of America
5678	Marvin Martian	Marvin Martian; 123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol
	123 Conqueror Lane	123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol
	Canal 4, 3rd level	Canal 4, 3rd level; Southern Hemisphere; Mars; Sol
	Southern Hemisphere	Southern Hemisphere; Mars; Sol
	Mars	Mars; Sol
	Sol	Sol

<tbody>
</tbody>