manipulate list of data into separate columns

[daveaton]

Hello,
I am using Excel 2007.
I have a list of data that I am trying to parsed out intto separate columns depending on criteria in the first column/list of data.

Example data:
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="477" height="741"><col style="width: 210pt;" width="280"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 210pt;" width="280" height="20">dial-peer voice 10000 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 5128793604</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 999 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 500 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 91..........</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 10911 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 911</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 501 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 99...........</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">clid network-number 7183706200</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 3 voip</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 91719579....</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 4 voip</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 7195796224</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 2 voip</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 918335....</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 5000 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">translation-profile outgoing MASKOUT</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 91800[2-9]......</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">clid network-number 2623241431</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 5005 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">translation-profile outgoing MASKOUT</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 914023937444</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">dial-peer voice 6000 pots</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">translation-profile outgoing S2420_transfer</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">destination-pattern 917403895585</td> </tr> </tbody></table>

Expected output:
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="943" height="380"><col style="width: 154pt;" width="205"> <col style="width: 215pt;" width="287"> <col style="width: 173pt;" width="230"> <col style="width: 164pt;" width="219"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 154pt;" width="205" height="20">dial-peer voice 10000 pots</td> <td style="width: 215pt;" width="287">destination-pattern 5128793604</td> <td style="width: 173pt;" width="230">
</td> <td style="width: 164pt;" width="219">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 999 pots</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 500 pots</td> <td>destination-pattern 91..........</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 10911 pots</td> <td>destination-pattern 911</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 501 pots</td> <td>destination-pattern 99...........</td> <td>
</td> <td>clid network-number 7183706200</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 3 voip</td> <td>destination-pattern 91719579....</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 4 voip</td> <td>destination-pattern 7195796224</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 2 voip</td> <td>destination-pattern 918335....</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 5000 pots</td> <td>translation-profile outgoing MASKOUT</td> <td>destination-pattern 91800[2-9]......</td> <td>clid network-number 2623241431</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 5005 pots</td> <td>translation-profile outgoing MASKOUT</td> <td>destination-pattern 914023937444</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">dial-peer voice 6000 pots</td> <td>translation-profile outgoing S2420_transfer</td> <td>destination-pattern 917403895585</td> <td>
</td> </tr> </tbody></table>

Is there an easy way this can be done?

 

[MickG]

Hi, Try this to start:-

[COLOR="Navy"]Sub[/COLOR] MG06Apr39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Omax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
ReDim ray(1 To Rng.count, 1 To Columns.count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] InStr(Dn, "dial-peer voice") > 0 [COLOR="Navy"]Then[/COLOR]
        ac = 1
        Rw = Rw + 1
        ray(Rw, ac) = Dn
    [COLOR="Navy"]Else[/COLOR]
        ac = ac + 1
        ray(Rw, ac) = Dn
        Omax = Application.Max(ac, Omax)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Range("C1").Resize(Rw, Omax) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick