1

I have an excel file consist of 1000x3 words. I wrote 5 rows here as an example.

 Target   choice1   choice2  choice3
 booklet  criquet   nutmeg   luciole
 argoman  border    phobie   liom
 mouche   libellule taplet   luciole
 abrot    guêpe     terrier  greeca

However I know currently all the words in choice2 are correct answer, I want to randomly mix the place of choice 1 and choice 2 and choice 3 using libra office such as below:

 Target   choice1   choice2     choice3
 booklet  nutmeg    criquet     luciole
 argoman  phobie     liom       border
 mouche   taplet     libellule  luciole
 abrot    terrier     guêpe     greeca

I am new to excel and I don't know how to work with it. Please advice me how to do it and in advance I appreciate all the comments.

Näzänin Mhd
  • 21
  • 4
  • See [Unique Random Numbers using VBA](http://stackoverflow.com/questions/18543169/unique-random-numbers-using-vba). –  Mar 14 '17 at 18:02
  • Do you want them to always change? For example, do you want to just scramble them randomly which means they might not actually move or do you want them to always change places? – user1274820 Mar 14 '17 at 18:53
  • No, actually I want to randomly change them once and use it after. I just don't want to hardcode them since the number of samples are too much (1000 samples). – Näzänin Mhd Mar 14 '17 at 18:55

1 Answers1

0

Sorry for the late reply.

You can use something like this:

Sub MixTheValues()
Dim x, y, r
Dim Words As New Collection
For x = 2 To Cells(Rows.CountLarge, "B").End(xlUp).Row
    For y = 2 To 4
        Words.Add (Cells(x, y).Value)
    Next y
    For y = 2 To 4
        r = Int(Words.Count * Rnd + 1)
        Cells(x, y).Value = Words(r)
        Words.Remove (r)
    Next y
Next x
End Sub

x starts on row 2 and goes to the last row.

y defines the columns you want to use (in this case, 2 to 4 or B to D

Note that this shuffles the answers, but because it is completely random, some of them may not move at all.

Results:

Results

user1274820
  • 7,786
  • 3
  • 37
  • 74