Sorting addresses with numbers and text

[bjkiwi007]

Hi there,
I am trying to sort a column with both numbers and text. For example, 1, 10, 1a, 2a, 2b etc. These are address numbers and I want to sort firstly by street name and then by house number. The street name part works fine but every time I sort the street number column I get 1, 4, 6, 1a, 10a etc. Can some one please help me in sorting this so that the number 1 is always at the top regardless if it has an a or b behind it. eg; 1, 1a, 1b, 2, 4, 6, 6a, 6b etc..
Thanks in advance and any help would be greatly appreciated.
BJkiwi007
Example below. As you can see, 10a and 8a are at the bottom but I want them to be after 8 and 10 respectively.
<table border="0" cellpadding="0" cellspacing="0" width="104"><colgroup><col style="mso-width-source:userset;mso-width-alt:1901; width:39pt" span="2" width="52"> </colgroup><tbody><tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">1</td> <td class="xl66" style="width:39pt" width="52">1</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">2</td> <td class="xl66" style="width:39pt" width="52">2</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">3</td> <td class="xl66" style="width:39pt" width="52">3</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">4</td> <td class="xl66" style="width:39pt" width="52">4</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">5</td> <td class="xl66" style="width:39pt" width="52">5</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">7</td> <td class="xl66" style="width:39pt" width="52">7</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">8</td> <td class="xl66" style="width:39pt" width="52">8</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">9</td> <td class="xl66" style="width:39pt" width="52">9</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">10</td> <td class="xl66" style="width:39pt" width="52">10</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">11</td> <td class="xl66" style="width:39pt" width="52">11</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">12</td> <td class="xl66" style="width:39pt" width="52">12</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">14</td> <td class="xl66" style="width:39pt" width="52">14</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">15</td> <td class="xl66" style="width:39pt" width="52">15</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">16</td> <td class="xl66" style="width:39pt" width="52">16</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">18</td> <td class="xl66" style="width:39pt" width="52">18</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">22</td> <td class="xl66" style="width:39pt" width="52">22</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">24</td> <td class="xl66" style="width:39pt" width="52">24</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">26</td> <td class="xl66" style="width:39pt" width="52">26</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">28</td> <td class="xl66" style="width:39pt" width="52">28</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">10a</td> <td class="xl66" style="width:39pt" width="52">10a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">8a</td> <td class="xl66" style="width:39pt" width="52">8a</td> </tr> </tbody></table>

 

[MickG]

Hi, If you can't get it working by other means and would like to go down the code route, can you show a simple layout of you Addresses within the sheet.
Mick

 

[bjkiwi007]

Hi Mick, I still haven't been able to sort it how I would like too.
My street names are in column C and the house numbers are in column D. The house numbers are at present like this:

-- removed inline image ---
<table border="0" cellpadding="0" cellspacing="0" width="52"><colgroup><col style="mso-width-source:userset;mso-width-alt:1901;width:39pt" width="52"> </colgroup><tbody><tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">11</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">11a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">13</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">16</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">17</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">18</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">20</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">21</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">22</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">23</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">24</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">25</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">26</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">100</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">102</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">104</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">104a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">106</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">106a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">11b</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">201</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">202</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">203</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">204</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">206</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">208a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">208b</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">209</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">210</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">211</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">215</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">216</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">217</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">218</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">219</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">220</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">220a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">222</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">223</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">224</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">225</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">226</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">227</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">227a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">228</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">231</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">232</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">233</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">234</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">234a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">235</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">236</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">237</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">238</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">239</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">240</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">241</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">241a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">242</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">243</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">245</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">248</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">249</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">250</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">251</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">252</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">253</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">255</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">257</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">258</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">259</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">260</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">262</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">262a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">263</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">264</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">265</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">269</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">27</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">270</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">271</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">272</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">274</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">275</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">277</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">277a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">279</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">27a</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">28</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">281</td> </tr> <tr style="mso-height-source:userset;height:12.0pt" height="16"> <td class="xl66" style="height:12.0pt;width:39pt" height="16" width="52">282</td> </tr> </tbody></table>
There are 1300 records or there about and as you can see the house numbers do not sort in the correct order. Do you need more info or is this enough?
Thanks once again
BJ

 

[sulakvea]

hi, did you try what i suggested?

