Random sort only if cell has a value

[ockie]

Hi,
I am trying to Random sort from column B but only if there is a value in column B. after the random sort from col B, data in the example below rows 9,11,15,16 will be under the last number in col B.
The sheet name is : template and on the sheet the columns in the sort range are B:L and the row range is 7:40.

this is for a sports club and if some is not going to attend I delete the number in col B, so I random sort only those who will be in attendance.


thanks in advance

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

7​

0.6459567​

21​

45​

Alan

Smith

=if(h7+"","",k7+j7)

8​

0.9768443​

14​

52​

Greg

Jones

0​

9​

25​

41​

Bruce

Brown

0​

10​

0.0316331​

19​

47​

Colin

White

0​

11​

26​

40​

Tony

Black

0​

12​

0.7737984​

29​

37​

Roger

Colt

0​

13​

0.9954291​

22​

44​

Joe

Cole

0​

14​

0.322505​

23​

43​

Ron

George

0​

15​

19​

47​

Miles

Michaels

0​

16​

18​

48​

Len

Werth

0​

17​

0.1097377​

11​

55​

Rick

Johnston

0​

 

[Crystalyzer]

Is the number in column B the result of a RAND() function? If so, why not simply sort the entire list on Col B Asc then Dec? This will give you exactly what you are looking for. If the value in column B is NOT generated from a RAND() function, then use a helper column in column M and insert the formula below and sort on column M.

=IF(C2<>"",RAND(),"")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ORIGINAL DATA
2	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
3	8		0.9768443		14	52		Greg	Jones				0
4	9				25	41		Bruce	Brown				0
5	10		0.0316331		19	47		Colin	White				0
6	11				26	40		Tony	Black				0
7	12		0.7737984		29	37		Roger	Colt				0
8	13		0.9954291		22	44		Joe	Cole				0
9	14		0.322505		23	43		Ron	George				0
10	15				19	47		Miles	Michaels				0
11	16				18	48		Len	Werth				0
12	17		0.1097377		11	55		Rick	Johnston				0
13
14
15	DATA WITH RAND() GENERATED NUMBERS IN COLUMN B
16	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
17	8		0.9768443		14	52		Greg	Jones				0
18	9				25	41		Bruce	Brown				0
19	10		0.0316331		19	47		Colin	White				0
20	11				26	40		Tony	Black				0
21	12		0.7737984		29	37		Roger	Colt				0
22	13		0.9954291		22	44		Joe	Cole				0
23	14		0.322505		23	43		Ron	George				0
24	15				19	47		Miles	Michaels				0
25	16				18	48		Len	Werth				0
26	17		0.1097377		11	55		Rick	Johnston				0
27
28	DATA WITH RAND() GENERATED NUMBERS IN COLUMN B SORTED ASCENDING
29	10		0.0316331		19	47		Colin	White				0
30	17		0.1097377		11	55		Rick	Johnston				0
31	14		0.322505		23	43		Ron	George				0
32	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
33	12		0.7737984		29	37		Roger	Colt				0
34	8		0.9768443		14	52		Greg	Jones				0
35	13		0.9954291		22	44		Joe	Cole				0
36	11				26	40		Tony	Black				0
37	15				19	47		Miles	Michaels				0
38	16				18	48		Len	Werth				0
39	9				25	41		Bruce	Brown				0
40
41	DATA WITH RAND() GENERATED NUMBERS IN COLUMN B SORTED DECENDING
42	13		0.9954291		22	44		Joe	Cole				0
43	8		0.9768443		14	52		Greg	Jones				0
44	12		0.7737984		29	37		Roger	Colt				0
45	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
46	14		0.322505		23	43		Ron	George				0
47	17		0.1097377		11	55		Rick	Johnston				0
48	10		0.0316331		19	47		Colin	White				0
49	9				25	41		Bruce	Brown				0
50	16				18	48		Len	Werth				0
51	15				19	47		Miles	Michaels				0
52	11				26	40		Tony	Black				0
Sheet1

 

[ockie]

Is the number in column B the result of a RAND() function? If so, why not simply sort the entire list on Col B Asc then Dec? This will give you exactly what you are looking for. If the value in column B is NOT generated from a RAND() function, then use a helper column in column M and insert the formula below and sort on column M.

