Sort 4 columns individually without losing the order in the other 3

[Ironman]

Hi

Row A:D is filtered so I can sort any of those columns.

My issue is that when I sort Column A Largest to Smallest or any other column then the data in the rows doesn't 'stick' with that column and the numbers become nonsensical.

Here's the data:

Book1
	A	B	C	D	E
178	DIST.	AVE TIME	ROUTE #	#RUNS	ROUTE DETAILS
179	2.2	0:27	32	14	Cullingworth Road/Viaduct (up and down)/Halifax Road/Greenside Lane
180	3.0	0:37	29	11	Cullingworth Road/Up viaduct/Down Station Road/Back up/Down viaduct/Halifax Road/Greenside Lane
181	4.2	0:54	33	15	Cullingworth Road/Viaduct/Station Road/Lane Side/Ling Bob/Wilsden Road/Bents Lane/Hallas Bridge
182	4.2	1:03	41	1	Hallas Bridge/Down Bents Lane/Harden Lane/Smithy Lane/Cross Lane/Lee Lane/Sandy Banks/Harden Lane/Bents Lane/Hallas Bridge
183	5.0	1:08	34	2	Cullingworth Road/Viaduct/Station Road/Lane Side/Ling Bob/Haworth Road/Shay Lane/Crack Lane/Main Street/Bents Lane/Hallas Bridge
184	5.3	1:14	35	3	Cullingworth Road/Viaduct/Station Road/Lane Side/Ling Bob/Haworth Road/Shay Lane/Coplowe Lane/Cross Lane/Smithy Lane/Main Street/Bents Lane/Hallas Bridge
185	5.4	1:11	40	2	Cullingworth Road/Shay Lane/Coplowe Lane/Smithy Lane/Bents Lane/Hallas Bridge
186	6.7	1:27	42	3	Cullingworth Road/Shay Lane/Crack Lane/Main Street/Harden Lane/Mill Hill Top/Wilsden Road/Mad Mile/Home
187	7.0	1:35	36	3	Cullingworth Road/Viaduct/Station Road/Lane Side/Ling Bob/Haworth Road/Wilsden Road/Cottingley Road/Lee Lane/Cross Lane/Smithy Lane/Wilsden Road/Bents Lane/Hallas Bridge
188	7.0	1:31	39	1	Cullingworth Road/Viaduct/Ling Bob/Haworth Road/Shay Lane/Coplowe Lane/Smithy Lane/Main Street/Harden Lane/Mill Hill Top/Wilsden Road/Mad Mile/Greenside Lane
189	7.7	1:44	37	4	Cullingworth Road/Viaduct/Station Road/Lane Side/Ling Bob/Haworth Road/Wilsden Road/Cottingley Road/Lee Lane/Cross Lane/Coplowe Lane/Crack Lane/Main Street/Bents Lane/Hallas Bridge
190	9.4	2:13	38	3	Cullingworth Road/Viaduct/Harecroft/Ling Bob/Wilsden Road/Cottingley Road/Lee Lane/Cross Lane/Coplowe Lane/Crack Lane/Main Street/Harden Lane/Wilsden Road/Mad Mile/Home
191	10.4	2:27	14	25	Hallas Br/Down Bents Ln/Up Harden/Smithy/Lee Farm/Black Hills/Golf Course/Beck Foot Ln/Wagon Ln/Up LLC to top of 5-Rise Locks/Back to 3-Rise Locks & over Br/Brown Cow/Main Rd home
192	13.1	3:12	43	1	Hallas Br/Down Bents Ln/Up Harden/Smithy/Lee Farm/Black Hills/Golf Course/Beck Foot Ln/Wagon Ln/Up LLC beyond Duck House to grass chicane/Back to 3-Rise Locks & over Br/Brown Cow/Main Rd home
193	15.3	3:45	44	1	Hallas Br/Down Bents Ln/Up Harden/Smithy/Lee Farm/Black Hills/Golf Course/Beck Foot Ln/Wagon Ln/Up LLC to junct. of Granby Ln (Riddlesden)/Back to 3-Rise Locks & over Br/Brown Cow/Main Rd home
Analysis

Cell Formulas
Range	Formula
B179	=C66
B180	=C47
B181	=C86
B182	=C151
B183	=C94
B184	=C102
B185	=C143
B186	=C159
B187	=C110
B188	=C135
B189	=C119
B190	=C127
B191	=C31
B192	=C167
B193	=C175
D179	=A66
D180	=A47
D181	=A86
D182	=A151
D183	=A94
D184	=A102
D185	=A143
D186	=A159
D187	=A110
D188	=A135
D189	=A119
D190	=A127
D191	=A31
D192	=A167
D193	=A167

