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]
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]