Create a low/high range of postal codes

[kijake]

Hi,

I have a list of postal codes arranged on 1 column of around 13000 rows.
Some of these postal codes could be collapsed and setup in ranges since they are consecutive like in the example below:

121012 and 121013 could be collapsed in 1 row as Low:121012 High:121013

Country	IATA Code	Low	High		Low	High
India	IN	121012	121012		121012	121013
India	IN	121013	121013		121101	121101
India	IN	121101	121101		121103	121103
India	IN	121103	121103		121105	121107
India	IN	121105	121105		122052	122052
India	IN	121106	121106		122104	122104
India	IN	121107	121107		122108	122108
India	IN	122052	122052		122413	122414
India	IN	122104	122104		122502	122506
India	IN	122108	122108
India	IN	122413	122413
India	IN	122414	122414
India	IN	122502	122502
India	IN	122503	122503
India	IN	122504	122504
India	IN	122505	122505
India	IN	122506	122506

<tbody>
</tbody>

Anyone with a solution to this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub kijake()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   
   Ary = Range("C2", Range("C" & Rows.count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 2)
   Nary(1, 1) = Ary(1, 1)
   For r = 1 To UBound(Ary) - 1
      If Ary(r, 1) <> Ary(r + 1, 1) - 1 Then
         nr = nr + 1
         Nary(nr, 2) = Ary(r, 1)
         Nary(nr + 1, 1) = Ary(r + 1, 1)
      End If
   Next r
   Nary(nr + 1, 2) = Ary(r, 1)
   Range("F2").Resize(nr + 1, 2).Value = Nary
End Sub

 

[kijake]

Hi & welcome to MrExcel.
How about

Sub kijake()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   
   Ary = Range("C2", Range("C" & Rows.count).End(xlUp)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To 2)
   Nary(1, 1) = Ary(1, 1)
   For r = 1 To UBound(Ary) - 1
      If Ary(r, 1) <> Ary(r + 1, 1) - 1 Then
         nr = nr + 1
         Nary(nr, 2) = Ary(r, 1)
         Nary(nr + 1, 1) = Ary(r + 1, 1)
      End If
   Next r
   Nary(nr + 1, 2) = Ary(r, 1)
   Range("F2").Resize(nr + 1, 2).Value = Nary
End Sub

Thanks a lot, it worked!
In the meantime, I managed to find a much more manual solution.
- by calculating the difference between each postal code
- each occurrence where the difference is = 1 is define as the Low start of a range
- I then created a pivot table by setting my Low postal codes as rows, and my High postal codes as the data with field setting MAX

The result was the exact same as yours by much more time consuming. I'm not a VBA guy at all, so thanks again!

 

[Fluff]

Glad to help & thanks for the feedback