How to randomize text string data 2

[philoshophy]

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

 

[MintJulep]

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.

 

[HgTX]

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

 

[Denial]

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.

 

[davidbeach]

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

=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.

 

[philoshophy]

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

 

[handleman]

I have not tried this add-in, but it sounds like it should do something like what you want:

http://office.microsoft.com/en-us/marketplace/EM102159811033.aspx

-handleman, CSWP (The new, easy test)

 

[IRstuff]

I thought the OP want to randomize the position of existing strings, not create strings with random content.

TTFN

FAQ731-376

 

[IRstuff]

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

 

[electricpete]

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.

 

[electricpete]

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.

 

[IRstuff]

The code appears to be pretty straightforward, sort of ;-) 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

 

[electricpete]

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.