Randomize the cell contents in different sets

[Pete2020]

Hi Team, I am scratching my head to randomize the cell contents in different sets with flower brackets and a comma separator

Excel Formula or function or VBA is much helpful. The actual data is Text and not a Numeric values.

Ex: Column A Row 1 ( Full range has around 10k Rows)
1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30

Expected Output in column B ROW 1
1.Random Unique values selection and Grouping 5 cell text into one group by comma seperate between each pair
2. If it is not divisible by 5 equally then last set will adjust accordingly with balance left

{{2|4|6|8|10},{1|3|5|7|9},{12|14|16|18|20},{11|13|15|17|19},{22|24|26|28|30},{21|23|25|27|29}}

 

[samitnair]

Would you mind attaching a screen shot of before & after sheet or use XL2BB to attach a sample.

 

[Pete2020]

Please find the sample data and expected result in column B

 

[Yongle]

Amend function below to match your own requirements

Formula
=ShuffleString(A1)

WARNING
- the formula recalclulates if cell in column B is edited (or even if user enters cell with F2 and changes nothing) because of inclusion of Randomize in the function

Credit
Function ShuffleArray found in link below adapted to match your requested output

Shuffle Array

www.cpearson.com

Function ShuffleString(ByVal aString As String) As String
    Dim Orig() As String, myStr As String
    Dim N As Long, Temp As Variant, J As Long, L As Long, Arr() As Variant
'array of original values
    Orig = Split(aString, "|")
'rearrange in random order
    Randomize
    L = UBound(Orig) - LBound(Orig) + 1
    ReDim Arr(LBound(Orig) To UBound(Orig))
    For N = LBound(Orig) To UBound(Orig)
        Arr(N) = Orig(N)
    Next N
    For N = LBound(Orig) To UBound(Orig)
        J = CLng(((UBound(Orig) - N) * Rnd) + N)
        Temp = Arr(N)
        Arr(N) = Arr(J)
        Arr(J) = Temp
    Next N
'return String
    J = 1
    For L = 0 To UBound(Arr)
        If J = 6 Then J = 1
        If J = 1 Then myStr = myStr & "{"
        myStr = myStr & Arr(L) & "|"
        If J = 5 Then myStr = myStr & "},"
        J = J + 1
    Next L
    If J <> 6 Then myStr = myStr & "}"
    myStr = Replace("{" & Replace(myStr, "|}", "}") & "}", "},}", "}}")
    ShuffleString = myStr
End Function

 

[Yongle]

Code in post#4 also works with text as per post 3

This string
The happy Dog|sat on the mat|next to the cat |the cat was not happy|and sctratched the dog|which yelped| and bit the cat|before eating its tea|the cat then chased the mouse|the mose ran into a hole|and ate some cheese|the dog barked|the cat meowed|the mouse roared

Is returned as
{{the cat was not happy|before eating its tea|the mose ran into a hole|and ate some cheese|which yelped},{ and bit the cat|The happy Dog|next to the cat |sat on the mat|the cat meowed},{the dog barked|the cat then chased the mouse|and sctratched the dog|the mouse roared}}

or as ...
{{next to the cat |the cat was not happy|and sctratched the dog|the cat then chased the mouse|before eating its tea},{the dog barked|and ate some cheese|the mouse roared|the mose ran into a hole|which yelped},{sat on the mat|the cat meowed| and bit the cat|The happy Dog}}

etc

 

[Pete2020]

Thank You So Much Yongle.

It meets my requirement.

I never forget your Great Help

 

[Yongle]

In future please ask all questions on the thread

(In a private message) you asked Can you please edit this macro without randomize the string order

This thread's title is Randomize the cell contents in different sets and now you are asking for it not to be randomized
- so it is a DIFFERENT question to the original and should be asked differently on a NEW thread

 

[Pete2020]

Thanks Yongle. Will do that Immediately