Set Lists

[MrExcel]

This is being posted on behalf of user Beaumont:

There must be a way to do this. I have a list of 350 students in sets called x1,x2..x7 and y1,y2..y7. Their names are in column A, sets in B and scores in C. I want Excel to take this info, without having to sort it, and give me fourteen rank ordered lists, one for each set, with scores in the left column and names in the right. These could begin in say cell e2 & f2 then a narrow one column gap to h2 and i2 or something.

This would be a great start. Even better would be if we could show how far ahead each student is ahead of the last, e.g if top student in x1 is 5% ahead of next student then this next student should appear 5 cells further down etc.

Best of all would be if we could do a quick visual comparison of the fourteen resultant lists (or perhaps the seven x sets, then the seven y sets) so that we can immediately see if anyone needs to be moved between groups.

Finally I would like someone to bring peace to the Middle East, land a human on Mars and enable my football team to win all their games next season.

Seriously I would appreciate any help you can give as I've been wrestling with this for years and have only achieved partial solutions. I could discuss these with you but don't want to prejudice any solutions you come up with.

Peter Beaumont

 

[Beaumont]

Didn't want to cause any tension folks - it's not worth having an argument over . Thank you for your efforts Nate. If I have to sort the data then I will do so. However I was hoping that the resulting lists would survive later sorts which might be required for other purposes. Of course I could do this by copying and pasting values but then the lists are not dynamic.

I have another spreadsheet which takes data from the league table of football teams and shows me at a glance how far Manchester United are ahead of Liverpool. If Man U are ten points in the lead then Liverpool will appear 10 cells below them, rather than on the next row. I hoped to do the same with my posted problem but the fact that there are fourteen sets all to be treated simultaneously makes it too complicated.

As for the pivot table, it will tell me how many people, for example, in set x1 got 78% but it will not tell me who they are. I can douuble click on any of the numbers in the body of the table but this just opens up a new sheet each time, which is no advantage to me.

So apart from Nate O's efforts your advice seems to be do it by hand. Oh well... thanks anyway Pete

 

[NateO]

Hello again,

Thank you for your efforts Nate. If I have to sort the data then I will do so. However I was hoping that the resulting lists would survive later sorts which might be required for other purposes. Of course I could do this by copying and pasting values but then the lists are not dynamic.

Well, if you want unsorted data to be returned to you in a sorted manner, it's going to need to be sorted one way or another, whether directly in the spreadsheet, via vba, or other means..

Not sure what you mean regarding dynamic lists. You don't have to do this manually, and you can retain the original list. The following makes a copy of the first sheet in the workbook, and sorts the copied sheet:

Sub Cpy_Srt()
Sheets(1).Copy , Sheets(1)
Sheets(2).[a1:c350].Sort [b1], 1, [c1], , 2, , , 0
End Sub

This is an example. You can copy the range only, sort different ways, etc... If you were looking to automate this... Check out the vbe help, there's examples and descriptions regarding sorting and copying.

I leave with a question (for which I think I know the answer). With the following quote translation:

Simplicity is the ultimate sophistication.

I suspect Rear Admiral Grace Murray Hopper's COBOL development team was thinking the very same thing in '59 by truncating years to two digits...

In any case, with the quote, does sophistication = good or does sophistication = bad?

 

[tusharm]

For all you KISS fans, stop reading here. I've streamlined the code above in terms of length:

Sorry, Nate. It was not my intent to annoy you. It was more of a reaction to what I see as an over-used response to all sorts of posts. For some reason people rush to write VBA code that has to be redone every time the OP tweaks her/his requirement. An extreme example was a response some months ago by a mrexcel mvp. Instead of providing a macro, the mvp could have pointed the OP to a *menu item*!

 

[NateO]

Hello,

Sorry, Nate. It was not my intent to annoy you.

No worries, I understand you were mentioning what you deem to be a viable approach that makes sense.

No harm, no foul. Have a good evening.