# Thread: how to randomise a row of data in excel (excel 2003)

1. ## how to randomise a row of data in excel (excel 2003)

hi -I wanted to know if this was possible - I'm conductinmg an experiment where i get rats to smell different concentrations of odours. There are 6 concentrations ranging from 2.0 - 0,2M - my problem is that i wish to randmise the order they recive the treatement - I know how to randomise the order of a colulm (rand and sort by rand) but i can't think of how this can be done for a row of data. basicaaly i want to randmoise

2.0, 1.64, 1.26, 0.92, 0.56 , 0.2 - within a row It would be great to be able to do this over multiple rows - so each rows has the same set of numbers (none replcated) in a random order.

Can anyone help?

beeman

2. ## Re: how to randomise a row of data in excel (excel

Hi beeman

Welcome to the Lounge

I have had a little play and this seems to work.

1) Select all the table of data
2) Edit | Copy
3) Select Empty Cell
4) Edit | Paste Special
5) Transpose | OK

You now have all your column headers as row headers and you can do your random sort from there. It is more a practical answer than a code answer.

Would that work with your dataset as I have done it with a very basic 4 x 4 data table?

3. ## Re: how to randomise a row of data in excel (excel

thankyou - worked well. - if you have a coding solution to it then i would like to hear it - but this is good for now. My ideal solution would be to have a code that would pick 1 out of the 6 numbers and put it in a cell in the row, the next cell would then be filled with one of the remaining numbers - i have no clue about how this would be done.

But thankyou very much for this solution

beeman - now to get back to those rats! I ussually work with bees - hence the name - and i'm doing a comparitive study on the ability to detect low concentrations of odours between rats and bees, So far bees are winning - which you might not expect.

4. ## Re: how to randomise a row of data in excel (excel

Hi There

Not the most eloquent of pieces of code but seems to work.

Put this code into a Module and run from your macro menu ( If you need help on this just get back and I can talk you through it)

Sub TransposeData()

Dim lngRow As Long
Dim lngCol As Long
Dim i As Long

Application.ScreenUpdating = False
lngCol = ActiveSheet.UsedRange.Columns.Count
lngRow = ActiveSheet.UsedRange.Rows.Count

Range(Cells(1, 1), Cells(lngRow, lngCol)).Select

Application.CutCopyMode = False
Selection.Copy

Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Cells(1, lngRow + 1).Select
ActiveCell.Value = "temp"

ActiveCell.Offset(1, 0).Select

For i = 1 To lngCol - 1

ActiveCell.Value = Rnd(1)

ActiveCell.Offset(1, 0).Select

Next i

Range(Cells(1, 1), Cells(lngCol, lngRow + 1)).Select
Application.CutCopyMode = False
Range(Cells(1, 1), Cells(lngCol, lngRow + 1)).Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Columns(lngRow + 1).Delete

Range("A1").Select

End Sub

5. ## Re: how to randomise a row of data in excel (excel

sorry - i really don't know about how to do this! but very willing to try if you can spare the time and effort

1. How do i put this code into a module? What is a module

If this is too much effort - don't worry - your ealier suggestioin will work fine

beeman

6. ## Re: how to randomise a row of data in excel (excel

No Problems

1) Open up your excel workbook
2) Press Alt + F11 to open the VB Editor

On the left hand side of the resulting window

3) Right hand mouse click Microsoft Excel Object
4) Insert | Module
5) Paste my code into the resulting window

Close the editor down and you return to the Excel workbook. Now go to

6) Tools | Macro | Macros
7) Click TransposeData and OK

Make sure you are on sheet 1 of your workbook and the data set starts in cell A1 and sheet 2 is clear.

If you like attach your workbook to a reply and I will have a look and adapt the code for you. I have attached my working copy, note sheet 2 is clear and the structure of the data table in sheet 1. Run the macro as per #6 and #7 above

7. ## Re: how to randomise a row of data in excel (excel

thanks for this Jerry- it works perfectly. Very well described too.

Beeman

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•