Shuffling the datas base on their columns and rows.

[asyamonique]

Hello,
Please help me with given sample tables below.
First table shows the original datas...
Second table is the results what I wanted to get by clicking the button all datas will randomly changed with below conditions.
1. Column A and Column B will be related datas.(If A1 goes to another row, B1 will follow that..)
2. Column C and Column D will not be related datas they will be shuffled in same columns!
3. Column E will be related with column A datas.

Many Thanks

COLUMN-A-	COLUMN-B-	COLUMN-C-	COLUMN-D-	COLUMN-E-
name1	abc1	def1	ghi1	10,000
name2	abc2	def2	ghi2	20,000
name3	abc3	def3	ghi3	30,000
name4	abc4	def4	ghi4	40,000
name5	abc5	def5	ghi5	50,000
name6	abc6	def6	ghi6	60,000
name7	abc7	def7	ghi7	70,000
name8	abc8	def8	ghi8	80,000
name9	abc9	def9	ghi9	90,000
name10	abc10	def10	ghi10	100,000
name11	abc11	def11	ghi11	110,000
name12	abc12	def12	ghi12	120,000
name13	abc13	def13	ghi13	130,000
name14	abc14	def14	ghi14	140,000
name15	abc15	def15	ghi15	150,000
name16	abc16	def16	ghi16	160,000
name17	abc17	def17	ghi17	170,000
name18	abc18	def18	ghi18	180,000
name19	abc19	def19	ghi19	190,000
name20	abc20	def20	ghi20	200,000

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

[TABLE="width: 401"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]COLUMN-A-[/TD]
[TD]COLUMN-B-[/TD]
[TD]COLUMN-C-[/TD]
[TD]COLUMN-D-[/TD]
[TD]COLUMN-E-[/TD]
[/TR]
[TR]
[TD]name10[/TD]
[TD]abc10[/TD]
[TD]def13[/TD]
[TD]ghi11[/TD]
[TD]100,000[/TD]
[/TR]
[TR]
[TD]name12[/TD]
[TD]abc12[/TD]
[TD]def8[/TD]
[TD]ghi13[/TD]
[TD]120,000[/TD]
[/TR]
[TR]
[TD]name19[/TD]
[TD]abc19[/TD]
[TD]def2[/TD]
[TD]ghi1[/TD]
[TD]190,000[/TD]
[/TR]
[TR]
[TD]name9[/TD]
[TD]abc9[/TD]
[TD]def15[/TD]
[TD]ghi19[/TD]
[TD]90,000[/TD]
[/TR]
[TR]
[TD]name17[/TD]
[TD]abc17[/TD]
[TD]def4[/TD]
[TD]ghi10[/TD]
[TD]170,000[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD]abc1[/TD]
[TD]def17[/TD]
[TD]ghi5[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]name11[/TD]
[TD]abc11[/TD]
[TD]def14[/TD]
[TD]ghi2[/TD]
[TD]110,000[/TD]
[/TR]
[TR]
[TD]name7[/TD]
[TD]abc7[/TD]
[TD]def18[/TD]
[TD]ghi9[/TD]
[TD]70,000[/TD]
[/TR]
[TR]
[TD]name8[/TD]
[TD]abc8[/TD]
[TD]def10[/TD]
[TD]ghi6[/TD]
[TD]80,000[/TD]
[/TR]
[TR]
[TD]name15[/TD]
[TD]abc15[/TD]
[TD]def12[/TD]
[TD]ghi15[/TD]
[TD]150,000[/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD]abc4[/TD]
[TD]def7[/TD]
[TD]ghi7[/TD]
[TD]40,000[/TD]
[/TR]
[TR]
[TD]name18[/TD]
[TD]abc18[/TD]
[TD]def16[/TD]
[TD]ghi14[/TD]
[TD]180,000[/TD]
[/TR]
[TR]
[TD]name5[/TD]
[TD]abc5[/TD]
[TD]def6[/TD]
[TD]ghi12[/TD]
[TD]50,000[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD]abc3[/TD]
[TD]def9[/TD]
[TD]ghi17[/TD]
[TD]30,000[/TD]
[/TR]
[TR]
[TD]name14[/TD]
[TD]abc14[/TD]
[TD]def11[/TD]
[TD]ghi4[/TD]
[TD]140,000[/TD]
[/TR]
[TR]
[TD]name6[/TD]
[TD]abc6[/TD]
[TD]def3[/TD]
[TD]ghi3[/TD]
[TD]60,000[/TD]
[/TR]
[TR]
[TD]name20[/TD]
[TD]abc20[/TD]
[TD]def19[/TD]
[TD]ghi16[/TD]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD]abc2[/TD]
[TD]def20[/TD]
[TD]ghi20[/TD]
[TD]20,000[/TD]
[/TR]
[TR]
[TD]name16[/TD]
[TD]abc16[/TD]
[TD]def1[/TD]
[TD]ghi8[/TD]
[TD]160,000[/TD]
[/TR]
[TR]
[TD]name13[/TD]
[TD]abc13[/TD]
[TD]def5[/TD]
[TD]ghi18[/TD]
[TD]130,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Ok, try this
Run "Sub a1083990a()" only
Note:
- I use column G as temporary helper column, change to suit.
- I assumed there is header at row 1

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1083990a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1083990-shuffling-datas-base-their-columns-rows.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], col [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range, rgA [COLOR=Royalblue]As[/COLOR] Range, rgC [COLOR=Royalblue]As[/COLOR] Range, rgD [COLOR=Royalblue]As[/COLOR] Range, rgX [COLOR=Royalblue]As[/COLOR] Range

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
col = [COLOR=crimson]7[/COLOR] [I][COLOR=seagreen]'column G as temporary helper column, change to suit[/COLOR][/I]

[COLOR=Royalblue]Set[/COLOR] rgA = Range([COLOR=brown]"A1:E"[/COLOR] & n)
[COLOR=Royalblue]Set[/COLOR] rgC = Range([COLOR=brown]"C1:C"[/COLOR] & n)
[COLOR=Royalblue]Set[/COLOR] rgD = Range([COLOR=brown]"D1:D"[/COLOR] & n)
[COLOR=Royalblue]Set[/COLOR] rgX = Range(Cells([COLOR=crimson]1[/COLOR], col), Cells(n, col))
vc = rgC: vd = rgD

[COLOR=Royalblue]Call[/COLOR] toRandom(rgX, n, col)
    va = rgA: rgC = vc
[COLOR=Royalblue]Call[/COLOR] toRandom(rgX, n, col)
    vc = rgC: rgD = vd
[COLOR=Royalblue]Call[/COLOR] toRandom(rgX, n, col)
    vd = rgD: rgA = va: rgC = vc: rgD = vd

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Sub[/COLOR] toRandom(rgX [COLOR=Royalblue]As[/COLOR] Range, n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], col [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR])
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range

    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] rgX
    Randomize
    r = WorksheetFunction.RandBetween([COLOR=crimson]0[/COLOR], n)
    [COLOR=Royalblue]Next[/COLOR]

