lookup and validation

[Invader]

Hi everyone,

i have a problem, that makes me spend hours sorting but i am sure there is a way that can be done in seconds,

it starts like this

i receive a monthly report and the below two columns are in it, i need to validate all ticket numbers if they are available in the database or lets validate it with different list in a separate workbook, the data is like the below

a brief explanation
(1- each ticket number = 13 digits example "0722429036406"
2- some tickets are like this "0722429034078-79" which means these are two tickets "0722429034078" and 0722429034079" i need to separate them in new cells
3- The "Additional E-Tickets" got more than all additional tickets in it, which i want to separate as well.)


[TABLE="width: 666"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Primary E-Ticket[/TD]
[TD]Additional E-Tickets[/TD]
[/TR]
[TR]
[TD]0722429036406[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429082802[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429025933[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429042406[/TD]
[TD]0722429042407[/TD]
[/TR]
[TR]
[TD]0722429028633[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429079533[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429072225[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429028736[/TD]
[TD]0722429028737,0722429028738[/TD]
[/TR]
[TR]
[TD]0722429029505[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429043818[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429024964[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429028217[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429024379[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429025239[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429024796[/TD]
[TD]0722429024797,0722429024798,0722429024799,0722429024800[/TD]
[/TR]
[TR]
[TD]0722429024955[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429034078-79[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429034801-02[/TD]
[TD]0722429034803-04,0722429034805-06,0722429034807-08,0722429034809-10[/TD]
[/TR]
</tbody>[/TABLE]

i want to make them like the below so i can use vlookup or conditional formatting to highlight the unique ones

[TABLE="width: 112"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Cleaned[/TD]
[/TR]
[TR]
[TD]0722429036406[/TD]
[/TR]
[TR]
[TD]0722429082802[/TD]
[/TR]
[TR]
[TD]0722429025933[/TD]
[/TR]
[TR]
[TD]0722429042406[/TD]
[/TR]
[TR]
[TD]0722429042407[/TD]
[/TR]
[TR]
[TD]0722429028633[/TD]
[/TR]
[TR]
[TD]0722429079533[/TD]
[/TR]
[TR]
[TD]0722429072225[/TD]
[/TR]
[TR]
[TD]0722429028736[/TD]
[/TR]
[TR]
[TD]0722429028737[/TD]
[/TR]
[TR]
[TD]0722429028738[/TD]
[/TR]
[TR]
[TD]0722429043818[/TD]
[/TR]
[TR]
[TD]0722429024964[/TD]
[/TR]
[TR]
[TD]0722429028217[/TD]
[/TR]
[TR]
[TD]0722429024379[/TD]
[/TR]
[TR]
[TD]0722429025239[/TD]
[/TR]
[TR]
[TD]0722429024796[/TD]
[/TR]
[TR]
[TD]0722429024797[/TD]
[/TR]
[TR]
[TD]0722429024798[/TD]
[/TR]
[TR]
[TD]0722429024799[/TD]
[/TR]
[TR]
[TD]0722429024800[/TD]
[/TR]
[TR]
[TD]0722429024955[/TD]
[/TR]
[TR]
[TD]0722429034078[/TD]
[/TR]
[TR]
[TD]0722429034079[/TD]
[/TR]
[TR]
[TD]0722429034801[/TD]
[/TR]
[TR]
[TD]0722429034802[/TD]
[/TR]
[TR]
[TD]0722429034803[/TD]
[/TR]
[TR]
[TD]0722429034804[/TD]
[/TR]
[TR]
[TD]0722429034805[/TD]
[/TR]
[TR]
[TD]0722429034806[/TD]
[/TR]
[TR]
[TD]0722429034807[/TD]
[/TR]
[TR]
[TD]0722429034808[/TD]
[/TR]
[TR]
[TD]0722429034809[/TD]
[/TR]
[TR]
[TD]0722429034810[/TD]
[/TR]
</tbody>[/TABLE]


Regards

 

[Molden]

Hi everyone,

i have a problem, that makes me spend hours sorting but i am sure there is a way that can be done in seconds,

it starts like this

i receive a monthly report and the below two columns are in it, i need to validate all ticket numbers if they are available in the database or lets validate it with different list in a separate workbook, the data is like the below

a brief explanation
(1- each ticket number = 13 digits example "0722429036406"
2- some tickets are like this "0722429034078-79" which means these are two tickets "0722429034078" and 0722429034079" i need to separate them in new cells
3- The "Additional E-Tickets" got more than all additional tickets in it, which i want to separate as well.)


[TABLE="width: 666"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Primary E-Ticket[/TD]
[TD]Additional E-Tickets[/TD]
[/TR]
[TR]
[TD]0722429036406[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429082802[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429025933[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429042406[/TD]
[TD]0722429042407[/TD]
[/TR]
[TR]
[TD]0722429028633[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429079533[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429072225[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429028736[/TD]
[TD]0722429028737,0722429028738[/TD]
[/TR]
[TR]
[TD]0722429029505[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429043818[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429024964[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429028217[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429024379[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429025239[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429024796[/TD]
[TD]0722429024797,0722429024798,0722429024799,0722429024800[/TD]
[/TR]
[TR]
[TD]0722429024955[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429034078-79[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0722429034801-02[/TD]
[TD]0722429034803-04,0722429034805-06,0722429034807-08,0722429034809-10[/TD]
[/TR]
</tbody>[/TABLE]

i want to make them like the below so i can use vlookup or conditional formatting to highlight the unique ones

[TABLE="width: 112"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Cleaned[/TD]
[/TR]
[TR]
[TD]0722429036406[/TD]
[/TR]
[TR]
[TD]0722429082802[/TD]
[/TR]
[TR]
[TD]0722429025933[/TD]
[/TR]
[TR]
[TD]0722429042406[/TD]
[/TR]
[TR]
[TD]0722429042407[/TD]
[/TR]
[TR]
[TD]0722429028633[/TD]
[/TR]
[TR]
[TD]0722429079533[/TD]
[/TR]
[TR]
[TD]0722429072225[/TD]
[/TR]
[TR]
[TD]0722429028736[/TD]
[/TR]
[TR]
[TD]0722429028737[/TD]
[/TR]
[TR]
[TD]0722429028738[/TD]
[/TR]
[TR]
[TD]0722429043818[/TD]
[/TR]
[TR]
[TD]0722429024964[/TD]
[/TR]
[TR]
[TD]0722429028217[/TD]
[/TR]
[TR]
[TD]0722429024379[/TD]
[/TR]
[TR]
[TD]0722429025239[/TD]
[/TR]
[TR]
[TD]0722429024796[/TD]
[/TR]
[TR]
[TD]0722429024797[/TD]
[/TR]
[TR]
[TD]0722429024798[/TD]
[/TR]
[TR]
[TD]0722429024799[/TD]
[/TR]
[TR]
[TD]0722429024800[/TD]
[/TR]
[TR]
[TD]0722429024955[/TD]
[/TR]
[TR]
[TD]0722429034078[/TD]
[/TR]
[TR]
[TD]0722429034079[/TD]
[/TR]
[TR]
[TD]0722429034801[/TD]
[/TR]
[TR]
[TD]0722429034802[/TD]
[/TR]
[TR]
[TD]0722429034803[/TD]
[/TR]
[TR]
[TD]0722429034804[/TD]
[/TR]
[TR]
[TD]0722429034805[/TD]
[/TR]
[TR]
[TD]0722429034806[/TD]
[/TR]
[TR]
[TD]0722429034807[/TD]
[/TR]
[TR]
[TD]0722429034808[/TD]
[/TR]
[TR]
[TD]0722429034809[/TD]
[/TR]
[TR]
[TD]0722429034810[/TD]
[/TR]
</tbody>[/TABLE]


Regards

If you highlight the cells you want the code to look at and then run the below it will put the results in column A.

If you are using column A then either change the code or add a column at the beginning.

Hope this helps:

Sub SortNumbers()

Dim i As Long, cell As Range

    With ActiveSheet

        i = ActiveCell.Row
    
        For Each cell In Selection
    
            If Not cell = "" Then
                varTicket = Split(cell, ",")
                
                For Each x In varTicket
                    If InStr(x, "-") > 0 Then
                        Cells(i, 1) = Left(x, 13)
                        i = i + 1
                        Cells(i, 1) = Left(x, 11) & Right(x, 2)
                        i = i + 1
                    Else
                        .Cells(i, 1) = x '(x)
                        i = i + 1
                    End If
                Next
            End If
            
        Next
    
    End With
    

End Sub

 

[oldbrewer]

[TABLE="width: 952"]
<colgroup><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Primary E-Ticket[/TD]
[TD]Additional E-Tickets[/TD]
[TD]len add E-ticket[/TD]
[TD]how many commas[/TD]
[TD]dash present ?[/TD]
[TD]no add E ticks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD="align: right"]235[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]345[/TD]
[TD]346 , 347[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]457 , 458 , 459[/TD]
[TD]15[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]551[/TD]
[TD]552-558[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]987[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]876[/TD]
[TD]877-879[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD]766 , 767 , 768[/TD]
[TD]15[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]444[/TD]
[TD="align: right"]445[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD]223 , 224[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]I used 4 helper columns to determine type of number - or not[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]in additional e tickets column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then this macro produced the required list[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]235[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]346[/TD]
[TD][/TD]
[TD][/TD]
[TD] Dim mylist(100)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]347[/TD]
[TD][/TD]
[TD][/TD]
[TD] step = -5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD][/TD]
[TD][/TD]
[TD] For j = 2 To 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]457[/TD]
[TD][/TD]
[TD][/TD]
[TD] Sum = Sum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]458[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] mylist(Sum) = Cells(j, 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]459[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] If Cells(j, 6) = "TRUE" Then GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]551[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] If Cells(j, 5) = "YES" Then GoTo 20 Else GoTo 30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]552[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]20 smallnum = Left(Cells(j, 2), 3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]553[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] largenum = Right(Cells(j, 2), 3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]554[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] mydiff = largenum - smallnum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]555[/TD]
[TD][/TD]
[TD][/TD]
[TD]25 tot = tot + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]556[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] If tot > mydiff Then myincr = 0: tot = 0: GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]557[/TD]
[TD][/TD]
[TD][/TD]
[TD] Sum = Sum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]558[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] mylist(Sum) = smallnum + myincr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]987[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] myincr = myincr + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]876[/TD]
[TD][/TD]
[TD][/TD]
[TD] GoTo 25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]877[/TD]
[TD][/TD]
[TD][/TD]
[TD] GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]878[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]30 If Cells(j, 4) = 0 Then Sum = Sum + 1: mylist(Sum) = Cells(j, 2): GoTo 100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]879[/TD]
[TD][/TD]
[TD][/TD]
[TD]35 step = step + 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Count = Count + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]766[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] If Count > Cells(j, 4) + 1 Then Count = 0: GoTo 40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD][/TD]
[TD][/TD]
[TD] Sum = Sum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]768[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] mylist(Sum) = Mid(Cells(j, 2), step, 3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]444[/TD]
[TD][/TD]
[TD][/TD]
[TD] GoTo 35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]445[/TD]
[TD][/TD]
[TD][/TD]
[TD]40 step = -5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]222[/TD]
[TD][/TD]
[TD][/TD]
[TD] GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]223[/TD]
[TD][/TD]
[TD][/TD]
[TD]100 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]224[/TD]
[TD][/TD]
[TD][/TD]
[TD] For z = 20 To 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(z, 1) = mylist(z - 19)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Invader]

thanks Molden, this is really fast, but only one issue, the format, when it sorts them it removes the zero from the beginning, can you modify it with the correct format, as a text maybe.

 

[Invader]

thanks alot

 

[oldbrewer]

does mine also ?

 

[Invader]

does mine also ?

to be honest, it is my first time to use a code, I want to try yours but I got confused when you said, that you used four columns,
how can I use it, just copy the code, then select the data (two columns) and run the macro?

 

[oldbrewer]

the 4 columns are calculations looking at the original data - so you type in the 4 formulas in first row and drag down. The macro looks at these values to produce the list you require. Basically it is searching for a "-" and counting how many commas.........

 

[Invader]

If you highlight the cells you want the code to look at and then run the below it will put the results in column A.

If you are using column A then either change the code or add a column at the beginning.

Hope this helps:

Sub SortNumbers()

Dim i As Long, cell As Range

    With ActiveSheet

        i = ActiveCell.Row
    
        For Each cell In Selection
    
            If Not cell = "" Then
                varTicket = Split(cell, ",")
                
                For Each x In varTicket
                    If InStr(x, "-") > 0 Then
                        Cells(i, 1) = Left(x, 13)
                        i = i + 1
                        Cells(i, 1) = Left(x, 11) & Right(x, 2)
                        i = i + 1
                    Else
                        .Cells(i, 1) = x '(x)
                        i = i + 1
                    End If
                Next
            End If
            
        Next
    
    End With
    

End Sub

this helped a lot, can you modify it please to ignore "-" so if there is a ticket with 0722429034078-79 than it should copy it as it is
basically to ignore point number two


so at the end, the condition will be
to sort all tickets and they are not necessarily 13 digits, but if more than one ticket in a cell it will be separated by a comma.

Regards

 

[Molden]

this helped a lot, can you modify it please to ignore "-" so if there is a ticket with 0722429034078-79 than it should copy it as it is
basically to ignore point number two


so at the end, the condition will be
to sort all tickets and they are not necessarily 13 digits, but if more than one ticket in a cell it will be separated by a comma.

Regards

Sorry for the delay.

I've done 2 examples below. Both have corrected the issue where the first zero was being removed.

This example doesn't do anything to the tickets with "-" in.
The cells with multiple tickets in get split out so there is one per row.

e.g.

0722429024955
0722429034078-79
0722429034801-02
0722429034803-04
0722429034805-06
0722429034807-08
0722429034809-10

Sub Sortnumbers

Dim i As Long, cell As Range

    With ActiveSheet

        i = ActiveCell.Row
        Range("A:A").NumberFormat = "@"
    
        For Each cell In Selection
    
            If Not cell = "" Then
                varTicket = Split(cell, ",")
                
                For Each x In varTicket
                    .Cells(i, 1) = x '(x)
                    i = i + 1
                Next
            End If
            
        Next
    
    End With

End Sub

This example keeps the cells with multiple tickets in the same format but copies them underneath:

e.g.
0722429024797,0722429024798,0722429024799,0722429024800
0722429024955
0722429034078-79
0722429034801-02
0722429034803-04,0722429034805-06,0722429034807-08,0722429034809-10

sub sortnumbers2

Dim i As Long, cell As Range

    With ActiveSheet

        i = ActiveCell.Row
        Range("A:A").NumberFormat = "@"
    
        For Each cell In Selection
    
            If Not cell = "" Then
'                varTicket = Split(cell, ",")
                
'                For Each x In varTicket
                    .Cells(i, 1) = cell '(x)
                    i = i + 1
'                Next
            End If
            
        Next
    
    End With

End Sub

Let me know if either aren't what you needed