Sorting existing data in new cells

I have b11-b50 with people names, the h11-h50 with related scores in them for those people. I want to populate b111-b150 with the names from b11-b50 but sorted in order from top to bottom by the scores in h11-h50. Could some provide the function or give some advice on the functions needed to accomplish? Thanks.
circa62Asked:
Who is Participating?
 
StephenJRCommented:
If you could add a helper column, e,g in J11: =RANK(H11,$H$11:$H$16)+COUNTIF($H$11:H11,H11)-1

Then, adapting broomee9's formula in B111 and down

=INDEX($B$11:$B$50,MATCH(ROW()-110,$J$11:$J$50,FALSE),1)
0
 
TracyVBA DeveloperCommented:
Put this in B111 and drag down:

=INDEX($B$11:$B$50,MATCH(LARGE($H$11:$H$50,ROW()-110),$H$11:$H$50,FALSE),1)

See attached example.
Book1.xls
0
 
scifo_dkCommented:
I would record a macro copying the data to the new area, and the sort it. Example that copy both rows side by side, sort, and delete the scores besides b111-b150:
Sub Makro1()
'
' Makro1 Makro
'

'
    Range("B11:B50").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=69
    Range("B111").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-75
    Range("H11").Select
    ActiveWindow.SmallScroll Down:=3
    Range("H11:H50").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=81
    Range("C111").Select
    ActiveSheet.Paste
    Range("B111").Select
    ActiveWindow.SmallScroll Down:=24
    Range("B111:C150").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Ark1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ark1").Sort.SortFields.Add Key:=Range("B111"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Ark1").Sort
        .SetRange Range("B111:C150")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C111:C150").Select
    Selection.ClearContents
    Range("B111").Select
End Sub

Open in new window


// Scifo_dk
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
circa62Author Commented:
I tried the top solution first and that one appears to work fine only if there are not duplicate scores. If there are duplicates then it fills that persons name in twice. Haven't tried the macro yet.
0
 
circa62Author Commented:
scifo_dk, I tried this one and it sorted the names into the new cells alphabetically, not by highest scores to lowest scores.
0
 
scifo_dkCommented:
The macro presupposes that the name-data is in B11:B50 AND the score-data is in H11:H50 AND you want the sorted end data in B111:B150.

I can make this relative if you like, if the number of data columns varies.

By the way, use this instead, the above one has some unnecessary lines of code.
Sub Makro1() 
    Range("B11:B50").Select 
    Selection.Copy 
    Range("B111").Select 
    ActiveSheet.Paste 
    Range("H11:H50").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Range("C111").Select 
    ActiveSheet.Paste 
    Range("B111:C150").Select 
    Application.CutCopyMode = False 
    ActiveWorkbook.Worksheets("Ark1").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Ark1").Sort.SortFields.Add Key:=Range("B111"), _ 
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
        xlSortTextAsNumbers 
    With ActiveWorkbook.Worksheets("Ark1").Sort 
        .SetRange Range("B111:C150") 
        .Header = xlNo 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
    Range("C111:C150").Select 
    Selection.ClearContents 
End Sub 

Open in new window

0
 
scifo_dkCommented:
Sorry, this one should sort by scores in Descending order:

Sub Makro1()

    Range("B11:B50").Select
    Selection.Copy
    Range("B111").Select
    ActiveSheet.Paste
    Range("H11:H50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C111").Select
    ActiveSheet.Paste
    Range("B111:C150").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Ark1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ark1").Sort.SortFields.Add Key:=Range("C111:C150"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Ark1").Sort
        .SetRange Range("B111:C150")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C111:C150").Select
    Selection.ClearContents
End Sub

Open in new window

0
 
scifo_dkCommented:
If you want it to sort ascending, just change "xlDescending" in line 16 to "xlAscending".
0
 
circa62Author Commented:
Thanks, I was able to get both to working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.