Randomize a Column & Identify a Word

rwmill9716 · Aug 25, 2023

I have a set of letters in Column A. That Column has been randomized in Column B where the word 'Ric' from Cell C1 is found. I need a macro that will randomize Column A 1000 times and count how many times 'Ric' is spelled out.

bebo021999 · Aug 30, 2023

A2:A4993 consists of 192 sets of A-Z, sequentially arranged as each set goes from A to Z and then repeats (referred to as the "List").
I tested with n=100 iterations. Adjustable in code.
For each test iteration:

This "List" will shuffle in position, but all items remains unchanged (192 A's, 192 B's,..., 192 Z's).
Afterward, it looks for sequences of R, I, C, or C, I, R (a condition sequence of any length specified in cell F1).

After n test iterations, the results will be in the range E:H.
If you want to know the specific results of each test iteration, they can be found in the columns to the right, starting from column M.

Book2.xlsm

drive.google.com

VBA Code:

Option Explicit
Sub words()
Dim lr&, i&, j&, k&, r, rng, arr(), res(), word As String, n&, c&, t&, st As String, count&
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
word = Range("F1").Value ' word criteria
n = 100 ' running time
ReDim res(1 To n, 1 To 3)
lr = Cells(Rows.count, "A").End(xlUp).Row
rng = Range("A2:A" & lr).Value ' given list in col A
Range("F3:H100000").ClearContents
Range("N2:XX100000").ClearContents
Randomize
Do
    ReDim arr(1 To UBound(rng), 1 To 1)
    c = c + 1: k = 0: count = 0
    Do
        r = Int(Rnd * UBound(rng)) + 1
        If Not dic.exists(r) Then
            dic.Add r, ""
            k = k + 1: arr(k, 1) = rng(r, 1)
        End If
    Loop Until k >= UBound(rng)
    dic.RemoveAll
    For i = 1 To UBound(arr)
        st = ""
        If arr(i, 1) = Left(word, 1) Then
            On Error Resume Next
            For j = 0 To Len(word) - 1
                st = st & arr(i + j, 1)
            Next
            On Error GoTo 0
            If st = word Then count = count + 1
        End If
        If arr(i, 1) = Right(word, 1) Then
            On Error Resume Next
            For j = Len(word) - 1 To 0 Step -1
                st = st & arr(i - j, 1)
            Next
            On Error GoTo 0
            If st = word Then count = count + 1
        End If
        If st = word Then res(c, 1) = count: res(c, 2) = st: res(c, 3) = i
    Next
    If count > 0 Then
        t = t + 1
        With Range("XX3").End(xlToLeft).Offset(0, 1)
            .Offset(-2, 0).Value = t
            .Offset(-1, 0).Value = count
            .Resize(UBound(arr), 1).Value = arr
        End With
    End If
Loop Until c >= n
Range("F3").Resize(n, 3).Value = res
MsgBox "Finish! match found: " & t
End Sub

DanteAmor · Aug 28, 2023

Try the following macro.
The result of the count in cell D1.
From column F onwards the matches.

NOTES:

1. It works for 3 letters in cell D1.

2. If you don't want the result columns, remove this line from the macro:

Range("F1").Resize(UBound(c, 1), UBound(c, 2)).Value = c

3. Set the number of times in this line of the macro:

ReDim b(1 To UBound(a, 1), 1 To 1000) 'times

Put the following code in a module:

VBA Code:

Sub rand_word_v2()
  Dim a As Variant, b As Variant, c As Variant, arr As Variant
  Dim i&, j&, k&, lr&, n&, m&, x&, y&, z&
  Dim w1 As String, w2 As String, w3 As String
  
  Randomize
  
  a = Range("A1", Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 1000)              'times
  ReDim c(1 To UBound(b, 1), 1 To UBound(b, 2))
  With Range("C1")
    w1 = LCase(Mid(.Text, 1, 1))
    w2 = LCase(Mid(.Text, 2, 1))
    w3 = LCase(Mid(.Text, 3, 1))
  End With
  
  For j = 1 To UBound(b, 2)
    arr = Evaluate("ROW(1:" & UBound(a, 1) & ")")   'total records
    lr = UBound(a, 1)
    For z = 1 To UBound(a)                          'how many do i want
      x = Int(Rnd * lr + z)
      y = arr(z, 1)
      arr(z, 1) = arr(x, 1)
      arr(x, 1) = y
      
      lr = lr - 1
      m = arr(z, 1)                                 'random number
      b(z, j) = a(m, 1)
    Next
    For i = 3 To UBound(b, 1)
      If LCase(b(i - 2, j)) = w1 And LCase(b(i - 1, j)) = w2 And LCase(b(i, j)) = w3 Then
        k = k + 1
        For n = 1 To UBound(b, 1)
          c(n, k) = b(n, j)
        Next
      End If
    Next
  Next
  Range("D1").Value = k
  Range("F1").Resize(UBound(c, 1), UBound(c, 2)).Value = c
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

rwmill9716 · Aug 28, 2023

DanteAmor said:
Try the following macro.
The result of the count in cell D1.
From column F onwards the matches.

View attachment 97919

NOTES:

1. It works for 3 letters in cell D1.
2. If you don't want the result columns, remove this line from the macro:
Range("F1").Resize(UBound(c, 1), UBound(c, 2)).Value = c
3. Set the number of times in this line of the macro:
ReDim b(1 To UBound(a, 1), 1 To 1000) 'times

Put the following code in a module:

VBA Code:

Sub rand_word_v2() Dim a As Variant, b As Variant, c As Variant, arr As Variant Dim i&, j&, k&, lr&, n&, m&, x&, y&, z& Dim w1 As String, w2 As String, w3 As String Randomize a = Range("A1", Range("A" & Rows.Count).End(3)).Value ReDim b(1 To UBound(a, 1), 1 To 1000) 'times ReDim c(1 To UBound(b, 1), 1 To UBound(b, 2)) With Range("C1") w1 = LCase(Mid(.Text, 1, 1)) w2 = LCase(Mid(.Text, 2, 1)) w3 = LCase(Mid(.Text, 3, 1)) End With For j = 1 To UBound(b, 2) arr = Evaluate("ROW(1:" & UBound(a, 1) & ")") 'total records lr = UBound(a, 1) For z = 1 To UBound(a) 'how many do i want x = Int(Rnd * lr + z) y = arr(z, 1) arr(z, 1) = arr(x, 1) arr(x, 1) = y lr = lr - 1 m = arr(z, 1) 'random number b(z, j) = a(m, 1) Next For i = 3 To UBound(b, 1) If LCase(b(i - 2, j)) = w1 And LCase(b(i - 1, j)) = w2 And LCase(b(i, j)) = w3 Then k = k + 1 For n = 1 To UBound(b, 1) c(n, k) = b(n, j) Next End If Next Next Range("D1").Value = k Range("F1").Resize(UBound(c, 1), UBound(c, 2)).Value = c End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Dante,

I've downloaded your macro and tried it. Thanks for working on this. My comments follow:
1. The rearrangements of column A don't appear to be random, i.e., it's not possible for "Ric" to show up in each column.
2. What I want is column A to be 10,000 rows (192 complete sets of 26 letters alphabet) {of course, the program development can be much smaller}.
3. That complete column is then randomized; it's ok to put the randomized column in another column.
4. The letter sequence in cell C1 is searched for in the randomized column, and the number of times it occurs is output in D1.
5. Here, I show a 3-letter sequence, but I would like to be able to change that to other length words.

My reason for doing this is that there are codes in the Bible involving word sequences. The rarity of these sequences happening by chance is a validation of the Bible. That work has been done and reported in the literature, but my interest is in evaluating myself how likely various sized word sequences could be expected to occur by random chance.

Thanks for your help.

Ric Miller

DanteAmor · Aug 28, 2023

rwmill9716 said:
1. The rearrangements of column A don't appear to be random, i.e., it's not possible for "Ric" to show up in each column.
2. What I want is column A to be 10,000 rows (192 complete sets of 26 letters alphabet) {of course, the program development can be much smaller}.
3. That complete column is then randomized; it's ok to put the randomized column in another column.
4. The letter sequence in cell C1 is searched for in the randomized column, and the number of times it occurs is output in D1.
5. Here, I show a 3-letter sequence, but I would like to be able to change that to other length words.

So I didn't understand your requirement.

1. You could put more representative examples of what you have in column A and what you expect as a result in the same column A.

2. I also didn't understand your column B example, if you don't require column B then don't mention it.

3. If you are not going to search for "ric" then give more representative examples. Write 3 or 4 examples of the results you need.

4. I don't want you to put the 10,000 sample records, but a sample of what you want as a result in column A. This is achieved, if you give an example of what you have before, and the example of what you expect as a result, that is, 2 examples and not just an image.

5.For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Peter_SSs · Aug 29, 2023

@rwmill9716
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

rwmill9716 · Aug 29, 2023

Here's a simplified version of my program. Col A is a column of letters; Col B is a random number assignment for each with which I'll sort Col A to randomize the letters (note, after this is done for some reason Col B generates a new set of random numbers). My interest with these two columns is to randomize the set of letters in Col A. Cols C & D build words from the randomized set. Col E then checks each word to see if it matches the word in F1 or G1 (if it does, it enters a 1); Cell F2 then sums Col E. I can hit F9 and generate a single result, but I would like a macro that effectively hits F9 100 times and places the F2 Cell result for each in Col I (results shown for 10 F9 pushes).

230209 Sequencing Share.xlsm

A

B

C

D

E

F

G

H

I

1

Letters

=RAND()

=CONCATENATE(A2,A3,A4)

=CONCATENATE(A4,A3,A2)

=IF(OR(F$1=C2,G$1=D2),1,0)

RIC

CIR

Results

2

F

3

L

4

L

1

5

O

2

6

C

7

F

8

A

9

G

10

P

0.33145

P

0

1

11

1

12

Word Probability

Cell Formulas
Range	Formula
B2:B10	B2	=RAND()
C2:C10	C2	=CONCATENATE(A2,A3,A4)
D2:D10	D2	=CONCATENATE(A4,A3,A2)
E2:E10	E2	=IF(OR(F$1=C2,G$1=D2),1,0)
F2	F2	=SUM(E2:E17)
F3:F10	F3	=IF(E3=1,1,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E9:E10	Cell Value	>0	text	NO
E1:E8,E11:E1048576	Cell Value	>0	text	NO
F:F	Cell Value	>">0"	text	NO

Peter_SSs · Aug 29, 2023

rwmill9716 said:
Here's a simplified version of my program.

That still does not tell us what Excel version you are using. Refer to post #5

rwmill9716 · Aug 29, 2023

Peter_SSs said:
That still does not tell us what Excel version you are using. Refer to post #5

Excel 2013

Peter_SSs · Aug 29, 2023

rwmill9716 said:
Excel 2013

Thanks. Can you now put it in your account details so that it is always readily available for helpers to refer to like this?

A bit more clarification about your question.

If this is about word occurrences, why check the reverse of the word as well as the word itself since the reverse is unlikely to be an actual word? That is, why check "RIC" and "CIR"?
If you have 10,000 rows of letters, it is possible (likely) that a word (&/or its reverse) will occur multiple times. So in a particular recalculation if "RIC" occurred 3 times and "CIR" occurred 2 times, can you clarify what value should appear in the results column for that row?

Randomize a Column & Identify a Word

rwmill9716

Active Member

Attachments

Excel Facts

bebo021999

Well-known Member

Book2.xlsm

DanteAmor

Well-known Member

rwmill9716

Active Member

DanteAmor

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

rwmill9716

Active Member

Peter_SSs

MrExcel MVP, Moderator

rwmill9716

Active Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Randomize a Column & Identify a Word

Active Member

Attachments

Excel Facts

Well-known Member

Well-known Member

Active Member

Well-known Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock