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


Primary E-TicketAdditional E-Tickets
0722429036406
0722429082802
0722429025933
07224290424060722429042407
0722429028633
0722429079533
0722429072225
07224290287360722429028737,0722429028738
0722429029505
0722429043818
0722429024964
0722429028217
0722429024379
0722429025239
07224290247960722429024797,0722429024798,0722429024799,0722429024800
0722429024955
0722429034078-79
0722429034801-020722429034803-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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.)


Primary E-TicketAdditional E-Tickets
0722429036406
0722429082802
0722429025933
07224290424060722429042407
0722429028633
0722429079533
0722429072225
07224290287360722429028737,0722429028738
0722429029505
0722429043818
0722429024964
0722429028217
0722429024379
0722429025239
07224290247960722429024797,0722429024798,0722429024799,0722429024800
0722429024955
0722429034078-79
0722429034801-020722429034803-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
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
Primary E-TicketAdditional E-Ticketslen add E-tickethow many commasdash present ?no add E ticks
12300TRUE
23423530
345346 , 34791
456457 , 458 , 459152
551552-55870YES
98700TRUE
876877-87970YES
765766 , 767 , 768152
44444530
222223 , 22491
I used 4 helper columns to determine type of number - or not
in additional e tickets column
123
234then this macro produced the required list
235
345
346 Dim mylist(100)
347 step = -5
456 For j = 2 To 11
457 Sum = Sum + 1
458 mylist(Sum) = Cells(j, 1)
459 If Cells(j, 6) = "TRUE" Then GoTo 100
551 If Cells(j, 5) = "YES" Then GoTo 20 Else GoTo 30
55220 smallnum = Left(Cells(j, 2), 3)
553 largenum = Right(Cells(j, 2), 3)
554 mydiff = largenum - smallnum + 1
55525 tot = tot + 1
556 If tot > mydiff Then myincr = 0: tot = 0: GoTo 100
557 Sum = Sum + 1
558 mylist(Sum) = smallnum + myincr
987 myincr = myincr + 1
876 GoTo 25
877 GoTo 100
87830 If Cells(j, 4) = 0 Then Sum = Sum + 1: mylist(Sum) = Cells(j, 2): GoTo 100
87935 step = step + 6
765 Count = Count + 1
766 If Count > Cells(j, 4) + 1 Then Count = 0: GoTo 40
767 Sum = Sum + 1
768 mylist(Sum) = Mid(Cells(j, 2), step, 3)
444 GoTo 35
44540 step = -5
222 GoTo 100
223100 Next j
224 For z = 20 To 100
Cells(z, 1) = mylist(z - 19)
Next z
200 End Sub

<colgroup><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>
 
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,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

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