RaqForum 28 No.
1 Reply  •  370 View  • 
     ...

How to Shuffle Values Randomly in a Specific Area in Excel

 
excel(115) transpose(9)

We have Excel file book1.xlsx:

1,2

1,2

1,98

1,21

1,97

1,21

1,98

1,21

1,99

1,22

1,22

1,99

1,98

1,97

1,2

1,98

1,21

1,22

1,22

1,98

1,97

1,97

1,23

1,2

1,99

And we need to shuffle values in this 5*5 data area according to the following layout:

1,98

1,98

1,97

1,23

1,99

1,98

1,21

1,2

1,99

1,98

1,22

1,22

1,22

1,99

1,21

1,2

1,2

1,97

1,2

1,22

1,98

1,97

1,97

1,21

1,21

It would be easy to get this done with esProc.
Download esProc installation package and free DSK edition license HERE.

1.        Write script p1.dfx in esProc:

A

1

=file("book1.xlsx").xlsimport@w()

2

=A1.conj(~).sort(rand()).group(#%5)

A1   Import data of book1.xlsx; @w option enables reading data as a sequence of sequences.

A2   Concatenate member sequences of A1’s sequence into a single one, shuffle members, and then group the sequence according to the member sequences in the original sequence.

2.     Execute script to get the desired result in A2.

To shuffle members within each row, we can use the following script:

A

1

=file("book1.xlsx").xlsimport@w()

2

=A1.(~.sort(rand()))

A1   Import data of book1.xlsx; @w option enables reading data as a sequence of sequences.

A2   Shuffle members of each sub-sequence.

To shuffle members within each column, we can use the following script:

A

1

=file("book1.xlsx").xlsimport@w()

2

=transpose(A1).(~.sort(rand()))

3

=transpose(A2)

A1   Import data of book1.xlsx; @w option enables reading data as a sequence of sequences.

A2  Perform row-to-column transposition and shuffle members of each sub-sequence.

A3   Perform row-to-column transposition on A2.


excel(115) transpose(9)
 
 •  370 View  •