Concatenate Break Formula

Craig Naylon

New Member
Joined
Jan 26, 2013
Messages
2
Hi,

The below table represents what I am currently doing manually but would am trying to come up with a formula to do for me. Or, some combination of formulas to get to the result I need.

I need to write the result in the HTML column Currently I'm using the concatenation formula in the Formula column. It is simply concatenating the html tags and the data in the Description column for every cell where the Prod Ref # is the same. I'm copying / pasting the formula into the cell where the next Prod Ref # changes and removing the cell references or adding cell references as necessary to the formula. It's VERY time consuming and inefficient.

I have a .xls with 10,000's of lines. I can't figure out how to automatically break and start a new concatenation formula as the Prod Ref # changes.

Any Suggestions?

Thanks.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Prod Ref #[/TD]
[TD]Description[/TD]
[TD]Formula[/TD]
[TD]HTML[/TD]
[/TR]
[TR]
[TD]201000[/TD]
[TD]DESC1[/TD]
[TD]'=CONCATENATE("
",B2,"

",B3,"

",B4,"

",B5,"

",B6,"

",B7,"
")[/TD]
[TD]DESC1

DESC2

DESC3

DESC4

DESC5

DESC6
[/TD]
[/TR]
[TR]
[TD]201000[/TD]
[TD]DESC2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201000[/TD]
[TD]DESC3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201000[/TD]
[TD]DESC4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201000[/TD]
[TD]DESC5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201000[/TD]
[TD]DESC6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201002[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]'=CONCATENATE("
",B8,"

",B9,"

",B10,"
")[/TD]
[TD]DESC1

DESC2

DESC3
[/TD]
[/TR]
[TR]
[TD]201002[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201002[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201004[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]'=CONCATENATE("
",B11,"

",B12,"

",B13,"

",B14,"

",B15,"
")[/TD]
[TD]DESC1

DESC2

DESC3

DESC4

DESC5
[/TD]
[/TR]
[TR]
[TD]201004[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201004[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201004[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201004[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD]DESC5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Use two formulae. Column C to build up the concatenation, and column D to only show it on the first row of a product.
Put these formulae in C2 and D2, then copy down:
=B2&IF(A2=A3,C3,"")
=IF(A1=A2,"",C2)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top