Align numbers between two columns

[Panoos64]

Hi to all of you! Due to the mix of invoices' numbers and orders' numbers, I would like to alter my data between these two columns “AN” and “AO” so that to align the numbers on its right columns using a vba code. Therefore, that invoice numbers are bigger numbers than orders numbers and I think is the key to move each other. I present below an extract of original data and expected result. Note that the rows arise to 100000+ and between them some cells are blank or contains text. However the align should be just for numbers. Thank you all in advance

Original data

Expected result

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Sub AlignNumbers()
  Dim a As Variant
  Dim i As Long, tmp As Long
  
  With Range("AN5:AO" & Columns("AN:AO").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
    a = .Value
    For i = 1 To UBound(a)
      If Len(a(i, 1)) > 0 And Len(a(i, 2)) > 0 And IsNumeric(a(i, 1)) And IsNumeric(a(i, 2)) Then
        If a(i, 2) > a(i, 1) Then
          tmp = a(i, 2)
          a(i, 2) = a(i, 1)
          a(i, 1) = tmp
        End If
      End If
    Next i
    .Value = a
  End With
End Sub

 

[jorismoerings]

Other Option:

Sub Flip_AN_AO()

Dim i, j, Rows1, Rows2 As Integer
Dim Invoice, Order As String


Rows1 = Worksheets("Sheet1").Cells(Rows.Count, "AO").End(xlUp).Row
Rows2 = Worksheets("Sheet1").Cells(Rows.Count, "AN").End(xlUp).Row


'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

If Rows2 > Rows1 Then Rows1 = Rows2

'Loop Through Rows
    For i = 4 To Rows1                                                      'your screenshot didn't showed Header or row details, so assumed your headers were placed on row 4.
                                                                            'If that's not the case you need to amend the 4 to the correct header Row
        Order = Worksheets("Sheet1").Cells(i, "AO").Value                   'Order Number
        Invoice = Worksheets("Sheet1").Cells(i, "AN").Value                 'Invoice Number
       
        If IsNumeric(Order) And IsNumeric(Invoice) And Order > 999999 And Invoice < 1000000 Then                        'If condition is true Order and Invoice values are mixed up
                Worksheets("Sheet1").Cells(i, "AO") = Invoice
                Worksheets("Sheet1").Cells(i, "AN") = Order
        End If
    Next i
   
'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

Other Option:

Not sure if you read or tested for speed but the OP has up to 100,000 rows.

 

[jorismoerings]

Not sure if you read or tested for speed but the OP has up to 100,000 rows.

Yep tested it with 40K Lines and your solution is the better option because when i expanded my range to 300k+ lines it took quit some time.

 

[Panoos64]

Thank you Peter, it works perfect and based to my data! Thank you once again for your support. Hv a great day!

 

[Panoos64]

Hi joris, thank for your support. I tested several times but the code stops on the line "If IsNumeric(Order) And IsNumeric(Invoice) And Order > 999999 And Invoice < 1000000 Then " and the message is below:
However i appreciated what you done for my project and if is not so complicate i would be thankful if you could check it again. Hv a lovely day!

 

[jorismoerings]

Hi joris, thank for your support. I tested several times but the code stops on the line "If IsNumeric(Order) And IsNumeric(Invoice) And Order > 999999 And Invoice < 1000000 Then " and the message is below:
However i appreciated what you done for my project and if is not so complicate i would be thankful if you could check it again. Hv a lovely day!

View attachment 44771

I noticed i copied the old version of the VBA. However @Peter_SSs version was the better solution anyway due to timeconstraint on lager number of lines.
Thx for the feedback anyway!

 

[Peter_SSs]

I tested several times but the code stops on the line "If IsNumeric(Order) And IsNumeric(Invoice) And Order > 999999 And Invoice < 1000000 Then

The problem with that line is if Order and/or Invoice is not numeric it still tries to do the last two comparisons Order > 999999 And Invoice < 1000000 which error if either Order or Invoice is not numeric.
That is why in my code I broke those sort of comparisons into two separate lines of code.

If Len(a(i, 1)) > 0 And Len(a(i, 2)) > 0 And IsNumeric(a(i, 1)) And IsNumeric(a(i, 2)) Then
  If a(i, 2) > a(i, 1) Then

Thank you Peter, it works perfect and based to my data! Thank you once again for your support. Hv a great day!

You are very welcome once again.