Randomly mixing datas base on same columns..

[asyamonique]

Good Day,
I have a data table like below starts from A2 to C19
Is it possible to ruffle those datas randomly by clicking a command button?
Every column will remain with same datas ony their rows will be changed...
Thank you...


[TABLE="width: 213"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]NAME1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NAME2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]NAME3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NAME4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]NAME5[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NAME6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]NAME7[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NAME8[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]NAME9[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NAME10[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]NAME11[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NAME12[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]NAME13[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NAME14[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]NAME15[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]NAME16[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]NAME17[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NAME18[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[BobUmlas]

Have the macro put =RAND() in D1:D19, sort all by column D, then clear Column D.

 

[Akuini]

Good Day,
I have a data table like below starts from A2 to C19
Is it possible to ruffle those datas randomly by clicking a command button?
Every column will remain with same datas ony their rows will be changed...

Try this:
I'm using column J as a temporary helper column, you may change that to suit

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1083594a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1083594-randomly-mixing-datas-base-same-columns.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], va
Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row - [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]ReDim[/COLOR] va([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] n, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] n
    Randomize
    va(i, [COLOR=crimson]1[/COLOR]) = WorksheetFunction.RandBetween([COLOR=crimson]0[/COLOR], n)
    [COLOR=Royalblue]Next[/COLOR]

Col = [COLOR=crimson]10[/COLOR] [I][COLOR=seagreen]'column J as helper column, change to suit[/COLOR][/I]

Cells([COLOR=crimson]2[/COLOR], Col).Resize(n, [COLOR=crimson]1[/COLOR]) = va
Range(Cells([COLOR=crimson]2[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(n + [COLOR=crimson]1[/COLOR], Col)).Sort key1:=Cells([COLOR=crimson]2[/COLOR], Col), order1:=xlAscending, Header:=xlNo
Cells([COLOR=crimson]2[/COLOR], Col).Resize(n, [COLOR=crimson]1[/COLOR]).ClearContents

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[asyamonique]

Thanks a lot

 

[asyamonique]

Hello again,
I did some alteration to start from A1, and sort..

Private Sub CommandButton1_Click()

Dim n As Long, i As Long, va
Application.ScreenUpdating = False
n = Range("A" & Rows.Count).End(xlUp).Row - 1
ReDim va(1 To n, 1 To 1)
    For i = 1 To n
    Randomize
    va(i, 1) = WorksheetFunction.RandBetween(0, n)
    Next


Col = 10 'column J as helper column, change to suit


Cells(1, Col).Resize(n, 1) = va
Range(Cells(1, "A"), Cells(n + 1, Col)).Sort key1:=Cells(1, Col), order1:=xlAscending, Header:=xlNo
Cells(1, Col).Resize(n, 1).ClearContents




'sorting
    Columns("A:D").Select
    ActiveWorkbook.Worksheets("tables").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("tables").Sort.SortFields.Add Key:=Range("C1:C30") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("tables").Sort
        .SetRange Range("A1:D30")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
Application.ScreenUpdating = True
End Sub

Could you please let me know if the row amount increase like C50 where to I change from the code?
Thanks again.