Spreadsheet: 65589d1266315872-excel-2007-sorting-alphanumerics-and-extracting-numbers-from-them-details1
Sheet: Contents

Fine data |  |  | Problem data
A | B |  | D | F
abc-0001 | abc-0005 |  | abc-0001 | abc-0005
abc-0006 | abc-0006 |  | abc-0006 | abc-0006
abc-0007 | abc-0009 |  | abc-0007 | abc-0009
abc-0010 | abc-0021 |  | abc-0007 | abc-0021 | Repeated  numbers | abc-0007 ; abc-0008 ; abc-0009 ;
abc-0022 | abc-0001 |  | abc-0008 | abc-0015
ABC-0001 | ABC-0030 |  | abc-0016 | abc-0020
ABC-0006 | ABC-0006 |  | abc-0025 | abc-0030 | Missing numbers | abc-0021; abc-0022; abc-0023; abc-0024; | abc-0001 -- abc-0030
ABC-0007 | ABC-0009 |  | ABC-0001 | ABC-0005
ABC-0010 | ABC-0021 |  | ABC-0006 | ABC-0006
ABC-0022 | ABC-0030 |  | ABC-0007 | ABC-0009
CALC - 0001 | CALC - 0015 |  | ABC-0007 | ABC-0021
[NAME] | [NAME] |  | ABC-0008 | ABC-0015
CAT-[PHONE].6 | CAT-[PHONE].8 |  | ABC-0016 | ABC-0020
CAT-[PHONE].7 | CAT-[PHONE].8 |  | ABC-0025 | ABC-0030 |  |  | ABC-0001 -- ABC-0030
CAT-[PHONE] | CAT-[PHONE] |  | CALC - 0001 | CALC - 0015 |  |  | CALC - 0001 -- CALC - 0001
CAT-000002 | CAT-000008 |  | CAT-  [PHONE] | CAT-  [PHONE]
CAT-000009 | CAT-000015 |  | CAT-  [PHONE].6 | CAT-  [PHONE].8
CAT-000016 | CAT-000020 |  | CAT-[PHONE].7 | CAT-[PHONE].8
CAT-000021 | CAT-000030 |  | CAT-[PHONE] | CAT-[PHONE] |  |  | CAT-[PHONE] -- CAT-[PHONE]
CAT-000031 | CAT-000040 |  | CAT-  000002 | CAT-  000008 | Missing numbers | CAT-000001;
MAX-[PHONE] | MAX-[PHONE] |  | CAT-  000009 | CAT-  000015
MAX-[PHONE] | MAX-[PHONE] |  | CAT-  000014 | CAT-  000020 | Repeated  numbers | CAT-000014 ; CAT-000015;
MAX-[PHONE] | MAX-[PHONE] |  | CAT-  000021 | CAT-  000030
MAX-[PHONE] | [NAME] |  | CAT-  000035 | CAT-  000040 | Missing numbers | CAT-000031 ; CAT-000032 ; CAT-000033 ; CAT-000034
MAX-[PHONE] | MAX-[PHONE] |  | MAX-    [PHONE] | MAX-    [PHONE]
ZIP-00000001 | ZIP-00000038 |  | MAX-[PHONE] | MAX-[PHONE]
ZIP-00000039 | ZIP-00000045 |  | MAX-[PHONE] | MAX-[PHONE] | Repeated  numbers | MAX-[PHONE] ;
ZIP-00000046 | ZIP-00000083 |  | MAX-[PHONE] | [NAME]
ZIP-00000084 | ZIP-00000090 |  | MAX-[PHONE] | MAX-[PHONE] |  |  | MAX-[PHONE] -- MAX-[PHONE]
 |  |  | ZIP-00000001 | ZIP-00000038
 |  |  | ZIP-00000039 | ZIP-00000045
 |  |  | ZIP-00000050 | ZIP-00000083
 |  |  | ZIP-00000074 | ZIP-00000080 | Repeated  numbers | ZIP-00000074; ….;ZIP-00000080;
 |  |  | ZIP-00000085 | ZIP-00000090 | Missing numbers | ZIP-00000081 ; ZIP-00000082 ; ZIP-00000083; ZIP-00000084; | ZIP-00000001 -- ZIP-00000090
 | If you look at my two samples
 | First sample 'Fine Data'  has continuous ranges and the second one (Problem Data) has repeated.
 | I need to work on the second one i.e problem data   .
 | The alphabets (prefixes) I have given is just an example. It can contain  any number of letters, like AGHET- , BATCH- etc
 | But the alphabets  are case sensitive.
 | Columns D anf F together makes one range.
 | If start with the ranges abc- in problem data
 | First 3 ranges i.e from abc-0001 to abc-0009 its finely existed.
 | But if you look at  next range i.e  abc-0007 to abc-0021  there  are 3 numbers i.e abc-0007 And abc-0008 and abc-0009 are repeated in other range.i.e in the previous range.
 | the code should tell me that  (Highlighed with color) these are repeated numbers and in next column should display  the repeated numbers.
 | Similarly, if you look at  another 2 ranges i.e abc-0016 to abc-0020 AND abc-0025 to abc-0030 the first range ends with  abc-0020 and the next range starts with abc-0025. the code has to dislay these missing numbers i.e abc-0021; abc-0022; abc-0023; abc-0024;
 | I also need to display the beginning and Ending numbers for each different types of ranges.(Shown in the last column)
 | When the loop  reaches ABC-0001, it should recognise that the ranges are (Prefix)   different and should apply the same above procedure from the beginning.
 | For range CAT- , in sample it exists twice in different length. And there are spaces.
 | I need to remove the spaces from the range i.e CAT-  [PHONE] to CAT-  [PHONE] and CAT-   [PHONE].6 to CAT-   [PHONE].8
 | But not from CAT- smaller lengthrange i.e CAT- 000002 to CAT-  000040 . Is there any way to do this?
 | This should be possible for any other ranges.