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

[TABLE="width: 630"]
<colgroup><col width="77"><col width="553"></colgroup><tbody>[TR]
[TD="width: 77"]Solutions[/TD]
[TD="width: 553"]Hosts[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 630"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Solution1[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution2[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution3[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution4[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution5[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution6[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution7[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution8[/TD]
[TD]Server1 [10.150.0.157], Server2 [10.150.0.153][/TD]
[/TR]
[TR]
[TD]Solution9[/TD]
[TD]Server3 [10.150.0.251], Server4 [10.150.8.252], Server3 [10.150.8.251]
[/TD]
[/TR]
[TR]
[TD]Solution10[/TD]
[TD]Server5 [10.150.0.157], Server6 [10.150.0.153][/TD]
[/TR]
</tbody>[/TABLE]

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