+ Reply to Thread
Results 1 to 6 of 6

How to Sort or move list of preferred names

Hybrid View

  1. #1
    Forum Contributor prabhuduraraj09's Avatar
    Join Date
    05-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    330

    How to Sort or move list of preferred names

    Hi all,

    My question is simple one i guess but yet tricky

    I have two headers, "Ref no" and "Names"

    I always need list of names "A" and "B" should go to middle portion of entire data. Names can duplicate.

    So all of A's or B's should be moved to the middle of entire data in the sheet.

    For example, The total records is 12 Then whatever the list i say like "A" and "B" data should moved to the middle of those 12 rows.
    So the desired result is "A" and "B" data are moved to the middle of the entire set of data. Somewhere after 6th row.




    Attached sample file.
    Attached Files Attached Files
    Thank you

    If I have helped you in someway, use the * icon below to give reputation feedback, it is appreciated.

  2. #2
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,018

    Re: How to Sort or move list of preferred names

    Using col C as helper column, try this:

    Sub b1269739b()
    'https://www.excelforum.com/excel-programming-vba-macros/1269739-how-to-sort-or-move-list-of-preferred-names.html
    
    Dim i As Long, m As Long, n As Long, z As Long
    Dim tx1 As String, tx2 As String
    
    tx1 = Range("J5"):  tx2 = Range("J6")
    
    n = Range("A" & Rows.count).End(xlUp).Row
    m = (n - 1) \ 2
    Range("C1") = 1: Range("C1:C" & n).DataSeries
    For i = 2 To n
    If Range("B" & i) = tx1 Or Range("B" & i) = tx2 Then Range("C" & i) = n + 1: z = z + 1
    Next
    
    Range("A" & 1 & ":C" & n).Sort Key1:=[c1], Order1:=xlAscending, Header:=xlYes
    Range("A" & n - z + 1 & ":C" & n).Cut
    Range("A" & m + 1).Insert xlDown
    Columns("C").ClearContents
    
    End Sub

  3. #3
    Forum Contributor prabhuduraraj09's Avatar
    Join Date
    05-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    330

    Re: How to Sort or move list of preferred names

    Hi akuini,

    I have a question, now as said criteria being "A" and "B" is fine. This code works as intended.

    What if i have criteria, listed in the range "J5:J20"?. How that can be updated...? instead of just two criteria.

  4. #4
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,018

    Re: How to Sort or move list of preferred names

    Maybe this:

    Sub b1269739c()
    'https://www.excelforum.com/excel-programming-vba-macros/1269739-how-to-sort-or-move-list-of-preferred-names.html
    
    Dim i As Long, m As Long, n As Long, z As Long
    Dim tx1 As String, tx2 As String
    Dim r As Range
    Dim d As Object
    Application.ScreenUpdating = False
    Set d = CreateObject("scripting.dictionary")
    
    For Each r In Range("J5:J20")
        d(r.Value) = ""
    Next
    If d.Exists("") Then d.Remove ""
    
    n = Range("A" & Rows.count).End(xlUp).Row
    m = (n - 1) \ 2
    Range("C1") = 1: Range("C1:C" & n).DataSeries
    
    For i = 2 To n
        If d.Exists(Range("B" & i).Value) Then Range("C" & i) = n + 1: z = z + 1
    Next
    
    Range("A" & 1 & ":C" & n).Sort Key1:=[c1], Order1:=xlAscending, Header:=xlYes
    Range("A" & n - z + 1 & ":C" & n).Cut
    Range("A" & m + 1).Insert xlDown
    Columns("C").ClearContents
    Application.ScreenUpdating = True
    
    End Sub

  5. #5
    Forum Contributor prabhuduraraj09's Avatar
    Join Date
    05-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    330

    Re: How to Sort or move list of preferred names

    Thanks Akuini, this helped.

  6. #6
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,018

    Re: How to Sort or move list of preferred names

    You're welcome, glad to help, & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. sort the list of names based on the group
    By protocoder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2018, 08:25 AM
  2. [SOLVED] Sort List of names
    By nicolaforni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2012, 04:55 AM
  3. Move and sort list of data to another sheet
    By Ramachandran in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2012, 05:15 AM
  4. Sort through list picking out names (not repeats)
    By stevedes7 in forum Excel General
    Replies: 1
    Last Post: 10-18-2009, 02:54 AM
  5. Sort list of names by surname
    By BVan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-29-2008, 02:31 PM
  6. List of names sort
    By mmednick in forum Excel General
    Replies: 3
    Last Post: 08-15-2006, 08:00 PM
  7. [SOLVED] How do I randomly sort a list of names?
    By JIreland in forum Excel General
    Replies: 3
    Last Post: 05-17-2006, 02:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1