scrambling cells row wise in excel!!! 1

[compudevil]

Hello FRIENDS,

When using =rand() formula, it will scramble the cells column wise. Is there any other way where we can jumble up all the cells row wise.
Basically my all numbers are dragged so I dont want them to look in series, so I want to jumble up all the cells ups and downs

Can anyone help how to make to look all the series numbers jumbled,...

Thanks in Advance,

Regards,

 

[GlennUK]

Please explain further ...

When using =rand() formula, it will scramble the cells column wise

???

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[AnotherHiggins]

I assume this is related to thread68-1205981, right?

You can use the exact same procedure you did for columns. Put =RAND() in a different row (instead of column, as I stated in the other thread), then select the two rows to be sorted and go to Data > Sort > Options. Then under the Orientation section, choose Sort left to right.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[compudevil]

Hello Friends,

I tried =Rand() which helps me doing the rows but It will do only for 3 rows maximum.

Imagine I have 25000 numbers in excel which got 5000rows and 5 columns. now I want to jumble in such a way that no one should recognise that the numbers are dragged in series.

For eg :

7988670105 7987670105 7986670105
7988670106 7987670106 7986670106
7988670107 7987670107 7986670107
7988670108 7987670108 7986670108
7988670109 7987670109 7986670109
7988670110 7987670110 7986670110
7988670111 7987670111 7986670111
7988670112 7987670112 7986670112
7988670113 7987670113 7986670113
7988670114 7987670114 7986670114

Can we jumble these no's right left ups and downs. can we put say A1 in C3 and C2 in B4 bla bla bla,...

Please Help,

Thanks,,.

 

[GlennUK]

Please explain:

I tried =Rand() which helps me doing the rows but It will do only for 3 rows maximum.

... what are you doing exactly? I can't help you improve on a method you are using if you don't tell me what it is!!!!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[compudevil]

Well,

I want to create millions of numbers(numbers as mentioned above) and wants to write it on a CD. When that datas is opened again then no one should recognise that the numbers are dragged and are in series one after the other. So I want the numbers to be anywhere in the sheet. Is there anyother way or other formula where we can scramble all the numbers anywhere in the sheet,....

Please help

Thanks in Advance!!!

 

[SkipVought]

Your post did ABSOLUELTY NOTHING to explain the METHOD that is somewhere in your head, I'd assume. You only described the RESULT that you want to have.

Cummon! Open that noggin of yours and give us a glimpse of your METHOD.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[AnotherHiggins]

I still don't know what "but It will do only for 3 rows maximum" means. You can use that to sort all of your rows, then all of your columns. If data doesn't look 'random enough', then repeat the process.

Perhaps there is another way to look at this whole thing. What is the end purpose here? Why would you want a huge chunk of completely random dates?

Are there any limits on what dates you want to use? I'm thinking that you could just use the RAND function (or a variation thereof) to generate the dates themselves.

Let's say that you only dates between Jan 1, 2000 and Dec 31, 2005. Then you could use
[tab][COLOR=blue white]=RANDBETWEEN(36525,38718)[/color]
Format the cell as a date. Then drag the formula across and then down as far as you want. Select all data, then Copy and Paste Special > Values to replace the formulae with actual dates.

[attn]Notes[/attn]:
[ul][li]You will need to have the Analysis Tookpak turned on in Excel to use this function[/li]
[ul][li]Go to Tools > Add-Ins and check the box beside Analysis Tookpak. If it is not listed as an option, post back[/li][/ul]
[li]For more information about why the two integers listed (36526 and 36527) work, see faq68-5827, "Why do Dates and Times seem to be so much trouble?".[/li][/ul]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bluedragon2]

Why not put the three columns into one row, apply the random sort, then break it into three rows again.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

one column I meant

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SilentAiche]

compudevil,

Start over, please.

The "data" you posted simply changed the 4th-6th digits from 86 to 76 to 66.

Do you want to disassociate rows or columns?

Sir, to what end?

798[red]86[/red]70105 798[red]76[/red]70105 798[red]66[/red]70105

Just curious, really curious!
Tim




[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]

 

[compudevil]

Hello,

Friends, I have tries =Rand() formula as been told by anotherhiggins which is helping me doing only 3 rows at a time but What If I got millions of datas in one sheet???.....

I want to Jumble the following numbers for example.Column wise we can jumble the numbers but cannot jumble row wise
so numbers seems in series row wise,...

The number changes from 3rd position:

