in microsoft excel: alpha by name, keeping #’s
October 3, 2011I want to alphabetize a list by channel names, keeping the channel #’s, please advise, such as:
2 WCBS New York (CBS)
3 WPXN New York (ION)
4 WNBC New York (NBC)
5 WNYW New York (FOX)
6 Univision
7 WABC New York (ABC)
8 New York 1 News
9 My9 New York (MNT-WWOR)
10 WLNY Riverhead (IND)
11 WPIX (11) New York
12 News 12 Brooklyn
13 WNET New York (PBS)
14 MSG Varsity
15 QVC
16 WNJU Linden (Telemundo)
17 TeleFutura
18 HSN
19 WRNN Kingston (IND)
20 WMBC Newton (IND)
21 WLIW Plainview (PBS)
22 NYC Life
23 MSNBC
24 CNBC
25 CNN
26 FOX News Channel
27 Discovery Channel
28 TLC
30 The NET
31 Disney Channel
32 Cartoon Network
33 Nickelodeon
34 TV Land
35 ESPN2
36 ESPN
37 TNT
38 USA Network
39 TBS
40 FX
41 Turner Classic Movies
42 WE tv
43 AMC
44 Bravo
45 Lifetime
46 A&E
47 History
48 Syfy
49 ABC Family
50 Comedy Central
51 E!
52 VH1
53 MTV
54 BET
55 MTV2
56 Spike TV
57 Animal Planet
58 truTV
59 Religious Programming
60 SportsNet New York
61 News 12 Traffic & Weather
62 The Weather Channel
63 Galavisión
64 SoapNet
65 C-SPAN
66 C-SPAN2
67 Brooklyn Community Access TV
68 Brooklyn Community Access TV
69 Brooklyn Community Access TV
70 Brooklyn Community Access TV
71 OTB on NYC TV
72 NYC Drive
73 NYC World
74 NYC Government
75 CUNY on NYC TV
76 GSN
77 CNN Headline News
78 fuse
80 Cablevision Channel Guide
81 Oxygen
82 HBO
83 IFC
84 Showtime
85 Cinemax
86 The Movie Channel
87 MSG
88 MSG Plus
89 YES Network
90 Showtime Too
91 Flix
92 Pay Per View
93 Pay Per View
94 Playboy TV (Adult)
95 Spice Xcess (Adult)
96 Travel Channel
97 Food Network
98 HGTV
99 Speed Channel
100 iO Digital Channel Guide
101 BBC America
102 C-SPAN3
103 EuroNews
104 BBC World News
105 Bloomberg TV
106 FOX Business Network
108 Live Well
109 NBC NY Non-Stop
110 Universal Sports
114 Antenna TV
115 Estrella TV
116 NYS Legislative TV
118 Leased Access
120 The Hub
121 Disney XD
122 Nicktoons Network
123 Nick Jr.
124 TeenNick
125 Boomerang
131 Kids Thirteen
132 WLIW World
133 WLIW Create
134 WTBY (54)
135 EWTN
136 Daystar
137 Telecare
140 ESPN Classic
141 ESPNews
142 FOX Soccer Channel
143 CBS Sports Network
144 ESPNU
145 The Golf Channel
146 VERSUS
148 NBA TV
149 MLB Network
158 National Geographic Wild
160 Bio
161 H2
Fyi: numbers & texts are in one column.
You can use the following event handler to sort as you wish. This assumes the data is in column A and column B is available for temp use.
Copy the following event handler to the clipboard:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i, LastRow
LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For i = 1 To LastRow
For j = 1 To Len(Cells(i, “A”))
If Not IsNumeric(Mid(Cells(i, “A”), j, 1)) Then
Cells(i, “B”).Value = Right(Cells(i, “A”), Len(Cells(i, “A”)) - j)
GoTo here
End If
Next
here:
Next
Columns(”A:B”).Select
Selection.Sort Key1:=Range(”B1″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns(”B:B”).ClearContents
Range(”A1″).Select
End Sub
Select the worksheet containing the data and right click the sheet tab.
Select ‘View Code’.
Paste the event handler into the editing area to the right.
Close the VBE and return to the worksheet.
Double click any cell to sort the data. If additional data is added, just double click any cell again.
Will this do:
46A&E
49ABC Family
43AMC
57Animal Planet
114Antenna TV
101BBC America
104BBC World News
54BET
160Bio
105Bloomberg TV
125Boomerang
44Bravo
67Brooklyn Community Access TV
68Brooklyn Community Access TV
69Brooklyn Community Access TV
70Brooklyn Community Access TV
80Cablevision Channel Guide
32Cartoon Network
143CBS Sports Network
85Cinemax
24CNBC
25CNN
77CNN Headline News
50Comedy Central
65C-SPAN
66C-SPAN2
102C-SPAN3
75CUNY on NYC TV
136Daystar
27Discovery Channel
31Disney Channel
121Disney XD
51E!
36ESPN
140ESPN Classic
35ESPN2
141ESPNews
144ESPNU
115Estrella TV
103EuroNews
135EWTN
91Flix
97Food Network
106FOX Business Network
26FOX News Channel
142FOX Soccer Channel
78fuse
40FX
63Galavisión
76GSN
161H2
82HBO
98HGTV
47History
18HSN
83IFC
100iO Digital Channel Guide
131Kids Thirteen
118Leased Access
45Lifetime
108Live Well
149MLB Network
87MSG
88MSG Plus
14MSG Varsity
23MSNBC
53MTV
55MTV2
9My9 New York (MNT-WWOR)
158National Geographic Wild
148NBA TV
109NBC NY Non-Stop
8New York 1 News
12News 12 Brooklyn
61News 12 Traffic & Weather
123Nick Jr.
33Nickelodeon
122Nicktoons Network
72NYC Drive
74NYC Government
22NYC Life
73NYC World
116NYS Legislative TV
71OTB on NYC TV
81Oxygen
92Pay Per View
93Pay Per View
94Playboy TV (Adult)
15QVC
59Religious Programming
84Showtime
90Showtime Too
64SoapNet
99Speed Channel
95Spice Xcess (Adult)
56Spike TV
60SportsNet New York
48Syfy
39TBS
124TeenNick
137Telecare
17TeleFutura
145The Golf Channel
120The Hub
86The Movie Channel
30The NET
62The Weather Channel
28TLC
37TNT
96Travel Channel
58truTV
41Turner Classic Movies
34TV Land
110Universal Sports
6Univision
38USA Network
146VERSUS
52VH1
7WABC New York (ABC)
2WCBS New York (CBS)
42WE tv
133WLIW Create
21WLIW Plainview (PBS)
132WLIW World
10WLNY Riverhead (IND)
20WMBC Newton (IND)
4WNBC New York (NBC)
13WNET New York (PBS)
16WNJU Linden (Telemundo)
5WNYW New York (FOX)
11WPIX (11) New York
3WPXN New York (ION)
19WRNN Kingston (IND)
134WTBY (54)
89YES Network

Add A Comment