Randomize a Column & Identify a Word

[rwmill9716]

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.

 

[DanteAmor]

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:

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]

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:

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]

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]

@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]

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	0.56356	FLL	LLF	0	0			0
3	L	0.94582	LLO	OLL	0				0
4	L	0.00596	LOC	COL	0				1
5	O	0.04993	OCF	FCO	0				2
6	C	0.79065	CFA	AFC	0				0
7	F	0.86092	FAG	GAF	0				0
8	A	0.19480	AGP	PGA	0				0
9	G	0.58370	GP	PG	0				0
10	P	0.33145	P	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]

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]

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

Excel 2013

 

[Peter_SSs]

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.

1. 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"?
2. 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?

 

[rwmill9716]

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

View attachment 97962

A bit more clarification about your question.

1. 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"?
2. 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?

Ultimately, I'm using this program to study word sequences (codes if you will) that occur in the Bible (validated by fulfilled prophecy, scientific facts, and hidden codes). These codes can have words that are written both ways depending on whether the name is being favorably or unfavorably looked at. The codes are there (usually with much longer words), and I want to get a feel for just how rare there occurrences are).

If RIC occurred 3 times and CIR 2 times, the count would be 5. Three-letter words will be a rare occurrence. I can resample by hitting F9 multiple times, and from doing that I get 16 RIC/CIR occurrences in 10,000 samplings of a population made by randomizing 192 copies of the 26 letters in the alphabet (192 Rs, Is, and Cs).