×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to randomize text string data
2

How to randomize text string data

How to randomize text string data

(OP)
Hi
Dear all

Anyone knows how to randomize the text data in string cell say I have a data like this

      A    B    C   D
1     XA   NA   PU  MN
2     AX   LN   TI  ND
3     EF   PN   UV  OX
4     JX   KN   JY  SL
.
.
1200  HN    SY  BY  WX


I want to ranomize this data in both rowwise and columnwise.
If there any simple trick, this will be great for me

Thanks
   

RE: How to randomize text string data

It would be relatively easy to populate a 1200 x 4 table with values picked at random from your existing 1200 x 4 data table - giving no regard to attempting use each value once and only once.  This could be done with native formulas.

It would be harder do randomize the list while ensuring that each value is used once and only once.  This would almost certainly require VBA.

RE: How to randomize text string data

Will it work to add a row & column, populate those with random numbers, and then sort on that row & column (transposing to sort on the row)? Or do you need something more automated?

Hg

Eng-Tips policies:  FAQ731-376: Eng-Tips.com Forum Policies

RE: How to randomize text string data

I would try something along the following lines.

(1) Copy your 1200x4 items into a 4800x1 column somewhere else on your worksheet.
(2) Immediately to the right of this new column put a 4800x1 column of =RAND() entries.
(3) Hit the calc key (F9) to regenerate a new set of random numbers.
(4) Sort this 4800x2 range based on the column of random numbers.
(5) Copy the newly sorted 4800x1 column back over the top of your original 1200x4 range.

That approach would work fine if you only want to do this once.  If you need to do it multiple times, you could make some refinements to the approach.  Among other things, you would have to modify step 5 to create a new 1200x4 range for the randomised version, leaving the original table unchanged.  Then create a keystroke-recorded macro to cover the recalc and sort operations.

If you want to go one step further and introduce flexibility in terms of how long your data table is (ie strating with a table 1300x4 or 2199x4 or ...) it becomes more complicated again, but I think it could probably be achieved within a reasonably simple macro.
 

RE: How to randomize text string data

Do you have 4800 specific two letter codes or do you just need a random collection of two letter codes?  After all, there are only 676 different two letter codes.  If all you need are random two letter codes, put

CODE

=CHAR(RAND()*(90-65)+65)&CHAR(RAND()*(90-65)+65)
in each cell of your array.  Every time the worksheet recalculates you will have a new array of random two letter codes.  If you need to have it more stable than that, make two array areas, put the formulas into one and then copy it and PASTE SPECIAL|VALUES into the working array.

RE: How to randomize text string data

(OP)
hi davidbeach
Actually the word is not of two letters, It depends on letter so cannto say how many letters. If so what happens. Could you suggest me.
Thanks
 

RE: How to randomize text string data

It might help if you explained the intent of this exercise.  There may be other solutions that are not obvious because the problem is overly constrained at this time.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: How to randomize text string data

See the file below:

http://home.comcast.net/~electricpete/eng-tips/RandomizeString.xls

Just select a range of interest, push the button (or select the macro randomize), and the entries of those cells are shuffled.

This was copied in part from "Excel For Scientists and Engineers" by Joseph Billo

The sort routine (sort based on random number of course) is very primitive and will likely bog down for very large lists.  If that becomes a problem, let me know. I can change it to do the sorting in another spreadhsett using excels sort functio which is much faster.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: How to randomize text string data

Pearson just added a new item to his site to accomlish the task of "shuffling of an array".  Seems like it could be easily adapted to a range (if nothing else, load the range into an array, shuffle it, and output it to a range).

It deoes not use any sorting, so I'm sure it would be much faster.  I haven't studied the logic well enough to understand whether it will truly shuffle, while avoiding omissions and repeats.

http://www.cpearson.com/excel/MainPage.aspx
http://www.cpearson.com/excel/ShuffleArray.aspx

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: How to randomize text string data

The code appears to be pretty straightforward, sort of winky smile being the inverse of a standard sorting algorithm.  

You loop through the array once, attempting to swap each element in passing with another random element.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: How to randomize text string data

You're right. It's pretty simple and looks like it will get the job done with a minimum of computation.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources