Excel Line Split

[jonnyp138]

Can anyone help with a vba script that will strip each server host and ip address from column B into a new line in a new sheet and apply the solution from column A next to each server in the new sheet?

Example: Current Layout

Solutions

Hosts

<colgroup><col width="77"><col width="553"></colgroup><tbody>
</tbody>

Solution1	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution2	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution3	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution4	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution5	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution6	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution7	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution8	Server1 [10.150.0.157], Server2 [10.150.0.153]
Solution9	Server3 [10.150.0.251], Server4 [10.150.8.252], Server3 [10.150.8.251]
Solution10	Server5 [10.150.0.157], Server6 [10.150.0.153]

<colgroup><col><col></colgroup><tbody>
</tbody>

Each server needs seperating to a seperate line with the correct solution next to it?

Hope this makes sense

 

[MickG]

Perhaps this:-
Results in sheet2.

[COLOR="Navy"]Sub[/COLOR] MG27Oct13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, P [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, ", ")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Sp(n)) [COLOR="Navy"]Then[/COLOR] Dic.Add Sp(n), New Collection
            Dic(CStr(Sp(n))).Add CStr(Dn.Offset(, -1).Value)
     [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn

c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] Dic(K)
        c = c + 1
        Sheets("Sheet2").Cells(c, "A") = K
        Sheets("Sheet2").Cells(c, "B") = P
    [COLOR="Navy"]Next[/COLOR] P
 [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick