How to search for random patterns within a column

[Frustrated_excel274]

Hi All, I'm totally lost and have been searching for 2 days on how to do this. I have an excel spreadsheet of with each cell containing random numbers and letters. I want do a search that will compare the cells and automatically find any 5 character patterns. Case sensitivity doesn't matter. Example:
A1: FeexV6bAHb8ybZjq
A2: iVkebCz8JfEEXvgkPiM
A3: hg363dRjUgq2feExvXL

I would want a search function that would output "feexv" because it is the most common 5 character set found. Is there a way to do this?

 

[gurs]

Are you trying to do this with VBA or straight formulas? Is this a one-time analysis or will the list of random text strings constantly be changing? If you are trying to do it with formulas, I think I came up with a solution. See this file & let me know if it does the trick. Just put the random text strings in A2:A4 and the spreadsheet should do the rest. You can insert additional rows above row 4 to add more text strings and just fill down the formulas is columns D:S from row 2. But if you add rows to the input range, you are probably going to need to make the "Unique Blobs" range really long though (this is an array formula, needs to be cntl-shift-enter). Probably cleaner to do this in VBA if the large data set is large.

Proposed Solution: https://file.io/6B81wYF1kWXa

 

[Frustrated_excel274]

It's a one-time analysis and the data set is large. I was trying to do it with a formula but couldn't get it work. I'm not sure what a VBA is. I'm willing to try anything to make it work. I tried clicking on your link but it says the file is deleted and nothing else.

 

[BSALV]

how big is your data ? How many cells * average number of characters within a cell
Can you add some cells with the XL2BB-tool ?
Still excel2010 ?

 

[Frustrated_excel274]

how big is your data ? How many cells * average number of characters within a cell
Can you add some cells with the XL2BB-tool ?
Still excel2010 ?

Still using excel 2010 but I've also got access to Google docs. It's a large document. Thousands of cells but all in the same column. All cells have 34 characters.

 

[Peter_SSs]

@gurs
In relation to your style of answer in post #2 above, please review #4 of the Forum Rules

@Frustrated_excel274
Give the following macro a try with a copy of your workbook.
It assumes the values are in column A and puts the result(s) in column B.
My data may not be representative of yours but processed 12,000 rows in less than half a second.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub Common5()
  Dim d As Object
  Dim a As Variant, itm As Variant, b As Variant
  Dim s As String, ss As String
  Dim i As Long, j As Long, k As Long, maxx As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a) * 30, 1 To 1)
  For i = 1 To UBound(a)
    s = a(i, 1)
    For j = 1 To Len(s) - 4
      ss = LCase(Mid(s, j, 5))
      d(ss) = d(ss) + 1
      If d(ss) > maxx Then maxx = d(ss)
    Next j
  Next i
  For Each itm In d.Keys
    If d(itm) = maxx Then
      k = k + 1
      b(k, 1) = itm
    End If
  Next itm
  Range("B1").Resize(k).Value = b
End Sub

Here is my small sample data and results.

Frustrated_excel274.xlsm
	A	B
1	FeexV6bAHb8ybZjq	feexv
2	iVkebCz8JfEEXvgkPiM	63drj
3	hg363dRjUgq2feExvXL
4	hk363dRjkgq2fzExjXL
5	hzz63dRjagq2feExjXL
6
Sheet1

 

[Peter_SSs]

@gurs
I have removed your last post. Please see the start of post #6 for the reason.

 

[BSALV]

Variant on Peter_SSs in #6 with all the collected substrings

Sub Common5bis()
     Dim Joined
     Set d = CreateObject("Scripting.Dictionary")               'make dictionary
     d.CompareMode = 1                                          'not case sensitive
     a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value   'data to array
     For i = 1 To UBound(a)                                     'loop trough alle records
          If Len(a(i, 1)) >= 5 Then                             'length at least 5
               For l = 1 To Len(a(i, 1)) - 4                    'all strings withing record
                    s = Mid(a(i, 1), l, 5)                      'that string
                    d(s) = d(s) + 1                             'increment
               Next
          End If
     Next

     Joined = Application.Index(Array(d.Keys, d.items), 0, 0)   'join the keys and the items
     With Range("D1").Resize(UBound(Joined, 2), 2)              'export range
          .EntireColumn.ClearContents                           'clear previous
          .Value = Application.Transpose(Joined)                'write to sheet
          .Sort .Range("B1"), xlDescending, Header:=xlNo        'sort descending
     End With

End Sub

Map1
	A	B	C	D	E
1	FeexV6bAHb8ybZjq	feexv		FeexV	3
2	iVkebCz8JfEEXvgkPiM	63drj		63dRj	3
3	hg363dRjUgq2feExvXL			363dR	2
4	hk363dRjkgq2fzExjXL			gq2fe	2
5	hzz63dRjagq2feExjXL			q2feE	2
6				2feEx	2
7				ExjXL	2
8				eexV6	1
9				exV6b	1
10				xV6bA	1
11				V6bAH	1
12				6bAHb	1
13				bAHb8	1
14				AHb8y	1
15				Hb8yb	1
16				b8ybZ	1
17				8ybZj	1
18				ybZjq	1
19				iVkeb	1
20				VkebC	1
21				kebCz	1
22				ebCz8	1
23				bCz8J	1
24				Cz8Jf	1
25				z8JfE	1
26				8JfEE	1
27				JfEEX	1
Blad1

 

[gurs]

@gurs
I have removed your last post. Please see the start of post #6 for the reason.

Well that's a bummer. I have a perfectly functional solution, with absolutely no code, VBA, trusted locations or XLSM required. I would be happy to post the XLSX file to this thread but it won't allow it. Not sure why this forum would want to prevent me from sharing my solution with the OP. Seems against the spirit of the forum, which I thought was to allow users to help other users. Sorry, @Frustrated_excel274, I tried!

 

[gurs]

@gurs
I have removed your last post. Please see the start of post #6 for the reason.

The OP actually said "I don't know what a VBA is". Pretty clear a no-code solution is the optimal path here. Frustrating that I developed one but am prevented from sharing it.