Concatenate value from A where B values equal

[evanscm3]

Need an expert to help me out with this one -

I have two columns A,B with rows of data.
A contains unique vales. B contains non-unique values.


I need to concatenate all of the values from Column A together where their B values are equal using ; as delimiter. It doesnt matter where the conc string goes (in a new row underneath each group?

For example,

1,A
2,A
3,B
4,B

Becomes:

1,A
2,A
1;2

3,B
4,B
3;4



Any ideas?
Thanks
Chris

 

[TK_Hussar]

Hi
Assuming you have value in A1 that you want to concatentate in C1 if the values in A1 and B1 match use the following formula:
=IF(A1=B1,A1&";"&B1)
Good luck.
Tony

 

[evanscm3]

Thanks - not quite... sorry if i wasnt clear enough.

Column A contains n rows of values which i need to concatenate together across all rows which have the same value in B..

...so basically a group of rows which have the same value in B, conc all of their 'a' values together.

 

[Peter_SSs]

Assuming the column B values are grouped together, here is one way that I think does what you want. It also assumes that there is a heading row so the actual values start in row 2. Results are placed in column C beside the last item in each column B group.

Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CocatGroups()<br>    <SPAN style="color:#00007F">Dim</SPAN> Data, Results<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">ReDim</SPAN> Results(1 <SPAN style="color:#00007F">To</SPAN> lr, 1 <SPAN style="color:#00007F">To</SPAN> 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A1:B" & lr)<br>        Data = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> Data(i, 2) = Data(i - 1, 2) <SPAN style="color:#00007F">Then</SPAN><br>                Results(i, 1) = Results(i - 1, 1) & ";" & Data(i, 1)<br>                Results(i - 1, 1) = ""<br>            <SPAN style="color:#00007F">Else</SPAN><br>                Results(i, 1) = Data(i, 1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Offset(, 2).Resize(, 1).Value = Results<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[evanscm3]

works perfectly. thanks.

 

[tcnt9176]

How would I alter the above code if the data I am wanting to compare is in column A & I need it to concatenate column B?

Thanks!!

 

[Peter_SSs]

How would I alter the above code if the data I am wanting to compare is in column A & I need it to concatenate column B?

Thanks!!

From what I can understand of this you just want a reversal of the previous situation. In that case try this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CocatGroups_v2()<br>    <SPAN style="color:#00007F">Dim</SPAN> Data, Results<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">ReDim</SPAN> Results(1 <SPAN style="color:#00007F">To</SPAN> lr, 1 <SPAN style="color:#00007F">To</SPAN> 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A1:B" & lr)<br>        Data = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> Data(i, 1) = Data(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>                Results(i, 1) = Results(i - 1, 1) & ";" & Data(i, 2)<br>                Results(i - 1, 1) = ""<br>            <SPAN style="color:#00007F">Else</SPAN><br>                Results(i, 1) = Data(i, 2)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Offset(, 2).Resize(, 1).Value = Results<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Sample data/results:

Excel Workbook
	A	B	C
1	H1	H2
2	A	1
3	A	2	1;2
4	B	3
5	B	4	3;4
6
Concat

 

[tcnt9176]

Thanks!!! That is what I needed! I tried several combinations and I guess I changed too many of 2's but now that I see your script, it makes sense.