Ranking and sorting a number with a dash, but not logically

[uknusa]

Hello again.

Still to do with this cricket thing I have bugging me. I need to rank and sort a TOP 10 Best Bowling Figures, for some players. The 1st number before the "-" in Col B represents wickets. The higher the better so the highest here would always qualify for the list. The 2nd number after the "-" represents the number of runs given(against). The lower number here should appear above the higher number if it has the same number wickets taken to someone else.

EXAMPLE (using 11 names and figures):

Col A Col B
Walt 5-50
Tim 2-22
John 4-34
Hank 4-33
Jabba 3-45
Stuart 4-40
Chris 3-41
Chuck 3-56
Roy 5-49
Paul 2-20
Bob 4-18

What I want the Top 10 to end up as must look like this:

ROY 5-49
Walt 5-50
Bob 4-18
Hank 4-33
John 4-34
Stuart 4-40
Chris 3-41
Jabba 3-45
Chuck 3-56
Paul 2-20

Thanks for your help!

[alansidman]

1. Data-->Text to Columns, Delimiter is "-"
2. Highlight all columns
3. Data-->Sort. Column B--Highest to lowest, column C Lowest to Highest
4. In Column D. concatenate Columns B and C with a separator "-"
5. In column D, Copy and Paste Special (Values) to Column D (on top of itself)
6. Delete columns B and C.

v	A	B
1	Roy	5-49
2	Walt	5-50
3	Bob	4-18
4	Hank	4-33
5	John	4-34
6	Stuart	4-40
7	Chris	3-41
8	Jabba	3-45
9	Chuck	3-56
10	Paul	2-20
11	Tim	2-22

[maras_mak]

hi, my proposition:

The first row is empty (data starts with A2)
1. Copy col. B to C.
2. Col. C -> Text to Columns, Delimiter is "-"
3. select all data (col. A: D)
4. sort - Col. C descending
5. add level
6. sort - col. D ascending
7. add level
8. sort - col. A ascending
9. Delete col. C and D

Best regards,
Maras

[uknusa]

Thanks for you help. In the end I came up with another way although not automated which I would of preferred.

1. I pulled the Top 10 into an automated range.
2. I then pasted values only into a new range.
3. I simply renumbered from 1 to 10 and then sorted manually by using the 1 to 10 numbering

Thanks though.