Macro To Sort Data Required

O

Older Excel Learner

New Member
Joined
Feb 3, 2018
Messages
12
[TABLE="class: outer_border, width: 1000, align: center"]
<tbody>[TR]
[TD][TABLE="width: 900"]
<colgroup><col span="12"></colgroup><tbody>[TR]
[TD]BEFORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col I[/TD]
[TD]Col J[/TD]
[TD]Col K[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12.1[/TD]
[TD]33.2[/TD]
[TD]0.3[/TD]
[TD]0.4[/TD]
[TD]14.5[/TD]
[TD]25.6[/TD]
[TD]17.7[/TD]
[TD]0.8[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]41.1[/TD]
[TD]33.2[/TD]
[TD]33.3[/TD]
[TD]25.4[/TD]
[TD]29.5[/TD]
[TD]38.6[/TD]
[TD]17.7[/TD]
[TD]33.8[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17.1[/TD]
[TD]Blank[/TD]
[TD]0.3[/TD]
[TD]0.4[/TD]
[TD]0.5[/TD]
[TD]33.6[/TD]
[TD]25.7[/TD]
[TD]0.8[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]50.1[/TD]
[TD]Blank[/TD]
[TD]0.3[/TD]
[TD]33.4[/TD]
[TD]25.5[/TD]
[TD]33.6[/TD]
[TD]25.7[/TD]
[TD]33.8[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.1[/TD]
[TD]Blank[/TD]
[TD]0.3[/TD]
[TD]0.4[/TD]
[TD]0.5[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.1[/TD]
[TD]Blank[/TD]
[TD]0.3[/TD]
[TD]0.4[/TD]
[TD]0.5[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AFTER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col I[/TD]
[TD]Col J[/TD]
[TD]Col K[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]33.2[/TD]
[TD]25.6[/TD]
[TD]17.7[/TD]
[TD]14.5[/TD]
[TD]12.1[/TD]
[TD]0.9[/TD]
[TD]0.8[/TD]
[TD]0.4[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]41.1[/TD]
[TD]38.6[/TD]
[TD]33.8[/TD]
[TD]33.3[/TD]
[TD]33.2[/TD]
[TD]29.5[/TD]
[TD]25.4[/TD]
[TD]17.7[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]33.6[/TD]
[TD]25.7[/TD]
[TD]17.1[/TD]
[TD]0.9[/TD]
[TD]0.8[/TD]
[TD]0.5[/TD]
[TD]0.4[/TD]
[TD]0.3[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]50.1[/TD]
[TD]33.8[/TD]
[TD]33.6[/TD]
[TD]33.4[/TD]
[TD]25.7[/TD]
[TD]25.5[/TD]
[TD]0.9[/TD]
[TD]0.3[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.5[/TD]
[TD]0.4[/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]Row 16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.5[/TD]
[TD]0.4[/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
This can be done with the reverse pivot table:

https://www.youtube.com/watch?v=xmqTN0X-AgY

In the resulting table ctrl-H replace Row(space) with nothing so you just have numbers, and paste the mod() formula, drag down, sort, then re-pivot to get:


Excel 2010
ABCDEFGHIJKLMNOPQ
1RowColumnValue
29133.2
39225.6
49317.7
59414.5Sum of ValueColumn Labels
69512.1Row Labels1234567891011
7960.9933.225.617.714.512.10.90.80.40.3
8970.81041.138.633.833.333.229.525.417.70.9
9980.411
10990.312
119101333.625.717.10.90.80.50.40.3
129111450.133.833.633.425.725.50.90.3
1310141.1150.50.40.30.1
1410238.6160.50.40.30.1
1510333.8
1610433.3
1710533.2
1810629.5
1910725.4
2010817.7
211090.9
221010
231011
24111
25112
26113
27114
28115
29116
30117
31118
32119
331110
341111
35121
36122
37123
38124
39125
40126
41127
42128
43129
441210
451211
4613133.6
4713225.7
4813317.1
491340.9
501350.8
511360.5
521370.4
531380.3
54139
551310
561311
5714150.1
5814233.8
5914333.6
6014433.4
6114525.7
6214625.5
631470.9
641480.3
65149
661410
671411
681510.5
691520.4
701530.3
711540.1
72155
73156
74157
75158
76159
771510
781511
791610.5
801620.4
811630.3
821640.1
83165
84166
85167
86168
87169
881610
891611
Sheet14
Cell Formulas
RangeFormula
B2=MOD(ROW(A1)-1,11)+1
 
Last edited:
Upvote 0
sheetspread, firstly thank you ever so much for taking the time to reply, it is greatly appreciated.

To be honest, I am lost looking at above. The reason I was looking for a macro is that all cells outside the range I have given are used and as a result of running other macros, my ranges as given above are the end result.

Could you provide me with a macro solution? If not again many thanks for your reply. Thank You.
 
Upvote 0

Excel 2010
ABCDEFGHIJKL
9Row 912.133.20.30.414.525.617.70.80.9
10Row 1041.133.233.325.429.538.617.733.80.9
11Row 11
12Row 12
13Row 1317.10.30.40.533.625.70.80.9
14Row 1450.10.333.425.533.625.733.80.9
15Row 150.10.30.40.5
16Row 160.10.30.40.5
Sheet12 (7)


Code: 
Sub Sorttest()
Dim i%, lr%, myrange As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 9 To lr
Set myrange = Cells(9, 4).Resize(, 9)
myrange.Offset(i - 9, 0).Sort Key1:=myrange.Offset(i - 9, 0), Order1:=xlDescending, Orientation:=xlLeftToRight
Next
End Sub


Excel 2010
ABCDEFGHIJKL
9Row 933.225.617.714.512.10.90.80.40.3
10Row 1041.138.633.833.333.229.525.417.70.9
11Row 11
12Row 12
13Row 1333.625.717.10.90.80.50.40.3
14Row 1450.133.833.633.425.725.50.90.3
15Row 150.50.40.30.1
16Row 160.50.40.30.1
Sheet12 (7)


This should work with formulas too (can be adjusted otherwise)
 
Last edited:
Upvote 0
A little neater:

Code: 
Sub Sorttest()
Dim i%, lr%, myrange As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 9 To lr
Set myrange = Cells(i, 4).Resize(, 9)
myrange.Sort Key1:=myrange, Order1:=xlDescending, Orientation:=xlLeftToRight
Next
End Sub
 
Upvote 0
Thanks for replying.

Your code is starting in Column D not Column C, I have changed the 4 to 3 in your code. I get that bit.

it seems to leave the number 100 before any full stop ranked behind where there are other lower numbers such as 33.1 or 86.9 in the row for example if the row only contains for example 100.1 and 0.8 it will rank it corrrectly

as I mentioned above the numbers before the full stop will always be between 0 and 100 inclusive. I didn't give any 100 examples above as I thought the previous covered it. Sorry.

Can I ask you is this possible.

When I started this golf hobby project, I pieced together other macros I found on this excellent site, they always had the cell ranges named as in A1:B1 for example

With your code as written above if I add another player bringing the range from C9:K302 (9 player columns) to a new range of C9:L302 (10 player columns)

I have to change all the nines to tens in your code as written, could you write the code as C9:K302 range so that I will only have change one entry. If not no problem.

Thanks for taking the time to help me out, it really is so great, as I said I trawled through archive posts hoping that I could complete it entirely unaided as a complete excel beginner. I thought I was there and didn't forsee this final sorting problem. So your help really is appreciated.
 
Upvote 0
I'm not sure what you mean about 100, e.g.:


Excel 2010
ABCDEFGHIJK
9Row 912.133.20.30.414.525.617.70.80.9
10Row 1041.133.233.325.429.538.617.733.80.9
11Row 11
12Row 12
13Row 1317.10.30.40.533.625.70.80.9
14Row 1450.10.333.425.533.625.733.80.9
15Row 150.10.30.40.5
16Row 160.10.30.40.5
17Row 175910099
18Row 18
Sheet12 (12)


Code: 
Sub Sorttest()
Dim i%, myrange As Range
For i = 9 To 302
Set myrange = Cells(i, 3).Resize(, 9)
myrange.Sort Key1:=myrange, Order1:=xlDescending, Orientation:=xlLeftToRight
Next
End Sub


Excel 2010
ABCDEFGHIJK
9Row 933.225.617.714.512.10.90.80.40.3
10Row 1041.138.633.833.333.229.525.417.70.9
11Row 11
12Row 12
13Row 1333.625.717.10.90.80.50.40.3
14Row 1450.133.833.633.425.725.50.90.3
15Row 150.50.40.30.1
16Row 160.50.40.30.1
17Row 171009995
18Row 18
Sheet12 (12)


what should be different?


If you add new columns, just resize by 9+n (can be dynamic like the rows were but i'm not sure how the rest of your sheet looks)
 
Last edited:
Upvote 0
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col I[/TD]
[TD]Col J[/TD]
[TD]Col K[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD][/TD]
[TD][/TD]
[TD]33.2[/TD]
[TD]25.6[/TD]
[TD]17.7[/TD]
[TD]14.5[/TD]
[TD]12.1[/TD]
[TD]0.9[/TD]
[TD]0.8[/TD]
[TD]0.4[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD][/TD]
[TD][/TD]
[TD]41.1[/TD]
[TD]38.6[/TD]
[TD]33.8[/TD]
[TD]33.3[/TD]
[TD]33.2[/TD]
[TD]29.5[/TD]
[TD]25.4[/TD]
[TD]17.7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</body>If I substitute the values in both cell G9 and cell G10 in my original Before table above to 100.5 in both cells.

In my AFTER table in will place 100.5 in G9 and 100.5 in J10, behind other lower numbers but ahead of zeroes instead of in the first cell

If a 100 value is the only number in any cell in the nine cell row range and the other cells are zero, blank or a mixture of zeroes and blanks, it will place it correctly in the first C Column cell.

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


Thanks for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Matrix returns wrong values
Replies
3
Views
395
Peter_SSs
Peter_SSs
P
  • Solved
Transpose with dates problem - from the dd/mm/yy to mm/dd/yyyy
Replies
4
Views
585
Alex Blakenburg
A
W
  • Question Question
Can A Macro Solve This Problem
Replies
4
Views
447
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question Question
Use VBA to find slope =0
Replies
4
Views
2K
artz
A
A
  • Question Question
Sales Related Questions
Replies
4
Views
669
anneb_87
A

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top