Col A contains source text strings which may include leading zeros and
whitespaces (leading/in-between/trailing). I'm looking for ways to randomly
scramble col A's contents in col B preferably using only standard worksheet
functions or with a UDF.

Col A Col B (random scramble of col A)
1234 4321
099 000 9 09000
opx123 1o2px3
opx 123 o p123x

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max,
Here is a UDF you could use.
It takes two arguments...
the first is the text to scramble
the second argument is optional.
Any entry there causes the function to scramble each time the sheet is calculated.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

' Apr 14, 2002 - Created - James Cone - San Francisco USA
' Scrambles the order of the data in a single-cell.
' Nov 02, 2003 - Last update
'--------------------------------------
Function SCRAMBLE(Optional ByRef UserText As Variant, _
Optional ByRef Everytime As Variant) As String
On Error GoTo Scorched
Dim i As Long
Dim Num As Long
Dim NewPosition As Long
Dim Temp As String
If IsMissing(UserText) Then
SCRAMBLE = "No data"
Exit Function
' No quotes automatically generates an error from the worksheet.
ElseIf IsError(UserText) Then
SCRAMBLE = "Error - try adding quote marks around your entry."
Exit Function
End If
Application.Volatile (Not IsMissing(Everytime))
If TypeName(UserText) = "Range" Then UserText = UserText(1).Value
Num = Len(UserText)
If Num 0 Then
For i = 1 To Num
Temp = Mid$(UserText, i, 1)
NewPosition = Int(Num * Rnd + 1)
Mid$(UserText, i, 1) = Mid$(UserText, NewPosition, 1)
Mid$(UserText, NewPosition, 1) = Temp
Next ' i
SCRAMBLE = UserText
Else
SCRAMBLE = "No data" 'Can result from entering ""
End If
Exit Function
Scorched:
SCRAMBLE = "Error " & Err.Number
End Function
'--------------------



"Max"
wrote in message
Col A contains source text strings which may include leading zeros and
whitespaces (leading/in-between/trailing). I'm looking for ways to randomly
scramble col A's contents in col B preferably using only standard worksheet
functions or with a UDF.

Col A Col B (random scramble of col A)
1234 4321
099 000 9 09000
opx123 1o2px3
opx 123 o p123x

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik

"Jim Cone" wrote:
.. Any entry there causes the function
to scramble each time the sheet is calculated.

Jim, many thanks for your UDF ! Is there a way to force the UDF to recalc
whenever I press F9? Most of the time, the source data would be static, but
I'd like to re-scramble.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

You can add

Application.Volatile

on one line but it will slow down Excel

you can use Ctrl + Alt + F+ instead of F9, that should recalculate but it
will slow down as well since recalculates the whole workbook


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Max" wrote in message
...
"Jim Cone" wrote:
.. Any entry there causes the function
to scramble each time the sheet is calculated.

Jim, many thanks for your UDF ! Is there a way to force the UDF to recalc
whenever I press F9? Most of the time, the source data would be static,
but
I'd like to re-scramble.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,
Any entry, number or text (any length), as the second argument does that...
=scramble(D6,x)

Regards,
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"Max"
wrote in message
"Jim Cone" wrote:
.. Any entry there causes the function
to scramble each time the sheet is calculated.

Jim, many thanks for your UDF ! Is there a way to force the UDF to recalc
whenever I press F9? Most of the time, the source data would be static, but
I'd like to re-scramble.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Jim, thanks! Yes, that does it.
Sorry I wasn't able to appreciate that 2nd argument earlier.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jim Cone" wrote:
Max,
Any entry, number or text (any length), as the second argument does that...
=scramble(D6,x)

Regards,
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

Peo, thanks for the insights. Ctrl + Alt + F9 also works.
But think I'll frame it up to use Jim's UDF's 2nd arg: =scramble(A2,x)
which works with an F9.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peo Sjoblom" wrote:
Max,

You can add

Application.Volatile

on one line but it will slow down Excel

you can use Ctrl + Alt + F+ instead of F9, that should recalculate but it
will slow down as well since recalculates the whole workbook


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

Oops! Didn't look at Jim's code, I just took you on your word <bg


Peo


"Peo Sjoblom" wrote in message
...
Max,

You can add

Application.Volatile

on one line but it will slow down Excel

you can use Ctrl + Alt + F+ instead of F9, that should recalculate but it
will slow down as well since recalculates the whole workbook


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Max" wrote in message
...
"Jim Cone" wrote:
.. Any entry there causes the function
to scramble each time the sheet is calculated.

Jim, many thanks for your UDF ! Is there a way to force the UDF to recalc
whenever I press F9? Most of the time, the source data would be static,
but
I'd like to re-scramble.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Peo Sjoblom" wrote:
Oops! Didn't look at Jim's code, I just took you on your word <bg

No prob. Guess I failed to appreciate the second arg earlier (admission with
apologies given to Jim in response <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That is interesting. I knew about the volatile statement, but had never seen
it used in the context of an optional argument. Good to know!
--
Kevin Vaughn


"Jim Cone" wrote:

Max,
Any entry, number or text (any length), as the second argument does that...
=scramble(D6,x)

Regards,
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"Max"
wrote in message
"Jim Cone" wrote:
.. Any entry there causes the function
to scramble each time the sheet is calculated.

Jim, many thanks for your UDF ! Is there a way to force the UDF to recalc
whenever I press F9? Most of the time, the source data would be static, but
I'd like to re-scramble.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---