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.)

Primary E-Ticket	Additional E-Tickets
0722429036406
0722429082802
0722429025933
0722429042406	0722429042407
0722429028633
0722429079533
0722429072225
0722429028736	0722429028737,0722429028738
0722429029505
0722429043818
0722429024964
0722429028217
0722429024379
0722429025239
0722429024796	0722429024797,0722429024798,0722429024799,0722429024800
0722429024955
0722429034078-79
0722429034801-02	0722429034803-04,0722429034805-06,0722429034807-08,0722429034809-10

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Cleaned
0722429036406
0722429082802
0722429025933
0722429042406
0722429042407
0722429028633
0722429079533
0722429072225
0722429028736
0722429028737
0722429028738
0722429043818
0722429024964
0722429028217
0722429024379
0722429025239
0722429024796
0722429024797
0722429024798
0722429024799
0722429024800
0722429024955
0722429034078
0722429034079
0722429034801
0722429034802
0722429034803
0722429034804
0722429034805
0722429034806
0722429034807
0722429034808
0722429034809
0722429034810

<colgroup><col></colgroup><tbody>
</tbody>

Regards

 

[Invader]

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

Let me know if either aren't what you needed

first one is what I need, thaaaaaaaaaaanks a lot

 

[Molden]

first one is what I need, thaaaaaaaaaaanks a lot

Glad I could help