Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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?
    Jerry

  3. #3
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  5. #5
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  7. #7
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •