Excel Macro -- Help

[joi3288]

Hi,

I have the following in my excel:

Code |Links
Mid-01 |Jane
Mid-01 |Nina
Mid-01 |Rain
Mid-01 |Cup
Mid-05 |Kent
Mid-05 |Shy
Mid-07 |Sher
Mid-07 |Penny
Mid-07 |Gin

I want to simplify the above into something like this:
Code |Links
Mid-01 |Jane, Nina, Rain, Cup
Mid-05 |Kent, Shy
Mid-07 |Sher, Penny, Gin

So, basically each value under Links must be separated with comma and a white space.

I have thousands of records, here. I would appreciate if someone help me make my work much easier, much faster, and error-free.

Thanks.

 

[Peter_SSs]

Test this in a copy of your workbook. Assumes data strats in cell A1 and the result is to replace the original data.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    <SPAN style="color:#00007F">Dim</SPAN> Data, Result<br>    <SPAN style="color:#00007F">Dim</SPAN> L <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>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("B" & Rows.Count).End(xlUp))<br>        Data = .Value<br>        L = <SPAN style="color:#00007F">UBound</SPAN>(Data, 1)<br>        <SPAN style="color:#00007F">ReDim</SPAN> Result(1 <SPAN style="color:#00007F">To</SPAN> L, 1 <SPAN style="color:#00007F">To</SPAN> 2)<br>        k = 1<br>        Result(1, 1) = Data(1, 1)<br>        Result(1, 2) = Data(1, 2)<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> L<br>            <SPAN style="color:#00007F">If</SPAN> Data(i, 1) = Data(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>                Result(k, 2) = Result(k, 2) & ", " & Data(i, 2)<br>            <SPAN style="color:#00007F">Else</SPAN><br>                k = k + 1<br>                Result(k, 1) = Data(i, 1)<br>                Result(k, 2) = 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>        .Value = Result<br>        .EntireColumn.AutoFit<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>

 

[joi3288]

Thanks. Just exactly what I needed.