lookup and validation

I

Invader

New Member
Joined
Jan 22, 2013
Messages
14
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Invader said:
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
Click to expand...

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:

Code: 
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
 
Upvote 0
[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]
 
Upvote 0
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.
 
Last edited:
Upvote 0
oldbrewer said:
does mine also ?
Click to expand...

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?
 
Upvote 0
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.........
 
Upvote 0
Molden said:
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:

Code: 
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
Click to expand...



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
 
Upvote 0
Invader said:
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
Click to expand...

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



Code: 
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


Code: 
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top