Hope you can help - thank you.

 

[RasGhul]

Using a hyperlink formula based on another row should work.

 

[Michael M]

I'm not sure I follow, but why not insert a single thin blank column after col "D" then sort "A:D"

 

[Ironman]

Thanks RasGhul and Michael.

RasGhul - I'm just reading up on the HYPERLINK formula to see if that will resolve my issue i.e. will the linked cell become incorrect when I insert another row (with the alternative then being to name each of the 75 cells separately, which is laborious)

Michael - that unfortunately won't work because the cell values are hyperlinks

 

[Michael M]

Ok....worth a try...happy running...

 

[RasGhul]

You maybe able to use something like this so that it doesn't matter what row its filtered to in Analysis, it always returns the correct row in Training Log hyperlink address;

Yet to filter and test I'll leave that up to you mate, but the theory is there. Also as long as there is only one exercise event on that date the formula should work. It would be better to add/insert the Route Number to the Training Log so the lookup value for match can be made more unique.


=HYPERLINK("#'Training Log'!"&ADDRESS(MATCH(B2,'Training Log'!$A$1:$A$50,0),8),"Click Training Log for Comments")


https://www.dropbox.com/s/4bhi6laomq788dn/Hyperlink demo_Ironman.xlsx?dl=0

 

[Ironman]

Hi RasGhul, many thanks for the formula/example.

The issue I'm having is that the hyperlinks aren't linked to text but to numerical values - see below

Book1
	A	B	C	D	E
1	RANK	DATE	TIME	PACE	LOG ENTRY
2
3	4	30.10.2005	1:28:32	8:31	LOG ENTRY
4	5	13.11.2005	1:29:08	8:34	LOG ENTRY
5	2	17.12.2005	1:26:33	8:19	LOG ENTRY
6	13	25.12.2005	1:34:27	9:05	LOG ENTRY
7	8	31.12.2005	1:32:10	8:52	LOG ENTRY
8	6	16.07.2006	1:29:33	8:37	LOG ENTRY
9	10	29.07.2006	1:32:31	8:54	LOG ENTRY
10	15	17.03.2007	1:34:58	9:08	LOG ENTRY
11	16	29.07.2007	1:36:01	9:14	LOG ENTRY
12	20	05.08.2007	1:40:19	9:39	LOG ENTRY
13	12	19.08.2007	1:33:48	9:01	LOG ENTRY
14	3	05.09.2007	1:28:07	8:28	LOG ENTRY
15	9	21.09.2007	1:32:10	8:52	LOG ENTRY
16	7	25.09.2007	1:30:16	8:41	LOG ENTRY
17	1	26.10.2007	1:24:22	8:07	LOG ENTRY
18	11	04.05.2008	1:33:20	8:58	LOG ENTRY
19	17	25.05.2008	1:37:05	9:20	LOG ENTRY
20	14	23.08.2008	1:34:47	9:07	LOG ENTRY
21	19	12.04.2009	1:37:44	9:24	LOG ENTRY
22	21	26.04.2009	1:41:57	9:48	LOG ENTRY
23	18	20.09.2009	1:37:09	9:21	LOG ENTRY
24
25	1	07.10.2012	1:54:15	10:59	LOG ENTRY
26
27	1	10.02.2019	2:26:49	14:07	LOG ENTRY
28	2	10.03.2019	2:28:02	14:14	LOG ENTRY
29	3	23.03.2019	2:29:00	14:20	LOG ENTRY
30
31	25	AVE (2019-)	2:27:57
32
33
34
35	11	17.04.2018	0:41:35	13:52	LOG ENTRY
36	4	16.11.2018	0:36:08	12:03	LOG ENTRY
37	5	05.12.2018	0:37:44	12:35	LOG ENTRY
38	8	07.12.2018	0:38:06	12:42	LOG ENTRY
39	6	15.12.2018	0:37:47	12:36	LOG ENTRY
40	9	31.12.2018	0:39:44	13:15	LOG ENTRY
41	3	01.01.2019	0:36:04	12:01	LOG ENTRY
42	10	03.01.2019	0:39:52	13:17	LOG ENTRY
43	7	05.01.2019	0:37:51	12:37	LOG ENTRY
44	2	13.01.2019	0:35:32	11:51	LOG ENTRY
45	1	17.01.2019	0:35:30	11:50	LOG ENTRY
46
47	11	AVERAGE	0:37:48
48
49
50
51	7	08.11.2018	0:26:09	11:53	LOG ENTRY
52	1	10.11.2018	0:24:38	11:12	LOG ENTRY
53	3	13.11.2018	0:24:52	11:18	LOG ENTRY
54	10	20.11.2018	0:27:54	12:41	LOG ENTRY
55	2	22.11.2018	0:24:40	11:13	LOG ENTRY
56	4	24.11.2018	0:24:58	11:21	LOG ENTRY
57	8	27.11.2018	0:26:59	12:16	LOG ENTRY
58	6	29.11.2018	0:25:47	11:43	LOG ENTRY
59	9	22.12.2018	0:27:04	12:18	LOG ENTRY
60	5	24.12.2018	0:25:31	11:36	LOG ENTRY
61	11	09.01.2019	0:28:30	12:57	LOG ENTRY
62	12	14.02.2019	0:29:53	13:35	LOG ENTRY
63	14	29.03.2019	0:33:33	15:15	LOG ENTRY
64	13	04.04.2019	0:31:28	14:18	LOG ENTRY
65
66	14	AVERAGE	0:27:17
67
68
69
70	5	09.12.2018	0:52:48	12:34	LOG ENTRY
71	9	13.12.2018	0:54:13	12:55	LOG ENTRY
72	15	20.12.2018	0:58:46	14:00	LOG ENTRY
73	6	25.12.2018	0:52:59	12:37	LOG ENTRY
74	3	27.12.2018	0:52:01	12:23	LOG ENTRY
75	7	07.01.2019	0:53:22	12:42	LOG ENTRY
76	1	21.01.2019	0:49:43	11:50	LOG ENTRY
77	12	25.01.2019	0:57:24	13:40	LOG ENTRY
78	8	31.01.2019	0:54:07	12:53	LOG ENTRY
79	11	04.02.2019	0:56:25	13:26	LOG ENTRY
80	2	08.02.2019	0:50:31	12:02	LOG ENTRY
81	4	12.02.2019	0:52:09	12:25	LOG ENTRY
82	10	20.02.2019	0:55:15	13:09	LOG ENTRY
83	13	26.02.2019	0:57:53	13:47	LOG ENTRY
84	14	02.04.2019	0:58:41	13:58	LOG ENTRY
85
86	15	AVERAGE	0:54:25
87
88
89
90	2	18.12.2018	1:09:25	13:53	LOG ENTRY
91	1	19.03.2019	1:06:40	13:20	LOG ENTRY
92
93
94	2	AVERAGE	1:08:03
Analysis

