Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Extract and arrange the numbers small to larger values

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Extract and arrange the numbers small to larger values

    Hello,

    I need to extract the numbers of column E:N and need to arrange them in order small to larger values in the columns P:Y


    Example

    EFGHIJKLMNOPQRSTUVWXY
    1
    2
    3n1n2n3n4n5n6n7n8n9n10n1n2n3n4n5n6n7n8n9n10
    40000000110131113
    500090010012139101213
    600000910012091011
    70000001000131013
    800000901112091112
    900000901101391113
    100340091000034910
    111234500090123459

    Sheet1





    Thank you all


    Excel 2000

    Regards,

    Moti
    Last edited by motilulla; Oct 21st, 2019 at 12:33 PM.

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,401
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    How about this:

    Code:
    Sub RemZero()
    
        Dim rng1 As Range, rng2 As Range, r As Long
        
        Application.ScreenUpdating = False
        Set rng1 = Range("A4:J" & Cells(Rows.Count, 1).End(xlUp).Row)
        rng1.Copy Range("L4")
        Set rng2 = Range("L4:U" & Cells(Rows.Count, 1).End(xlUp).Row)
        For r = rng2.Cells.Count To 1 Step -1
            If rng2.Cells(r) = 0 Then rng2.Cells(r).Delete Shift:=xlToLeft
        Next
        Application.ScreenUpdating = True
            
    End Sub
    Last edited by igold; Oct 21st, 2019 at 02:36 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,920
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Just another way to do it.

    Code:
    Sub arrange_numbers1()
      Dim c As Range
      For Each c In Range("E4:N" & Range("E" & Rows.Count).End(xlUp).Row)
        If c <> 0 Then Cells(c.Row, Cells(c.Row, Columns.Count).End(xlToLeft).Column + 1) = c
      Next
      Range("O4:O" & Range("E" & Rows.Count).End(xlUp).Row).Insert Shift:=xlToRight
    End Sub
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by igold View Post
    How about this:

    Code:
    Sub RemZero()
    
        Dim rng1 As Range, rng2 As Range, r As Long
        
        Application.ScreenUpdating = False
        Set rng1 = Range("A4:J" & Cells(Rows.Count, 1).End(xlUp).Row)
        rng1.Copy Range("L4")
        Set rng2 = Range("L4:U" & Cells(Rows.Count, 1).End(xlUp).Row)
        For r = rng2.Cells.Count To 1 Step -1
            If rng2.Cells(r) = 0 Then rng2.Cells(r).Delete Shift:=xlToLeft
        Next
        Application.ScreenUpdating = True
            
    End Sub
    igold, thank you for the code you provide it works I need to shift the data in the blank sheet and run the code, if I run in the same sheet it disturb all the right columns data I got all the time columns E:N data update every day so is it possible the code work in the same sheet and update columns P:Y

    Kind Regards,

    Moti


  5. #5
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by DanteAmor View Post
    Just another way to do it.

    Code:
    Sub arrange_numbers1()
      Dim c As Range
      For Each c In Range("E4:N" & Range("E" & Rows.Count).End(xlUp).Row)
        If c <> 0 Then Cells(c.Row, Cells(c.Row, Columns.Count).End(xlToLeft).Column + 1) = c
      Next
      Range("O4:O" & Range("E" & Rows.Count).End(xlUp).Row).Insert Shift:=xlToRight
    End Sub
    DanteAmor, thank you for the code it is printing the results end of the used columns I need the result must be in P:Y columns without disturbing other columns data

    Kind Regards,

    Moti

  6. #6
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,401
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Change the highlighted lines to where you want the results returned...

    Code:
    Sub RemZero()
    
    
        Dim rng1 As Range, rng2 As Range, r As Long
        
        Application.ScreenUpdating = False
        Set rng1 = Range("A4:J" & Cells(Rows.Count, 1).End(xlUp).Row)
        rng1.Copy Range("L4")
        Set rng2 = Range("L4:U" & Cells(Rows.Count, 1).End(xlUp).Row)
        For r = rng2.Cells.Count To 1 Step -1
            If rng2.Cells(r) = 0 Then rng2.Cells(r).Delete Shift:=xlToLeft
        Next
        Application.ScreenUpdating = True
            
    End Sub
    Last edited by igold; Oct 21st, 2019 at 03:57 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,316
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Am I remembering correctly from past postings of yours... except for the zero values, aren't your numbers in each row of Columns E:N always in sorted numerical order from left to right? If so, this should work for you...
    Code:
    Sub DumpZeros()
      Range("E3").CurrentRegion.Copy Range("P3")
      With Range("P3").CurrentRegion
        .Replace 0, "", xlWhole, , , , False, False
        .SpecialCells(xlBlanks).Delete xlShiftToLeft
      End With
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,920
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by motilulla View Post
    DanteAmor, thank you for the code it is printing the results end of the used columns I need the result must be in P:Y columns without disturbing other columns data

    Kind Regards,

    Moti

    Try this, This should write from P to Y without altering other columns.

    Code:
    Sub arrange_numbers1()
      Dim c As Range, j As Long
      For Each c In Range("E4:N" & Range("E" & Rows.Count).End(xlUp).Row)
        If c.Column = 5 Then j = Columns("P").Column
        If c <> 0 Then
          Cells(c.Row, j) = c
          j = j + 1
        End If
      Next
    End Sub
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Rick, code also is giving problem having data in the right columns, to be specific here is how my data looks I want result in columns P:Y without disturbing any data of the columns AA:AJ...

    EFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
    1
    2
    3n1n2n3n4n5n6n7n8n9n10n1n2n3n4n5n6n7n8n9n10n1n2n3n4n5n6n7n8n9n10
    400000001101311131520232740322253049
    5000900100121391012131322334450112274247
    60000091001209101212132936382237434445
    70000001000131013214354142821253850
    8000009011120911121329313340233424748
    9000009011013911139102737422437414346
    1003400910000349102022232437211184046
    11123450009012345941735424569354144

    Sheet1





    Kind Regards,
    Moti
    Last edited by motilulla; Oct 21st, 2019 at 04:20 PM.

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    328
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    HI
    What about
    Code:
    Sub test()
        Dim b As Variant
        Dim lr, i
           For i = 5 To Cells(Rows.Count, 5).End(xlUp).Row
            ReDim b(1 To 10)
            t = 1
            For j = 5 To 15
                If Cells(i, j) <> 0 Then
                    b(t) = Cells(i, j): t = t + 1
                End If
            Next
            Cells(i, 16).Resize(, UBound(b)) = b
        Next
    End Sub
    Last edited by mohadin; Oct 21st, 2019 at 04:21 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •