Problem:- How to randomize the given set of data in an Excel?

Solution: – We would try to shuffle the rows of data in a given excel. We would try it manually first followed by code written in Vb-script.

Step1:- Add column(say Random) in the starting of the excel

Step2:- Apply formula to the first cell (=Rand ()) and drag it across all the cells of the same column in the sheet.

Step3:- Apply on the Filter on the first row of the excel (Data – > Filter)

Step4:- Now sort the Column one data in ascending order .All set we have our randomize data in the end.

We would use Vbscript code to automate the randomization of excel data. We would replicate the above manual steps by using coding.Please refer our tutorial on Excel automation to understand the below code.

Note:-

Please do the following changes to run the below code:-

Solution: – We would try to shuffle the rows of data in a given excel. We would try it manually first followed by code written in Vb-script.

**Manual Approach:-**Step1:- Add column(say Random) in the starting of the excel

Step2:- Apply formula to the first cell (=Rand ()) and drag it across all the cells of the same column in the sheet.

Step3:- Apply on the Filter on the first row of the excel (Data – > Filter)

Step4:- Now sort the Column one data in ascending order .All set we have our randomize data in the end.

**Automation Approach:-**We would use Vbscript code to automate the randomization of excel data. We would replicate the above manual steps by using coding.Please refer our tutorial on Excel automation to understand the below code.

Note:-

Please do the following changes to run the below code:-

- sFileName = Change it to Your file that need to be randomized
- Change the sheetname in the code line
*Set oReadSheet = oReadWB.Worksheets(“Sample”)*

'Function Call to Randomize data in Excel

fn_Randomize

'*******************************************Function*********************************

'Function Name:- fn_Randomize

'Function Description:- Function to shuffle Excel data

'Input Parameters:- N/A

'Output Parameters:- N/A

'************************************************************************************

Sub fn_Randomize()

'FileName of the Excel

Dim sFileName

sFileName = "D:AutomateDataSheetRandomTest.xlsx"

'Creating the Excel Object

Set oExcel = CreateObject("Excel.Application")

'Making Excel Visible

oExcel.Visible = True

'Creating the Workbooks object

Set oReadWB = oExcel.Workbooks.Open(sFileName)

'Creating the Read sheet object

Set oReadSheet = oReadWB.Worksheets("Sample")

'Inserting 'Random' column as our first column in the sheet

oReadSheet.Columns(1).Insert

oReadSheet.Range("A1").Value = "Random"

'Running the loop till for all the rows of excel which have data

For iRow = 2 To oReadSheet.UsedRange.Rows.Count

'Applying Random formula on the first column of the cell

oReadSheet.Cells(iRow, 1) = "=Rand()"

'Copying only the value and removing the formula from the cell

'Try commenting the below line of code and see the cell still contains "=Rand"

oReadSheet.Cells(iRow, 1).Value = oReadSheet.Cells(iRow, 1).Value

Next

'Now sorting the Data

'Clearing any formuales on the Temp sheet

oReadSheet.Sort.SortFields.Clear

'Sorting on the basis of Colunm =1 in the asencding order

Call oReadSheet.Sort.SortFields.Add(oReadSheet.Columns(1), , xlAscending)

'Giving the range to be sorted(till the end of the sheet)

'Start cell is A2 as we have header on the Top

'End of sheet is taken as XFD1048576 as per Excel-2007

Call oReadSheet.Sort.SetRange(oReadSheet.Range("A2:XFD1048576"))

'Applying the sort

oReadSheet.Sort.Apply

MsgBox "Data is Randomized..Cheers!!"

Set oExcel = Nothing

End Sub