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
[TABLE="width: 692"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 424"]
<tbody>[TR]
[TD]Vendor Code[/TD]
[TD]Vendor Name and address[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD] Asia Private Limited -Goa[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Plot No. M - 14 to M- 18,Verna Industrial estate[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Goa[/TD]
[/TR]
[TR]
[TD]91264[/TD]
[TD] Technologies Inc, TX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 4501 Attwa, 477590,United States of America[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="class: grid, width: 711"]
<tbody>[TR]
[TD]Vendor Code[/TD]
[TD]Vendor Name and address[/TD]
[TD]CalcAddress[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD]Asia Private Limited -Goa[/TD]
[TD]Asia Private Limited -Goa; Plot No. M - 14 to M- 18,Verna Industrial estate; Goa[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Plot No. M - 14 to M- 18,Verna Industrial estate[/TD]
[TD]Plot No. M - 14 to M- 18,Verna Industrial estate; Goa[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Goa[/TD]
[TD]Goa[/TD]
[/TR]
[TR]
[TD="align: right"]91264[/TD]
[TD]Technologies Inc, TX[/TD]
[TD]Technologies Inc, TX; 4501 Attwa, 477590,United States of America[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4501 Attwa, 477590,United States of America[/TD]
[TD]4501 Attwa, 477590,United States of America[/TD]
[/TR]
[TR]
[TD="align: right"]5678[/TD]
[TD]Marvin Martian[/TD]
[TD]Marvin Martian; 123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123 Conqueror Lane[/TD]
[TD]123 Conqueror Lane; Canal 4, 3rd level; Southern Hemisphere; Mars; Sol[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Canal 4, 3rd level[/TD]
[TD]Canal 4, 3rd level; Southern Hemisphere; Mars; Sol[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Southern Hemisphere[/TD]
[TD]Southern Hemisphere; Mars; Sol[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mars[/TD]
[TD]Mars; Sol[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sol[/TD]
[TD]Sol[/TD]
[/TR]
</tbody>[/TABLE]