Shuffling the datas base on their columns and rows.

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
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-
name1abc1def1ghi110,000
name2abc2def2ghi220,000
name3abc3def3ghi330,000
name4abc4def4ghi440,000
name5abc5def5ghi550,000
name6abc6def6ghi660,000
name7abc7def7ghi770,000
name8abc8def8ghi880,000
name9abc9def9ghi990,000
name10abc10def10ghi10100,000
name11abc11def11ghi11110,000
name12abc12def12ghi12120,000
name13abc13def13ghi13130,000
name14abc14def14ghi14140,000
name15abc15def15ghi15150,000
name16abc16def16ghi16160,000
name17abc17def17ghi17170,000
name18abc18def18ghi18180,000
name19abc19def19ghi19190,000
name20abc20def20ghi20200,000

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



COLUMN-A-COLUMN-B-COLUMN-C-COLUMN-D-COLUMN-E-
name10abc10def13ghi11100,000
name12abc12def8ghi13120,000
name19abc19def2ghi1190,000
name9abc9def15ghi1990,000
name17abc17def4ghi10170,000
name1abc1def17ghi510,000
name11abc11def14ghi2110,000
name7abc7def18ghi970,000
name8abc8def10ghi680,000
name15abc15def12ghi15150,000
name4abc4def7ghi740,000
name18abc18def16ghi14180,000
name5abc5def6ghi1250,000
name3abc3def9ghi1730,000
name14abc14def11ghi4140,000
name6abc6def3ghi360,000
name20abc20def19ghi16200,000
name2abc2def20ghi2020,000
name16abc16def1ghi8160,000
name13abc13def5ghi18130,000

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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

Code:
[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

Board Regular
Joined
Dec 11, 2017
Messages
89
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
Assuming your data starts in cell A1 (change red highlighted addresses if necessary), the here is another macro that you can consider...
Code:
[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

Well-known Member
Joined
Jan 29, 2008
Messages
1,126

ADVERTISEMENT

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

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
If there is no header just change (in my code):

Header:=xlYes

to

Header:=xlNo
 
Last edited:

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
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...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,585
Messages
5,637,224
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top