randomizing multiple columns

[andrewb90]

Hello all,

I have this code that I use to randomize cell contents. So I can take the values of A1:A10 and rearrange the order randomly. My problem is, if in B1:B10 I have values that correspond to the values on Column A, they either don't shuffle, or they are included in the shuffle, but they don't match side by side

To clarify. If A1 = Apple, B1 = Red. After the shuffle, Apple can be in A4, but then B4 needs to be Red. (It needs to move with it, not shuffle independently.

Here's my code:

Sub rdmA()Columns("C:X").Hidden = False
Dim a, y, c As Long
Dim j As Long, x As Long, lr As Long


Randomize
For c = 4 To 24   'choose what columns# to shuffle
'lr = 3 'number of rows to shuffle
lr = Cells(Rows.Count, c).End(xlUp).Row   'till end of data
a = Cells(c).Resize(lr)


For j = 14 To lr 'starting row #
    x = Application.RandBetween(j, lr)
    y = a(j, 1)
    a(j, 1) = a(x, 1)
    a(x, 1) = y
Next j


Cells(c).Resize(lr) = a
Next c
End Sub

Any insight would be greatly appreciated.

 

[Rick Rothstein]

If you have less than 65,536 rows of data, then this macro will randomize the rows the way you want quite quickly. On my computer, it was able to randomize 65535 rows by 26 columns in under 5 seconds.

[table="width: 500"]
[tr]
	[td]Sub RearrangeColumns()
  Dim X As Long, LastRow As Long, LastCol As Long, Cnt As Long, Index As Long, Tmp As Long
  Dim Letters As Variant, NewLetters As Variant, RowOrder As Variant
  Randomize
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  RowOrder = Evaluate("TRANSPOSE(ROW(1:" & LastRow & "))")
  For Cnt = UBound(RowOrder) To 1 Step -1
    Index = Int(Cnt * Rnd + 1)
    Tmp = RowOrder(Index)
    RowOrder(Index) = RowOrder(Cnt)
    RowOrder(Cnt) = Tmp
  Next
  Range("A1").Resize(LastRow, LastCol) = Application.Index(Cells, Application.Transpose(RowOrder), Join(Evaluate("COLUMN(A1:" & Cells(1, LastCol).Address & ")")))
End Sub[/td]
[/tr]
[/table]

 

[andrewb90]

I put values in A1:B4 and they all turned to #Value .

 

[Rick Rothstein]

I put values in A1:B4 and they all turned to #Value .

Sorry, posted the wrong version...

[table="width: 500"]
[tr]
	[td]Sub RearrangeColumns()
  Dim X As Long, LastRow As Long, LastCol As Long, Cnt As Long, Index As Long, Tmp As Long
  Dim Letters As Variant, NewLetters As Variant, RowOrder As Variant
  Randomize
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  RowOrder = Evaluate("TRANSPOSE(ROW(1:" & LastRow & "))")
  For Cnt = UBound(RowOrder) To 1 Step -1
    Index = Int(Cnt * Rnd + 1)
    Tmp = RowOrder(Index)
    RowOrder(Index) = RowOrder(Cnt)
    RowOrder(Cnt) = Tmp
  Next
  Range("A1").Resize(LastRow, LastCol) = Application.Index(Cells, Application.Transpose(RowOrder), Evaluate("COLUMN(A1:" & Cells(1, LastCol).Address & ")"))
End Sub[/td]
[/tr]
[/table]

 

[andrewb90]

Ah! that works really well! Thank you! Now, I'm assuming If I change A1 to say D4, then D4 and down and Column E would be shuffled? Scratch that, I tried it, and it made some weird things happen. How would I specify a particular set of columns or starting rows?

 

[Rick Rothstein]

Ah! that works really well! Thank you! Now, I'm assuming If I change A1 to say D4, then D4 and down and Column E would be shuffled? Scratch that, I tried it, and it made some weird things happen. How would I specify a particular set of columns or starting rows?

I am not clear on what you are asking here. Are you saying you want to be able to randomly shuffle the rows of a subset of your complete data? If so, you do realize the columns you do not include will get out of sync with the columns you randomize (which is not compliant with what you said you wanted in your original message), correct? If not, then are you saying your data starts at cell D4, not A1? Or are you asking to be able to select a subrange (possibly not including rows above and below and columns before and after) of your data? If so, how did you want to be able to specify your range (select it or change the code directly)? Please clarify.

 

[andrewb90]

So with this code I need to be able to select what column is being shuffled, and what row # its starting at.
If my data is in C4:C8, then the matching data is in D4:D8, when the code runs, the four rows are shuffled, but the data in column D still matches the original items in C.

Your code works now, but only if my data starts in A1.
Before code
C D
4 Apple Red
5 Pear Green
6 Kiwi Brown
7 Banana Yellow
8 Blueberry Blue
after code

C D
4 Blueberry Blue
5 Apple Red
6 Kiwi Brown
7 Pear Green
8 Banana Yellow

 

[Rick Rothstein]

So with this code I need to be able to select what column is being shuffled, and what row # its starting at.

Will you have data in Columns A:B and/or Rows 1:3 for the scenario you mentioned in Message #7 ? What I am trying to lock down from you is do you want the code to automatically find the filled cells and then randomize their rows? Or are you wanting to specify a subrange of your data and only randomize the rows of that subrange (leaving data in the cells outside of the subrange untouched)? In other words, what I am trying to find out is if you want all of your data randomized by row and it is just that your data does not start in cell A1 or whether your data does start in cell A1 but you want to randomize the rows of a range starting in a different cell than A1?

 

[andrewb90]

I see, I would like to specify a specific subrange within the code to randomize and ignore any other data outside that range.

Ideally, I could alternate between both:

[COLOR=#333333]'lr = 3 'number of rows to shuffle
[/COLOR][COLOR=#333333]lr = Cells(Rows.Count, c).End(xlUp).Row   'till end of data[/COLOR]

In my original code, I could choose the number of rows to shuffle, or just do until the data ends, depending on what my specific need was.

 

[Rick Rothstein]

I see, I would like to specify a specific subrange within the code to randomize and ignore any other data outside that range.

See if this macro does what you want. To use it, just change the red highlighted range to the range whose rows you want to randomize...

[table="width: 500"]
[tr]
	[td]Sub RearrangeColumns()
  Dim X As Long, Cnt As Long, Index As Long, Tmp As Long, Rng As Range
  Dim Letters As Variant, NewLetters As Variant, RowOrder As Variant
  Set Rng = Range("[B][COLOR="#FF0000"][SIZE=3]C5:J19[/SIZE][/COLOR][/B]")
  Randomize
  RowOrder = Evaluate("TRANSPOSE(ROW(" & Rng(1).Resize(Rng.Rows.Count).Address & "))")
  For Cnt = UBound(RowOrder) To 1 Step -1
    Index = Int(Cnt * Rnd + 1)
    Tmp = RowOrder(Index)
    RowOrder(Index) = RowOrder(Cnt)
    RowOrder(Cnt) = Tmp
  Next
  Rng = Application.Index(Cells, Application.Transpose(RowOrder), Evaluate("COLUMN(" & Rng(1).Resize(, Rng.Columns.Count).Address & ")"))
End Sub[/td]
[/tr]
[/table]