i did with the sample you just posted - took me 10 seconds to sort.
replace letters with number / then replace those .01 and .02 back with letters.

hi, you shouldnt have too many "a"'s and "b"'s in the list right? say you have 10 at most - like a, b, c, aa, etc.

replace "a" (i.e. in the entire column, with ctrl+h) with .01 (make sure you spell it that way, i.e. replace "a" with .01, not 0.01). replace "b" with .02, etc. So now your 10a will become 10.01, i.e. a number, and once sorted - will end up right after 10

this is what i get:

11
11a
11b
13
16
17
18
20
21
22
23
24
25
26
27
27a
28
100
102
104
104a
106
106a
201
202
203
204
206
208a
208b
209
210
211
215
216
217
218
219
220
220a
222
223
224
225
226
227
227a
228
231
232
233
234
234a
235
236
237
238
239
240
241
241a
242
243
245
248
249
250
251
252
253
255
257
258
259
260
262
262a
263
264
265
269
270
271
272
274
275
277
277a
279
281
282

 

[MickG]

Try this Street Names in "C" Numbers in "D".
This code should also sort the individual "a","b", "c" order as well

[COLOR="Navy"]Sub[/COLOR] MG28Apr48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ray
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] TNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp3 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Columns("E:E").Insert Shift:=xlToRight
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & rows.Count).End(xlUp))
        ray = Rng.Resize(, 3).value
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray)
    [COLOR="Navy"]If[/COLOR] Not IsNumeric(Right(ray(n, 2), 1)) [COLOR="Navy"]Then[/COLOR]
        TNum = Right(ray(n, 2), 1)
        ray(n, 2) = Left(ray(n, 2), Len(ray(n, 2)) - 1)
        ray(n, 3) = TNum
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] i = 1 To UBound(ray)
    [COLOR="Navy"]For[/COLOR] j = i To UBound(ray)
        [COLOR="Navy"]If[/COLOR] Val(ray(j, 2)) < Val(ray(i, 2)) [COLOR="Navy"]Then[/COLOR]
            Temp = Val(ray(i, 2))
            Temp2 = ray(i, 3)
             Temp3 = ray(i, 1)
                ray(i, 2) = ray(j, 2)
                ray(i, 3) = ray(j, 3)
                 ray(i, 1) = ray(j, 1)
                    ray(j, 2) = Temp
                    ray(j, 3) = Temp2
                    ray(j, 1) = Temp3
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] j
[COLOR="Navy"]Next[/COLOR] i
Rng.Resize(, 3) = ray
Call Alpha(Rng)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) <> "" [COLOR="Navy"]Then[/COLOR]
        Dn = Dn & Dn.Offset(, 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Columns("E:E").Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] Alpha(Rng [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] ray, n
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dup [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 1)
    [COLOR="Navy"]If[/COLOR] Application.CountIf(Rng.Offset(, 1), Dn) > 1 And Dn <> Dup [COLOR="Navy"]Then[/COLOR]
            Dup = Dn
            [COLOR="Navy"]Set[/COLOR] nRng = Dn.Offset(, -1).Resize(Application.CountIf(Rng.Offset(, 1), Dn))
            ray = nRng.Resize(, 3).value
        [COLOR="Navy"]For[/COLOR] i = 1 To UBound(ray)
            [COLOR="Navy"]For[/COLOR] j = i To UBound(ray)
                [COLOR="Navy"]If[/COLOR] ray(j, 3) < ray(i, 3) [COLOR="Navy"]Then[/COLOR]
                    Temp = ray(i, 1)
                    Temp1 = ray(i, 2)
                    Temp2 = ray(i, 3)
                    ray(i, 1) = ray(j, 1)
                    ray(i, 2) = ray(j, 2)
                    ray(i, 3) = ray(j, 3)
                        ray(j, 1) = Temp
                        ray(j, 2) = Temp1
                        ray(j, 3) = Temp2
                 [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] j
    [COLOR="Navy"]Next[/COLOR] i
Dn.Offset(, -1).Resize(UBound(ray), 3) = ray
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[bjkiwi007]

Hi Mick and sulakvea,
Sorry I have taken a while to reply. Both your solutions have worked perfectly. Thanks so much for your help. It is very much appreciated.
BJ