Range(Cells([COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(n, col)).Sort key1:=Cells([COLOR=crimson]1[/COLOR], col), order1:=xlAscending, Header:=xlYes
Cells([COLOR=crimson]1[/COLOR], col).Resize(n, [COLOR=crimson]1[/COLOR]).ClearContents

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[The_Macrotect]

Assuming your worksheet is set up like your example, this should do the trick:

Sub Randomize()
Dim lastrow As Integer, x As Integer
Application.ScreenUpdating = False

Columns("A:A").Insert shift:=xlToRight 'creates helper column
lastrow = ActiveSheet.UsedRange.Rows.Count 'finds the last used row in the sheet

For x = 1 To lastrow 'populates the helper column with random numbers
Cells(x, 1).Formula = "=rand()*1000"
Next x

Range("A1:F" & lastrow).Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlNo 'sorts data in columns A - E randomly

Range("A1:A" & lastrow).Copy 'creates a new helper column and populates it with random numbers by copying the formulas from the last helper column
Columns("D:D").Insert shift:=xlToRight
Range("D1:D" & lastrow).PasteSpecial
Columns("A").Delete 'deletes original helper column

Range("C1:D" & lastrow).Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlNo ' sorts data in column C randomly

Range("C1:C" & lastrow).Copy 'creates a new helper column and populates it with random numbers by copying the formulas from the last helper column
Columns("E:E").Insert shift:=xlToRight
Range("E1:E" & lastrow).PasteSpecial
Columns("C").Delete 'deletes second helper column

Range("D1:E" & lastrow).Sort key1:=Range("D1"), order1:=xlAscending, Header:=xlNo 'sorts data in column D randomly

Columns("D").Delete 'deletes final helper column

Application.ScreenUpdating = True 'refreshes screen
End Sub

 

[Rick Rothstein]

Assuming your data starts in cell A1 (change red highlighted addresses if necessary), the here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub ShuffleDataRows()
  Dim R As Long, C As Long, RandomIndex As Long, Data As Variant, Result As Variant
  Randomize
  Data = Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]").CurrentRegion.Value
  ReDim Result(1 To UBound(Data, 1), 1 To UBound(Data, 2))
  For R = UBound(Data, 1) To 1 Step -1
    RandomIndex = Int(R * Rnd + 1)
    For C = 1 To UBound(Data, 2)
      Result(R, C) = Data(RandomIndex, C)
      Data(RandomIndex, C) = Data(R, C)
    Next
  Next
  Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]").CurrentRegion = Result
End Sub[/td]
[/tr]
[/table]

 

[asyamonique]

Hello Again,
So far I need to use the Macrotect's code cos all my data's starts from A1 cell.
Cheers to all.

 

[Rick Rothstein]

So far I need to use the Macrotect's code cos all my data's starts from A1 cell.

My code assumes your data starts from cell A1 also.

 

[asyamonique]

Hi,
Yes but column D and E wasn't changed unlike my sample table...
I will double check..
By the way I'm trying to find the logic if I do that only in one column to circle each-other..
Thanks

 

[Rick Rothstein]

Hi,
Yes but column D and E wasn't changed unlike my sample table...

The only way that would/could happen is if you had a blank column before them which, from your example data was not the case.

By the way I'm trying to find the logic if I do that only in one column to circle each-other..

I am not sure what you mean by "to circle each-other"?

 

[Akuini]

If there is no header just change (in my code):

Header:=xlYes

to

Header:=xlNo

 

[asyamonique]

Thanks Akuini,
Rick yes you are right.
Also I can use your code even if there is only data's at columns A & B only ..
Is it possible to loop that code like when click it will mix the datas 3 times in one cilick!
I will use that option for lucky draw.The project is on userform and data's shown by labels.
So when I click the button if those data's ruffles 3 times in one click it will be great...