MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 308|Reply: 8

How to sort data

[Copy link]

7

Topics

27

Posts

81

Integral

Member

Rank: 2

Integral
81
Post on 10-5-2016 13:05:17 | All posts |Read mode
Hello,
Using the example in the attached picture from the link below, how would you also sort the data by
(1) name ascending / descending
(2) salary highest to lowest
(3) ID asending / descending?
http://www.tutorialspoint.com/sql/sq...or-clauses.htm
I've tried:
Select ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY  2000 or AGE  25
ORDER BY SALARY DESC
However I play with order by command, I end up with an error.
Thank you for help
















Reply

Props Report

7

Topics

27

Posts

81

Integral

Member

Rank: 2

Integral
81
 Author| Post on 10-5-2016 14:06:41 | All posts



I will note that if I remove the second function (and age  25) for AND ORDER BY SALARY DESC that salary is ordered amounts  2000.
I just don't know how to pull certain data with multiple conditions and sort it by a particular column
Reply Support Opposition

Props Report

16

Topics

186

Posts

444

Integral

Conqueror

Rank: 3Rank: 3

Integral
444
Post on 1-18-2017 22:54:52 | All posts



You might try sorting the players by their average.  Then start going down the list from top to bottom putting them in team first going from highest average then the next player would be from from the bottom lowest to highest. Then repeat until all players have been assigned.




Reply Support Opposition

Props Report

63

Topics

2079

Posts

4419

Integral

Forum Legend

Rank: 8Rank: 8

Integral
4419
Post on 1-18-2017 23:50:11 | All posts



Sort macro:
Sub srtCol()
Columns(A:B).Select
Selection.Sort Key1:=Range(B2), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
   
End SubThere is also a formula version in the workbook, Column C1, pasted and drag down.








  • Sort MAX to MIN.xlsm
    (17.1 KB, 25 views)
    Download















  • Reply Support Opposition

    Props Report

    34

    Topics

    1969

    Posts

    4182

    Integral

    Forum Legend

    Rank: 8Rank: 8

    Integral
    4182
    Post on 1-19-2017 01:29:57 | All posts



    I think this will do what you want. The players in columns A  B will be sorted by average. Make sure that only players and their scores are in these two columns and that the rows of data are contiguous (no blank rows in the data range).
    Enter in the number of teams in cell E1 and click on the Assign Teams Button.








  • Bowl.xlsb
    (20.2 KB, 28 views)
    Download















  • Reply Support Opposition

    Props Report

    0

    Topics

    13

    Posts

    30

    Integral

    Member

    Rank: 2

    Integral
    30
    Post on 1-19-2017 02:06:41 | All posts



    Sorry for the late reply, and thanks for yours!  From a formatting perspective, this is exactly what I need!
    However, when looking at a final team I notice that since these are in order of average, the team average will ultimately get skewed downward the higher the team #. For example, I put in 12 players (I added one more player (Nate 130 avg) into the table in the original post) and sorted for 4 teams using the macro.
    The end result is that each team has a lower overall average as you progress. My goal is to sort the teams so the team averages are either equal, or as close to equal as they can get.
    Is there any way to do that in this macro?  Thanks in advance for your help!
    (sorry for the format of this table, for some reason it would not let me post an html table)
                    Team 1      Team 2      Team 3      Team 4
    Jack        Pete        Mihir       Perry
    Hank        Sally       Scott       Jill
    James       John        Chris       Nate
    Team Average:   169         163         158         151    (note averages going down, need the macro to sort players so the team averages are as close to the same as possible)




    Reply Support Opposition

    Props Report

    34

    Topics

    1969

    Posts

    4182

    Integral

    Forum Legend

    Rank: 8Rank: 8

    Integral
    4182
    Post on 1-19-2017 03:14:41 | All posts



    Ok, so here is a rewrite with a change in logic. The original logic was a straight deal off the top of the deck, distributing the players in the order they came off the stack. The new logic is to deal off the top and bottom of the deck paring the lowest scoring player with the highest scoring player, second lowest with second highest, etc.








  • Bowl 2.xlsb
    (20.8 KB, 18 views)
    Download













  • Reply Support Opposition

    Props Report

    63

    Topics

    2079

    Posts

    4419

    Integral

    Forum Legend

    Rank: 8Rank: 8

    Integral
    4419
    Post on 1-19-2017 04:50:02 | All posts



    Thanks for the Add Rep.









    Reply Support Opposition

    Props Report

    0

    Topics

    13

    Posts

    30

    Integral

    Member

    Rank: 2

    Integral
    30
    Post on 1-19-2017 05:34:43 | All posts



    This is great! I ran a few simulations and the team averages were always very close. Nice!
    The only issue I ran into so far is if I change the # of teams. For example, try the same table but with 4 teams (12 players). It ends up with two teams of 4 and two teams of 2.  
    If I tried 15 players, 4 teams it distributed players perfectly. However, 15 players, 5 teams did not work. I'm guessing due to the pairing.
    I tried to look at the code, but it's above my head

      Is there a way to adjust this to compensate?  Thanks in advance again!
    Reply Support Opposition

    Props Report

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 9-24-2017 21:29 , Processed in 0.180305 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!