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?

 

[Peter_SSs]

I have a perfectly functional solution, ....

I agree that you have a suggestion that may suit the OP, it is just that the rule asks you to post your solution in the forum, the reason being that other forum users can search for answers without asking the same question and also the OP or other users can access the solution even if their workplace bars downloads such as yours - and they can get the solution for more than 7 days.

.. am prevented from sharing it.

Not so, I would have thought that with 11 years of forum membership you would have seen plenty of solutions posted in the forum.
It is easy enough to post the solution here with XL2BB as below. If you are unable to use XL2BB, you could at least post the formulas for B2, C2, D2, E2, A7 & B7 and describe where to copy them to.

Formula suggestion by @gurs

5charTest.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	String	Len	Blobs	Blob1	Blob2	Blob3	Blob4	Blob5	Blob6	Blob7	Blob8	Blob9	Blob10	Blob11	Blob12	Blob13	Blob14	Blob15	Blob16
2	FeexV6bAHb8ybZjq	16	12	FeexV	eexV6	exV6b	xV6bA	V6bAH	6bAHb	bAHb8	AHb8y	Hb8yb	b8ybZ	8ybZj	ybZjq
3	iVkebCz8JfEEXvgkPiM	19	15	iVkeb	VkebC	kebCz	ebCz8	bCz8J	Cz8Jf	z8JfE	8JfEE	JfEEX	fEEXv	EEXvg	EXvgk	XvgkP	vgkPi	gkPiM
4	hg363dRjUgq2feExvXL	19	15	hg363	g363d	363dR	63dRj	3dRjU	dRjUg	RjUgq	jUgq2	Ugq2f	gq2fe	q2feE	2feEx	feExv	eExvX	ExvXL
5
6	Unique Blobs	Freq
7	FeexV	3
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
28	EEXvg	1
29	EXvgk	1
30	XvgkP	1
31	vgkPi	1
32	gkPiM	1
33	hg363	1
34	g363d	1
35	363dR	1
36	63dRj	1
37	3dRjU	1
38	dRjUg	1
39	RjUgq	1
40	jUgq2	1
41	Ugq2f	1
42	gq2fe	1
43	q2feE	1
44	2feEx	1
45	eExvX	1
46	ExvXL	1
47
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LEN(A2)
C2:C4	C2	=B2-4
D2:D4	D2	=LEFT(A2,5)
E2:S4	E2	=IF(COLUMN()-3>$C2,"",MID($A2,COLUMN()-3,5))
A7:A47	A7	=INDIRECT(TEXT(MIN(IF(($D$2:$S$4<>"")*(COUNTIF(A$6:A6,$D$2:$S$4)=0),ROW($2:$4)*100+COLUMN($D:$S),7^8)),"R0C00"),)&""
B7:B47	B7	=IF(A7="","",COUNTIFS($D$2:$S$4,A7))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:B57	Other Type	Color scale		NO

 

[BSALV]

i'm sure with OP's excel-2010 and all those matrixformulas, that 's not really a solid solution.
Your result is an unsorted list of unique blobs (here with only 3 cells of 18 characters, you needed at least minimum 42 of those matrixformulas). That becomes quickly too much and too slow.
Afterwards you still have to find the most frequent with possible equal number.
OP wanted a solution and even if it was VBA, it was okay.
Imho, VBA is the only solution for this job in a 2010-version.

 

[gurs]

Formula suggestion by @gurs

Thanks for posting that for me. Just a lot more work for a less-proficient person seeking help than a functioning workbook. I get your point regarding the preferability of having files that are proposed solutions be available indefinitely, which makes sense. I could swear you used to be able to attach xls files to a post here, but maybe I am just confusing Mrexcel with some other forum.

 

[BSALV]

indeed, you can attach xls and other files here.
Use the link-symbol (CTRL+K), just left of the smiley-icon, while you're posting.
When you use that one, copy the link-string in there.
But indeed, the site prefers also the other solution with for example XL2BB.

 

[gurs]

Use the link-symbol (CTRL+K), just left of the smiley-icon, while you're posting.
When you use that one, copy the link-string in there.

Apologies for being dense about this, but what is the "link-string" for a file on my local hard drive?

 

[Fluff]

indeed, you can attach xls and other files here.

No you can't. You can upload files to a share site & post a link to them (which is what gurs has already done), but you cannot attach them to this site.

 

[gurs]

... You can upload files to a share site & post a link to them ...

If this method is acceptable, why were my file links taken down as a TOS violation?

 

[Fluff]

Peter has already mentioned why in post#6.

 

[gurs]

Peter has already mentioned why in post#6.

In post #6, @Peter_SSs referred me to rule #4, which says in relevant part: "please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links". Yet then in post #14, @BSALV suggests that I post a link to a file shared on another site, and in post #16 you said it was acceptable to "upload files to a share site & post a link to them".

I hope you understand my confusion. How can it be a TOS violation to

"answer questions by creating solutions elsewhere and then referencing those solutions via file links",​

but acceptable to

"upload files to a share site & post a link to them"?​

Aren't those two phrases saying the same thing?

 

[Fluff]

The rule is quite clear, it says

Likewise, please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links. We ask that you answer the question within the thread itself

In post#16 I was not saying it was acceptable to answer questions, simply by supplying a file. I was saying that you cannot upload files to this site, but would need to use a share site.