manipulate list of data into separate columns

D

daveaton

New Member
Joined
Apr 6, 2010
Messages
1
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?


moz-screenshot.png
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Hi, Try this to start:-
Code: 
[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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Locked
  • Question Question
parse data from text file
Replies
1
Views
638
Fluff
Fluff
R
  • Question Question
parse data from text file
Replies
5
Views
886
DanteAmor
DanteAmor
S
  • Question Question
Identify number from a separate list and copy to separate sheet
Replies
3
Views
662
mumps
mumps
B
  • Question Question
Index and match vertical and horizontal data
Replies
6
Views
1K
DanteAmor
DanteAmor
G
  • Question Question
data extraction from a table in a particular format
Replies
3
Views
448
Joe4
Joe4

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top