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

 

[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