excel - Sorting / splitting ranges of numbers -

tldr:

1. compare ranges in order make macro realize range 2xx includes range 23x example
2. under above condition , compare ranges in order make macro realize 2xx not include 23xx example , smaller 23xx
3. in list of ranges find largest/highest range of given starting numeral (and mark if following steps dont work).

e.g.

1xxx  find 1  12xx 123x 1xx   find 1  12x 2xx   find 1  23x 234 

4. check in column b of found large ranges "n" or "y"
5. if in column b there "n" delete found large range list , subordinate ranges.
6. if in column b there "y" check in subordniate ranges in column b "n".
7. if there "n" in column b, take create range borders e.g.

1xx y 12x n 123 y 

to created

10x  11x 13x  14x 15x 16x 17x 18x 19x 

long version below

i have list of around 3000 entries represent ranges of numbers. range in list example

123xxxxxx , meaning whole range 1230xxxxx 1239xxxxx 

now have 2 types of ranges in list.

1. ranges indicate range fragements kept in list
2. ranges indicate range fragment removed list

for example

range

123xxxxxx included 1233xxxxx included 

however

range

1231xxxxx excluded 1232xxxxx excluded 1236xxxxx excluded 

what need extract in case

1230xxxxx 1233xxxxx 1235xxxxx , 1237xxxxx 1239xxxxx 

unfortunately format of given ranges not same.

a) example

1170987xxx excluded  1170999xxx excluded 1170988xxx excluded 1170666xxx excluded 11709111xx excluded 

with no leading

1170xxxxxx included 

which means

the whole 1170xxxxxx not included

and ranges

1170987xxx excluded  1170999xxx excluded 1170988xxx excluded 1170666xxx excluded 11709111xx excluded 

can deleted.

b)

2220987xxx included 2220999xxx included 2220988xxx included 2220666xxx included 22209111xx included 

and

222xxxxxxx excluded 

making

2220987xxx included 2220999xxx included 2220988xxx included 2220666xxx included 22209111xx included 

irrelevant larger range of

222xxxxxxx excluded 

trumps other ranges.

i guess in first step need way excel realize

222xxxxxxx 

is including

2220987xxx 

so counting length of range , if length (including x's) of 2 numbers same, compare numerals starting left.

1. if numerals same, range less numerals larger.
2. if larger range has on different column value "excluded" smaller ranges can deleted, including large range.
3. if larger range has on different column value "included" has checked fragemented ranges excluded larger range , "remaining" ranges larger range have written in excel respective borders.

last condition of ranges

the length between 12 , 19 digits including numerals , x's.

anybody idea how solve this?

i managed extract excluded ranges/borders unfortunately due these "overarching" larger ranges might or not include whole range of other ranges around them, these , themselves, dont me much.

the code excluded ranges

sub ueberpruefen() dim long dim b long dim zaehlen long dim long dim j long dim y long dim z long dim k integer dim l integer dim tabellenname string dim h integer dim starttabelle string  tabellenname = "ziel" starttabelle = "testtabelle"  worksheets(starttabelle).cells(1, 1).autofilter field:=8 h = activeworkbook.worksheets(starttabelle).cells(rows.count, 1).end(xlup).row worksheets(starttabelle).cells(1, 1).autofilter field:=8, criteria1:="n", visibledropdown:=true worksheets(starttabelle).activate activesheet.autofilter.sort.sortfields.clear activesheet.autofilter.sort.sortfields.add key:=range("a:a"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal activesheet.autofilter.sort.apply range("a2:a" & h).select selection.copy destination:=worksheets(tabellenname).range("b1:b1") worksheets(starttabelle).activate range("b2:b" & h).select selection.copy destination:=worksheets(tabellenname).range("c1:c1")  worksheets(tabellenname).activate  l = activeworkbook.worksheets(tabellenname).cells(rows.count, 2).end(xlup).row  k = 1 l  activeworkbook.worksheets(tabellenname).cells(k, 2).value = replace(activeworkbook.worksheets(tabellenname).cells(k, 2).value, " ", "") activeworkbook.worksheets(tabellenname).cells(k, 2).value = replace(activeworkbook.worksheets(tabellenname).cells(k, 2).value, "x", "")  next k  worksheets(tabellenname).activate  zaehlen = 10000      = 2 zaehlen         j = 1 zaehlen             = activeworkbook.worksheets(tabellenname).cells(i, 2).value             b = activeworkbook.worksheets(tabellenname).cells(i + j, 2).value                 if <= 1                 else                     if b <= 1                     else                         if b = + j                         else                             if j = 1                                 activeworkbook.worksheets(tabellenname).rows(i + j).select                                 selection.insert shift:=xldown, copyorigin:=xlformatfromleftorabove                                 exit                             elseif j = 2                                 activeworkbook.worksheets(tabellenname).rows(i + j).select                                 selection.insert shift:=xldown, copyorigin:=xlformatfromleftorabove                                 exit                             else                                 y = + 1                                 z = + j - 2                                 activeworkbook.worksheets(tabellenname).rows(y & ":" & z).select                                 selection.delete shift:=xlup                                 exit                             end if                         end if                     end if                 end if         next j     next end sub 

thinking doing manually honest .. :/

thanks lot!