=IF(C2<>"",RAND(),"")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ORIGINAL DATA
2	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
3	8		0.9768443		14	52		Greg	Jones				0
4	9				25	41		Bruce	Brown				0
5	10		0.0316331		19	47		Colin	White				0
6	11				26	40		Tony	Black				0
7	12		0.7737984		29	37		Roger	Colt				0
8	13		0.9954291		22	44		Joe	Cole				0
9	14		0.322505		23	43		Ron	George				0
10	15				19	47		Miles	Michaels				0
11	16				18	48		Len	Werth				0
12	17		0.1097377		11	55		Rick	Johnston				0
13
14
15	DATA WITH RAND() GENERATED NUMBERS IN COLUMN B
16	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
17	8		0.9768443		14	52		Greg	Jones				0
18	9				25	41		Bruce	Brown				0
19	10		0.0316331		19	47		Colin	White				0
20	11				26	40		Tony	Black				0
21	12		0.7737984		29	37		Roger	Colt				0
22	13		0.9954291		22	44		Joe	Cole				0
23	14		0.322505		23	43		Ron	George				0
24	15				19	47		Miles	Michaels				0
25	16				18	48		Len	Werth				0
26	17		0.1097377		11	55		Rick	Johnston				0
27
28	DATA WITH RAND() GENERATED NUMBERS IN COLUMN B SORTED ASCENDING
29	10		0.0316331		19	47		Colin	White				0
30	17		0.1097377		11	55		Rick	Johnston				0
31	14		0.322505		23	43		Ron	George				0
32	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
33	12		0.7737984		29	37		Roger	Colt				0
34	8		0.9768443		14	52		Greg	Jones				0
35	13		0.9954291		22	44		Joe	Cole				0
36	11				26	40		Tony	Black				0
37	15				19	47		Miles	Michaels				0
38	16				18	48		Len	Werth				0
39	9				25	41		Bruce	Brown				0
40
41	DATA WITH RAND() GENERATED NUMBERS IN COLUMN B SORTED DECENDING
42	13		0.9954291		22	44		Joe	Cole				0
43	8		0.9768443		14	52		Greg	Jones				0
44	12		0.7737984		29	37		Roger	Colt				0
45	7		0.6459567		21	45		Alan	Smith				=IF(H7+"","",K7+J7)
46	14		0.322505		23	43		Ron	George				0
47	17		0.1097377		11	55		Rick	Johnston				0
48	10		0.0316331		19	47		Colin	White				0
49	9				25	41		Bruce	Brown				0
50	16				18	48		Len	Werth				0
51	15				19	47		Miles	Michaels				0
52	11				26	40		Tony	Black				0
Sheet1

66	0.70665944​		26	40		Craig	Lawson				0
66	0.714311304​		19	47		Brett	Connelly				0
66	0.453310556​		22	44		Mitch	Smith				0
66	0.12439334​		20	46		Greg	Tuchin				0
66	0.870337551​		23	43		Tony	Geraghty				0
66	0.42094629​		66	0		Mick	Ney				0
66	0.433084413​		22	44		Ben	Foster				0
66	0.701829695​		26	40		Peter	Gormly				0
66	0.029189249​		-19	85		John	Kearns				0
66	0.422872325​		17	49		Dave	Pratt				0
66	0.748089569​		21	45		Rob	Eardley				0
66	0.044219276​		29	37		Ron	Scott				0
66	0.051547527​		21	45		Richard	Taylor				0
66	0.944398279​		29	37		Sean	Connolly				0
66	0.728972609​		21	45		Russell	Brinkworth				0
66	0.463699607​		27	39		Rory	Tayler				0
66			11	55		Ron	Johnston				0
66			14	52		Ian	Gibson				0
66			18	48		Ross	Dearden				0
66			19	47		Gary	Hoban				0
66			25	41		Garry	Hughes				0
66			33	33		Craig	Bradshaw				0

 

[ockie]

66	0.70665944​		26	40		Craig	Lawson				0
66	0.714311304​		19	47		Brett	Connelly				0
66	0.453310556​		22	44		Mitch	Smith				0
66	0.12439334​		20	46		Greg	Tuchin				0
66	0.870337551​		23	43		Tony	Geraghty				0
66	0.42094629​		66	0		Mick	Ney				0
66	0.433084413​		22	44		Ben	Foster				0
66	0.701829695​		26	40		Peter	Gormly				0
66	0.029189249​		-19	85		John	Kearns				0
66	0.422872325​		17	49		Dave	Pratt				0
66	0.748089569​		21	45		Rob	Eardley				0
66	0.044219276​		29	37		Ron	Scott				0
66	0.051547527​		21	45		Richard	Taylor				0
66	0.944398279​		29	37		Sean	Connolly				0
66	0.728972609​		21	45		Russell	Brinkworth				0
66	0.463699607​		27	39		Rory	Tayler				0
66			11	55		Ron	Johnston				0
66			14	52		Ian	Gibson				0
66			18	48		Ross	Dearden				0
66			19	47		Gary	Hoban				0
66			25	41		Garry	Hughes				0
66			33	33		Craig	Bradshaw				0

Hi Crystalyzr,

​

 

[Crystalyzer]

I don't understand what you are posting. do you have a question?

 

[ockie]

Hi Crystalyzer, the last one did not make sense as I accidentally hit post reply.

I tried your method and it is the same as what I have been trying.
the above image is when I manually deleted the rand() number in col B for the names I did not want random sorted( the bottom 6), I would like the names at the bottom to be directly below with no lines in between.

I had a formula in col B =if(h7="","",rand()) this made the cells seem empty in col B , this works until i manually delete a figure in col B (for the people I do not wish to random sort.

also a formula in col L =if(h7="","",k7+j7), which is needed.

I thought there may be an easy way to random sort after cells were manually deleted.

thanks

 

[ockie]

Hi Crystalyzer,
When i first tried the way you suggested I must have done it incorrectly.
I have just had another go at it, I took my time and followed exactly what you suggested, and it worked.

thank you for your help it is very much appreciated, especially for a novice such as myself.