7910000000 7920000000 7930000000 7940000000
7910000001 7920000001 7930000001 7940000001
7910000002 7920000002 7930000002 7940000002
7910000003 7920000003 7930000003 7940000003
7910000004 7920000004 7930000004 7940000004
7910000005 7920000005 7930000005 7940000005
7910000006 7920000006 7930000006 7940000006
7910000007 7920000007 7930000007 7940000007
7910000008 7920000008 7930000008 7940000008
7910000009 7920000009 7930000009 7940000009
7910000010 7920000010 7930000010 7940000010
7910000011 7920000011 7930000011 7940000011
7910000012 7920000012 7930000012 7940000012
7910000013 7920000013 7930000013 7940000013
7910000014 7920000014 7930000014 7940000014
7910000015 7920000015 7930000015 7940000015
7910000016 7920000016 7930000016 7940000016
7910000017 7920000017 7930000017 7940000017
7910000018 7920000018 7930000018 7940000018
7910000019 7920000019 7930000019 7940000019
7910000020 7920000020 7930000020 7940000020

Thanks,

 

[GlennUK]

In what way are you using the =Rand() formula? ( this is not the first time this has been asked in this thread! )



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[compudevil]

Hello,

Say my numbers are in column A B & C. I use =Rand() in the column D and sort it in that same new column
and for Rows I Put =RAND() in a different row (instead of column), then select the two rows to be sorted and go to Data > Sort > Options. Then under the Orientation section, choose Sort left to right.

Sorting row wise will help me only do 3 rows at a time and I want to jumble up at once or in Half......

,....I tried in date wise but getting an error,....

Is there anyway to sort this problem out?

 

[GlennUK]

Hi compudevil,

I'm really finding this thread an uphill struggle ... query:

I want to jumble up at once or in Half......

... what?????

And another query:

,....I tried in date wise but getting an error,....

this doesn't mean anything to me. Care to rephrase?

And please have a try to answer SilentAiche's queries ... that would help clarify things.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

Haven't tested on large data set, but seems that the code is easiest way:

Dim DataInRange As Variant
Dim i As Long, i1 As Long, i2 As Long
Dim j As Long, j1 As Long, j2 As Long
Dim NoOfVariables As Long, VariableToSwap As Long
Dim Num1, Num2
DataInRange = Range("B2:D11")
i = UBound(DataInRange, 1) ' number of rows
j = UBound(DataInRange, 2) ' number of columns
For j1 = 1 To j
    For i1 = 1 To i
        NoOfVariables = (j - j1) * i + (i - i1) + 1 ' within 1 to N
        ' VariableToSwap: 0 for Nth, 1 for Nth-1 etc., within 0 to N-1
        VariableToSwap = Round(Rnd * (NoOfVariables - 0.5), 0)
        j2 = Int(VariableToSwap / i)
        i2 = VariableToSwap - i * j2
        Num1 = DataInRange(i1, j1)
        Num2 = DataInRange(i - i2, j - j2)
        DataInRange(i1, j1) = Num2
        DataInRange(i - i2, j - j2) = Num1
    Next i1
Next j1
Range("B2:D11") = DataInRange

combo

 

[AnotherHiggins]

compudevil,

You keep asking vague questions, but haven't given any feedback on suggestions we've made or questions we've asked.

First: did you try the RandBetween method I suggested earlier?

There are several knowledgeable folks trying to help you in this thread. Please answer some questions and provide more information about problems you are running into.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[compudevil]

Hello AnotherHigins,....I have just started this and excel for me is very new,....u mentioned something about date which I m not getting what u trying to say,...can u please explain me again in different way so I can apply that formula again,.... Please correct me if I am wrong somewhere as I m new in this field),....I have tried your first solution =Rand which really helped me out with columns,...but later I could not sort it out my problems with row wise,...

Once again let me tell u I want to mix all the cells which are in sequential order,...thats what I want,...!!!

Thanks,...

 

[SkipVought]

...I have just started this and excel for me is very new...

These very basic questions have had NOTHING AT ALL TO DO WITH EXCEL, but have had EVERYTHING TO DO WITH YOUR PURPOSE AND METHOD.

This is a professional forum. Please state you [red]business case[/red] for this process. If you don't know what a business case is, or if you cannot clearly & concisely define requirements, then perhaps this is not the place for you to be posting questions.


Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[compudevil]

Hello AnotherHiggins,....I found out the software DIGB which is compatible with excel,....where all the numbers can be mixed in one go.....It takes time depends on the size of file.
Thanks for your help for =rand functions which I am still using for some other purpose,....
thank you very much