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.

 

[sheetspread]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Row	Column	Value
2	9	1	33.2
3	9	2	25.6
4	9	3	17.7
5	9	4	14.5			Sum of Value	Column Labels
6	9	5	12.1			Row Labels	1	2	3	4	5	6	7	8	9	10	11
7	9	6	0.9			9	33.2	25.6	17.7	14.5	12.1	0.9	0.8	0.4	0.3
8	9	7	0.8			10	41.1	38.6	33.8	33.3	33.2	29.5	25.4	17.7	0.9
9	9	8	0.4			11
10	9	9	0.3			12
11	9	10				13	33.6	25.7	17.1	0.9	0.8	0.5	0.4	0.3
12	9	11				14	50.1	33.8	33.6	33.4	25.7	25.5	0.9	0.3
13	10	1	41.1			15	0.5	0.4	0.3	0.1
14	10	2	38.6			16	0.5	0.4	0.3	0.1
15	10	3	33.8
16	10	4	33.3
17	10	5	33.2
18	10	6	29.5
19	10	7	25.4
20	10	8	17.7
21	10	9	0.9
22	10	10
23	10	11
24	11	1
25	11	2
26	11	3
27	11	4
28	11	5
29	11	6
30	11	7
31	11	8
32	11	9
33	11	10
34	11	11
35	12	1
36	12	2
37	12	3
38	12	4
39	12	5
40	12	6
41	12	7
42	12	8
43	12	9
44	12	10
45	12	11
46	13	1	33.6
47	13	2	25.7
48	13	3	17.1
49	13	4	0.9
50	13	5	0.8
51	13	6	0.5
52	13	7	0.4
53	13	8	0.3
54	13	9
55	13	10
56	13	11
57	14	1	50.1
58	14	2	33.8
59	14	3	33.6
60	14	4	33.4
61	14	5	25.7
62	14	6	25.5
63	14	7	0.9
64	14	8	0.3
65	14	9
66	14	10
67	14	11
68	15	1	0.5
69	15	2	0.4
70	15	3	0.3
71	15	4	0.1
72	15	5
73	15	6
74	15	7
75	15	8
76	15	9
77	15	10
78	15	11
79	16	1	0.5
80	16	2	0.4
81	16	3	0.3
82	16	4	0.1
83	16	5
84	16	6
85	16	7
86	16	8
87	16	9
88	16	10
89	16	11
Sheet14

Cell Formulas
Range	Formula
B2	=MOD(ROW(A1)-1,11)+1

 

[sheetspread]

(I mean sort first)

 

[Older Excel Learner]

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.

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
9	Row 9			12.1	33.2	0.3	0.4	14.5	25.6	17.7	0.8	0.9
10	Row 10			41.1	33.2	33.3	25.4	29.5	38.6	17.7	33.8	0.9
11	Row 11
12	Row 12
13	Row 13			17.1		0.3	0.4	0.5	33.6	25.7	0.8	0.9
14	Row 14			50.1		0.3	33.4	25.5	33.6	25.7	33.8	0.9
15	Row 15			0.1		0.3	0.4	0.5
16	Row 16			0.1		0.3	0.4	0.5
Sheet12 (7)

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
	A	B	C	D	E	F	G	H	I	J	K	L
9	Row 9			33.2	25.6	17.7	14.5	12.1	0.9	0.8	0.4	0.3
10	Row 10			41.1	38.6	33.8	33.3	33.2	29.5	25.4	17.7	0.9
11	Row 11
12	Row 12
13	Row 13			33.6	25.7	17.1	0.9	0.8	0.5	0.4	0.3
14	Row 14			50.1	33.8	33.6	33.4	25.7	25.5	0.9	0.3
15	Row 15			0.5	0.4	0.3	0.1
16	Row 16			0.5	0.4	0.3	0.1
Sheet12 (7)

This should work with formulas too (can be adjusted otherwise)

 

[sheetspread]

A little neater:

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

 

[Older Excel Learner]

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.

 

[Older Excel Learner]

Thanks, have just seen your new code post.

 

[sheetspread]

I'm not sure what you mean about 100, e.g.:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
9	Row 9		12.1	33.2	0.3	0.4	14.5	25.6	17.7	0.8	0.9
10	Row 10		41.1	33.2	33.3	25.4	29.5	38.6	17.7	33.8	0.9
11	Row 11
12	Row 12
13	Row 13		17.1		0.3	0.4	0.5	33.6	25.7	0.8	0.9
14	Row 14		50.1		0.3	33.4	25.5	33.6	25.7	33.8	0.9
15	Row 15		0.1		0.3	0.4	0.5
16	Row 16		0.1		0.3	0.4	0.5
17	Row 17		5		9		100		99
18	Row 18
Sheet12 (12)

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
	A	B	C	D	E	F	G	H	I	J	K
9	Row 9		33.2	25.6	17.7	14.5	12.1	0.9	0.8	0.4	0.3
10	Row 10		41.1	38.6	33.8	33.3	33.2	29.5	25.4	17.7	0.9
11	Row 11
12	Row 12
13	Row 13		33.6	25.7	17.1	0.9	0.8	0.5	0.4	0.3
14	Row 14		50.1	33.8	33.6	33.4	25.7	25.5	0.9	0.3
15	Row 15		0.5	0.4	0.3	0.1
16	Row 16		0.5	0.4	0.3	0.1
17	Row 17		100	99	9	5
18	Row 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)

 

[Older Excel Learner]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

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

<tbody>
</tbody>

</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.