Cell Formulas
Range	Formula
A3	=RANK(C3,$C$3:$C$23,1)
A4	=RANK(C4,$C$3:$C$23,1)
A5	=RANK(C5,$C$3:$C$23,1)
A6	=RANK(C6,$C$3:$C$23,1)
A7	=RANK(C7,$C$3:$C$23,1)
A8	=RANK(C8,$C$3:$C$23,1)
A9	=RANK(C9,$C$3:$C$23,1)
A10	=RANK(C10,$C$3:$C$23,1)
A11	=RANK(C11,$C$3:$C$23,1)
A12	=RANK(C12,$C$3:$C$23,1)
A13	=RANK(C13,$C$3:$C$23,1)
A14	=RANK(C14,$C$3:$C$23,1)
A15	=RANK(C15,$C$3:$C$23,1)
A16	=RANK(C16,$C$3:$C$23,1)
A17	=RANK(C17,$C$3:$C$23,1)
A18	=RANK(C18,$C$3:$C$23,1)
A19	=RANK(C19,$C$3:$C$23,1)
A20	=RANK(C20,$C$3:$C$23,1)
A21	=RANK(C21,$C$3:$C$23,1)
A22	=RANK(C22,$C$3:$C$23,1)
A23	=RANK(C23,$C$3:$C$23,1)
A25	=RANK(C25,$C$25:$C$25,1)
A27	=RANK(C27,$C$27:$C$30,1)
A28	=RANK(C28,$C$27:$C$30,1)
A29	=RANK(C29,$C$27:$C$30,1)
A31	=COUNTA(A3:A30)
A35	=RANK(C35,$C$35:$C$46,1)
A36	=RANK(C36,$C$35:$C$46,1)
A37	=RANK(C37,$C$35:$C$46,1)
A38	=RANK(C38,$C$35:$C$46,1)
A39	=RANK(C39,$C$35:$C$46,1)
A40	=RANK(C40,$C$35:$C$46,1)
A41	=RANK(C41,$C$35:$C$46,1)
A42	=RANK(C42,$C$35:$C$46,1)
A43	=RANK(C43,$C$35:$C$46,1)
A44	=RANK(C44,$C$35:$C$46,1)
A45	=RANK(C45,$C$35:$C$46,1)
A47	=COUNTA(A35:A46)
A51	=RANK(C51,$C$51:$C$65,1)
A52	=RANK(C52,$C$51:$C$65,1)
A53	=RANK(C53,$C$51:$C$65,1)
A54	=RANK(C54,$C$51:$C$65,1)
A55	=RANK(C55,$C$51:$C$65,1)
A56	=RANK(C56,$C$51:$C$65,1)
A57	=RANK(C57,$C$51:$C$65,1)
A58	=RANK(C58,$C$51:$C$65,1)
A59	=RANK(C59,$C$51:$C$65,1)
A60	=RANK(C60,$C$51:$C$65,1)
A61	=RANK(C61,$C$51:$C$65,1)
A62	=RANK(C62,$C$51:$C$65,1)
A63	=RANK(C63,$C$51:$C$65,1)
A64	=RANK(C64,$C$51:$C$65,1)
A66	=COUNTA(A51:A65)
A70	=RANK(C70,$C$70:$C$85,1)
A71	=RANK(C71,$C$70:$C$85,1)
A72	=RANK(C72,$C$70:$C$85,1)
A73	=RANK(C73,$C$70:$C$85,1)
A74	=RANK(C74,$C$70:$C$85,1)
A75	=RANK(C75,$C$70:$C$85,1)
A76	=RANK(C76,$C$70:$C$85,1)
A77	=RANK(C77,$C$70:$C$85,1)
A78	=RANK(C78,$C$70:$C$85,1)
A79	=RANK(C79,$C$70:$C$85,1)
A80	=RANK(C80,$C$70:$C$85,1)
A81	=RANK(C81,$C$70:$C$85,1)
A82	=RANK(C82,$C$70:$C$85,1)
A83	=RANK(C83,$C$70:$C$85,1)
A84	=RANK(C84,$C$70:$C$85,1)
A86	=COUNTA(A70:A85)
D3	=C3/10.4
D4	=C4/10.4
D5	=C5/10.4
D6	=C6/10.4
D7	=C7/10.4
D8	=C8/10.4
D9	=C9/10.4
D10	=C10/10.4
D11	=C11/10.4
D12	=C12/10.4
D13	=C13/10.4
D14	=C14/10.4
D15	=C15/10.4
D16	=C16/10.4
D17	=C17/10.4
D18	=C18/10.4
D19	=C19/10.4
D20	=C20/10.4
D21	=C21/10.4
D22	=C22/10.4
D23	=C23/10.4
D25	=C25/10.4
D27	=C27/10.4
D28	=C28/10.4
D29	=C29/10.4
D35	=C35/3
D36	=C36/3
D37	=C37/3
D38	=C38/3
D39	=C39/3
D40	=C40/3
D41	=C41/3
D42	=C42/3
D43	=C43/3
D44	=C44/3
D45	=C45/3
D51	=C51/2.2
D52	=C52/2.2
D53	=C53/2.2
D54	=C54/2.2
D55	=C55/2.2
D56	=C56/2.2
D57	=C57/2.2
D58	=C58/2.2
D59	=C59/2.2
D60	=C60/2.2
D61	=C61/2.2
D62	=C62/2.2
D63	=C63/2.2
D64	=C64/2.2
D70	=C70/4.2
D71	=C71/4.2
D72	=C72/4.2
D73	=C73/4.2
D74	=C74/4.2
D75	=C75/4.2
D76	=C76/4.2
D77	=C77/4.2
D78	=C78/4.2
D79	=C79/4.2
D80	=C80/4.2
D81	=C81/4.2
D82	=C82/4.2
D83	=C83/4.2
D84	=C84/4.2
C31	=AVERAGE(C27:C30)
C47	=AVERAGE(C35:C46)
C66	=AVERAGE(C51:C65)
C86	=AVERAGE(C70:C85)

 

[RasGhul]

What is your actual goal Iron?

 

[Ironman]

Referring back to my first post, all I want to do is be able to sort any of the first 4 columns from smallest to largest and largest to smallest. I think the cell links are making this difficult though.

 

[mole999]

If you were confident that the 4 variables would always return a unique value the concatenate the four and use a vlookup against the unique set

 

[Ironman]

Thanks Mole - I understand what you're saying but not how to go about it