Macro To Sort Data Required

[Older Excel Learner]

BEFORE Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Row 9 12.1 33.2 0.3 0.4 14.5 25.6 17.7 0.8 0.9 Row 10 41.1 33.2 33.3 25.4 29.5 38.6 17.7 33.8 0.9 Row 11 Blank Blank Blank Blank Blank Blank Blank Blank Blank Row 12 Blank Blank Blank Blank Blank Blank Blank Blank Blank Row 13 17.1 Blank 0.3 0.4 0.5 33.6 25.7 0.8 0.9 Row 14 50.1 Blank 0.3 33.4 25.5 33.6 25.7 33.8 0.9 Row 15 0.1 Blank 0.3 0.4 0.5 Blank Blank Blank Blank Row 16 0.1 Blank 0.3 0.4 0.5 Blank Blank Blank Blank AFTER Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Row 9 33.2 25.6 17.7 14.5 12.1 0.9 0.8 0.4 0.3 Row 10 41.1 38.6 33.8 33.3 33.2 29.5 25.4 17.7 0.9 Row 11 Blank Blank Blank Blank Blank Blank Blank Blank Blank Row 12 Blank Blank Blank Blank Blank Blank Blank Blank Blank Row 13 33.6 25.7 17.1 0.9 0.8 0.5 0.4 0.3 Blank Row 14 50.1 33.8 33.6 33.4 25.7 25.5 0.9 0.3 Blank Row 15 0.5 0.4 0.3 0.1 Blank Blank Blank Blank Blank Row 16 0.5 0.4 0.3 0.1 Blank Blank Blank Blank Blank <colgroup><col span="12"></colgroup><tbody> </tbody>

<tbody>
</tbody>

Hello All, I am new to Excel and have hit a problem that I require help with. I have tried Custom Sort within Excel but it does not do what I require.

The figures above represent two seperate/different numbers within the same cell each seperated by a . (full stop)

The first number before the . represents an individuals score (always between 0 and 100 inclusive)

The second number after the . represents a number assigned to an individual (currently 9 of us golfers)

The data range is C9:K302 . Each row has no bearing to any other row, so the sorting should begin in C9:K9, then start again in C10:K10 and so on down to the last row C302:K302

The data should be sorted by the numbers BEFORE the full stop , going from left to right, by highest, then zeroes, then blanks as above example.

It doesn't really matter if 2 players have the same score and it puts 33.2 in the cell before 33.1 or if it sorts 0.1 after 0.5, it would be nice if it went from player 1 to player 9, but highest then zeroes, then blanks in any player sequence will suffice.

All cells have formulas in them importing the data from another workbook, all have the same Conditional formatting and all are classified as General cells.

Thanks in advance for any help forthcoming.

 

[Older Excel Learner]

Sorry ignore the table in my post directly above, I made a mess of posting the table, the words explain the problem.


Thanks

 

[sheetspread]

Is it anything to do with 100 being a 3 digit number I wonder.

Not if they're actually numbers. Text with numbers on the other hand can sort inconsistently (try sorting the cells containing the text Row 9, Row 10, Row 11 to see). Text will also have a higher sort value than numbers, and blanks will be last in either direction.

 

[Older Excel Learner]

Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L

Row 9 7.1 20.2 64.3 40.4 1.5 10.6 100.7 Blank 0.9 9.10 Data Before running macro


Row 9 100.7 64.3 40.4 20.2 10.6 9.10 7.1 1.5 0.9 Blank Required after running macro


Hi sheetspread, thanks for sticking with me, when single digit scores are in a cell in the range, they cause a further problem by being ranked ahead of double digit numbers with the 100 being placed last just ahead of zero and blank using the current macro.

Could you please have a fresh look using the data above and see if you could come up with a new macro to try and solve it or even get close to above

My cells are GENERAL format and can't be NUMBER format as then when a tenth player as above is added the 2 decimal places will make player 1 become player 10 also.

The data still starts in C9 but above has 10 players instead of old macro accounting for 9, so the Range end is now L302

Thank you so much.

 

[sheetspread]

They sorted fine in my test, with the general format, what order are you getting? Are these numbers entered by you or downloaded from somewhere? Is the column width affecting the results? Is there any kind of text in the cell (including ="")?