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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.