Hello everybody, I have the need to concatenate multiple values if certain criterias are met.
I have a table that goes like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Mat
[/TD]
[TD]Value
[/TD]
[TD]Val2
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]40
[/TD]
[TD]X
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]D200
[/TD]
[TD]41
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]45
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]D200
[/TD]
[TD]30
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]HD2O
[/TD]
[TD]45
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]41
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]44
[/TD]
[TD]R
[/TD]
[/TR]
</tbody>[/TABLE]
And I have another table I need to fill with the concatenated values in the other table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Mat
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]40, 45
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]D200
[/TD]
[TD]41
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]D200
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]HD2O
[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]41, 44
[/TD]
[/TR]
</tbody>[/TABLE]
If you notice, the UNIQUE value is COLOR&MAT. The values must be unique, not repeated. There might be instances where the COLOR&MAT will have repeated values, but they will be rare. So I need to modify this VBA code I found two be able to do this.
I don't know who wrote this code, and I am not sure if I can post the link to where I found it, but cheers to whoever wrote this!
TL;DR
The problem with this function is that it just accepts one criteria and it repeats all values. I need at least two criterias and for it to give back unique values only.
Plus
I am not sure how hard will it be to do this, but if there is a way to concatenate an additional value in another column (VAL2) like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Mat
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]40 - X, 45 - T
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]D200
[/TD]
[TD]41 - M
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]D200
[/TD]
[TD]30 - S
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]HD2O
[/TD]
[TD]45 - T
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]41 - M, 44
[/TD]
[/TR]
</tbody>[/TABLE]
I will GREATLY satisfied with my original request, but if someone can help me with the plus in one go I will very much appreciate it.
I have a table that goes like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Mat
[/TD]
[TD]Value
[/TD]
[TD]Val2
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]40
[/TD]
[TD]X
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]D200
[/TD]
[TD]41
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]45
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]D200
[/TD]
[TD]30
[/TD]
[TD]S
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]HD2O
[/TD]
[TD]45
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]41
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]44
[/TD]
[TD]R
[/TD]
[/TR]
</tbody>[/TABLE]
And I have another table I need to fill with the concatenated values in the other table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Mat
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]40, 45
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]D200
[/TD]
[TD]41
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]D200
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]HD2O
[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]41, 44
[/TD]
[/TR]
</tbody>[/TABLE]
If you notice, the UNIQUE value is COLOR&MAT. The values must be unique, not repeated. There might be instances where the COLOR&MAT will have repeated values, but they will be rare. So I need to modify this VBA code I found two be able to do this.
I don't know who wrote this code, and I am not sure if I can post the link to where I found it, but cheers to whoever wrote this!
TL;DR
The problem with this function is that it just accepts one criteria and it repeats all values. I need at least two criterias and for it to give back unique values only.
Code:
<code class="vb keyword">Function</code> <code class="vb plain">ConcatenateIf(CriteriaRange </code><code class="vb keyword">As</code> <code class="vb plain">Range, Condition </code><code class="vb keyword">As</code> <code class="vb keyword">Variant</code><code class="vb plain">, ConcatenateRange </code><code class="vb keyword">As</code> <code class="vb plain">Range, </code><code class="vb keyword">Optional</code> <code class="vb plain">Separator </code><code class="vb keyword">As</code> <code class="vb keyword">String</code> <code class="vb plain">= </code><code class="vb string">","</code><code class="vb plain">) </code><code class="vb keyword">As</code> <code class="vb keyword">Variant</code>
<code class="vb comments">'Update 20150414</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xResult </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb keyword">If</code> <code class="vb plain">CriteriaRange.Count <> ConcatenateRange.Count </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb plain">ConcatenateIf = CVErr(xlErrRef)</code>
<code class="vb spaces"> </code><code class="vb keyword">Exit</code> <code class="vb keyword">Function</code>
<code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb keyword">For</code> <code class="vb plain">i = 1 </code><code class="vb keyword">To</code> <code class="vb plain">CriteriaRange.Count</code>
<code class="vb spaces"> </code><code class="vb keyword">If</code> <code class="vb plain">CriteriaRange.Cells(i).Value = Condition </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb plain">xResult = xResult & Separator & ConcatenateRange.Cells(i).Value</code>
<code class="vb spaces"> </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb keyword">Next</code> <code class="vb plain">i</code>
<code class="vb keyword">If</code> <code class="vb plain">xResult <> </code><code class="vb string">""</code> <code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb plain">xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)</code>
<code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb plain">ConcatenateIf = xResult</code>
<code class="vb keyword">Exit</code> <code class="vb keyword">Function</code>
<code class="vb keyword">End</code> <code class="vb keyword">Function</code>
Plus
I am not sure how hard will it be to do this, but if there is a way to concatenate an additional value in another column (VAL2) like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Mat
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]X100
[/TD]
[TD]40 - X, 45 - T
[/TD]
[/TR]
[TR]
[TD]BLUE
[/TD]
[TD]D200
[/TD]
[TD]41 - M
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]D200
[/TD]
[TD]30 - S
[/TD]
[/TR]
[TR]
[TD]YELLOW
[/TD]
[TD]HD2O
[/TD]
[TD]45 - T
[/TD]
[/TR]
[TR]
[TD]RED
[/TD]
[TD]X100
[/TD]
[TD]41 - M, 44
[/TD]
[/TR]
</tbody>[/TABLE]
I will GREATLY satisfied with my original request, but if someone can help me with the plus in one go I will very much appreciate it.