Sort a list randomly

[BrSuthe]

Is there a simple way to rearrange a row of cells randomly (without repetition) using a formula preferably? and without sorting, filtering etc.

Basically what i need a formula to do something like this:

My sample data:

DATA                    RANDOMIZED          
1    2    3    4    ----->    2    4    3    1
5    6    7    8    ----->    6    5    8    7

Thanks.

I have Excel 2016.

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G	H	I
1	DATA					RANDOMIZED
2	1	2	3	4	----->	4	3	1	2
3	5	6	7	8	----->	8	6	7	5
Sheet1

Cell Formulas
Range	Formula
F2:I3	F2	=AGGREGATE(15,6,$A2:$D2/(COUNTIF($E2:E2,$A2:$D2)=0),RANDBETWEEN(1,COLUMNS(F2:$I2)))

 

[BrSuthe]

yes, it seems to do the trick. May I ask why does the formulas reshuffle every time I make a change in any cell of the sheet? is it possible to prevent this? thanks again.

 

[Phuoc]

Copy and Paste Values

 

[BrSuthe]

Thanks a lot for your help.