Compare 2 alphanumeric strings (without spaces) and highlight the similar part

[massi]

Hi everyone, I need to compare the strings contained in 2 adjacent columns (row by row) and highlight the similar part.

I tried with some VBA code I found online but it doesn't work well for me, as my strings don't contain any spaces or other characters.

I attach some of the lines.

Thank you all!!

Cartel1
	A	B
1	Column 1	Column 2
2	pantotexsrl	pantotex
3	hgisrl	abbigliamentohgi
4	modamilanosrl	modamilanosrl
5	bluediffusionsrl	bluediffusion
6	centromodaruggierobyarconfabbigliamentosumisura	centromoda2
7	lafogliasrl	lafogliasrl
8	franzeseabbigliamentosrl	franzeseabb
9	euforiakajalproduzioneabbigliamentodonnabyfdpsrl	fdpsrl
10	stigmatieltd	stigmati
11	ghirafsrl	ghirafsrl
12	cucitex2srlindumentiusati	cucitex2011
13	produzioneeingrossoabbigliamentodonnamonicamorelli	monicamorelliofficial
14	atelebabrideelenabarba	elenabarbasposa
15	goldjeanssrl	goldjeans
16	lauclecreazionidiguarinoanna	laucle
17	machodiffusion2ndstreetshowroom	machodiffusion
18	sitelliskinlaboutletrivadirenobologna	sitellishop
19	nuovaorizzontisrl	orimodasrl
20	soelservicesncditagliatielisaebenatisonia	soelservice
21	ellebisrl	alanellebi
22	plissettaturacaporalidivalmorritomasecsas	plissettaturacaporali
23	confezionievi	eviconfezioni
24	deantonicarnia	dacarnia
25	gheshandgoldsrl	commercialegheshgold
26	implosedabbigliamentouomo	implosed
27	centrodeca	decaroma
28	dmyddmsrlu	dmsrl17
29	aretesrl	aretemoda
30	ingrossoabbigliamentosportivoroma100x100cotone	100x100cotonesrls
31	mpmartoranosrl	mpmartoranosrl
32	martinettireadivisescolasticheabitiprimacomunione	martinettirea
33	lineaecostume	lineacostume
34	blive	blivepromozionale
35	fashiontradesrl	fashiontrade
36	mffsrlmattmodafashionfursrl	mffsrl
37	futurasasdirizzivitoec	futuravitorizzi
38	annarockmilano	annarockmilano
39	almarsrl	almaringrossoabbigliamento
Foglio1

 

[duggie33]

Hi massi,

Welcome! Please provide some expected results. Which column is to be highlighted? Does the entire string from column B need to be found in column A? You do not want to match single letters right?

Thanks,

Doug

 

[massi]

Hi massi,

Welcome! Please provide some expected results. Which column is to be highlighted? Does the entire string from column B need to be found in column A? You do not want to match single letters right?

Thanks,

Doug

Hi Duggie and thanks for your help!

The column that needs to be highlighted is the second (B), in the image I also highlighted the first (in green) just to make you see the correspondence without effort.

You can see the expected result in the image. I understand that it can be complicated but maybe there is a way to set a minimum match of 3 consecutive characters (letters and numbers) so that it is detected and highlighted.

 

[duggie33]

I know this does not meet your results, but this is part way there...the formula in column C returns the three letter strings (generated from column B) that are found in column A. I am still thinking about the approach to writing this in VBA. It is for sure possible since I was able to generate these results. I might not be able to get to that until later today if I am lucky.

Book_2023-10-03.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Column 1	Column 2
2	pantotexsrl	pantotex	pan	ant	nto	tot	ote	tex
3	hgisrl	abbigliamentohgi	hgi
4	modamilanosrl	modamilanosrl	mod	oda	dam	ami	mil	ila	lan	ano	nos	osr	srl
5	bluediffusionsrl	bluediffusion	blu	lue	ued	edi	dif	iff	ffu	fus	usi	sio	ion
6	centromodaruggierobyarconfabbigliamentosumisura	centromoda2	cen	ent	ntr	tro	rom	omo	mod	oda
7	lafogliasrl	lafogliasrl	laf	afo	fog	ogl	gli	lia	ias	asr	srl
8	franzeseabbigliamentosrl	franzeseabb	fra	ran	anz	nze	zes	ese	sea	eab	abb
9	euforiakajalproduzioneabbigliamentodonnabyfdpsrl	fdpsrl	fdp	dps	psr	srl
10	stigmatieltd	stigmati	sti	tig	igm	gma	mat	ati
11	ghirafsrl	ghirafsrl	ghi	hir	ira	raf	afs	fsr	srl
12	cucitex2srlindumentiusati	cucitex2011	cuc	uci	cit	ite	tex	ex2
13	produzioneeingrossoabbigliamentodonnamonicamorelli	monicamorelliofficial	mon	oni	nic	ica	cam	amo	mor	ore	rel	ell	lli
14	atelebabrideelenabarba	elenabarbasposa	ele	len	ena	nab	aba	bar	arb	rba
15	goldjeanssrl	goldjeans	gol	old	ldj	dje	jea	ean	ans
16	lauclecreazionidiguarinoanna	laucle	lau	auc	ucl	cle
17	machodiffusion2ndstreetshowroom	machodiffusion	mac	ach	cho	hod	odi	dif	iff	ffu	fus	usi	sio	ion
18	sitelliskinlaboutletrivadirenobologna	sitellishop	sit	ite	tel	ell	lli	lis
19	nuovaorizzontisrl	orimodasrl	ori	srl
20	soelservicesncditagliatielisaebenatisonia	soelservice	soe	oel	els	lse	ser	erv	rvi	vic	ice
21	ellebisrl	alanellebi	ell	lle	leb	ebi
22	plissettaturacaporalidivalmorritomasecsas	plissettaturacaporali	pli	lis	iss	sse	set	ett	tta	tat	atu	tur	ura	rac	aca	cap	apo	por	ora	ral	ali
23	confezionievi	eviconfezioni	evi	con	onf	nfe	fez	ezi	zio	ion	oni
24	deantonicarnia	dacarnia	car	arn	rni	nia
25	gheshandgoldsrl	commercialegheshgold	ghe	hes	esh	gol	old
26	implosedabbigliamentouomo	implosed	imp	mpl	plo	los	ose	sed
27	centrodeca	decaroma	dec	eca
28	dmyddmsrlu	dmsrl17	dms	msr	srl
29	aretesrl	aretemoda	are	ret	ete
30	ingrossoabbigliamentosportivoroma100x100cotone	100x100cotonesrls	100	00x	0x1	x10	100	00c	0co	cot	oto	ton	one
31	mpmartoranosrl	mpmartoranosrl	mpm	pma	mar	art	rto	tor	ora	ran	ano	nos	osr	srl
32	martinettireadivisescolasticheabitiprimacomunione	martinettirea	mar	art	rti	tin	ine	net	ett	tti	tir	ire	rea
33	lineaecostume	lineacostume	lin	ine	nea	cos	ost	stu	tum	ume
34	blive	blivepromozionale	bli	liv	ive
35	fashiontradesrl	fashiontrade	fas	ash	shi	hio	ion	ont	ntr	tra	rad	ade
36	mffsrlmattmodafashionfursrl	mffsrl	mff	ffs	fsr	srl
37	futurasasdirizzivitoec	futuravitorizzi	fut	utu	tur	ura	vit	ito	riz	izz	zzi
38	annarockmilano	annarockmilano	ann	nna	nar	aro	roc	ock	ckm	kmi	mil	ila	lan	ano
39	almarsrl	almaringrossoabbigliamento	alm	lma	mar
6112292

Cell Formulas
Range	Formula
C2:H2,C39:E39,C38:N38,C37:K37,C36:F36,C35:L35,C34:E34,C33:J33,C32:M32,C31:N31,C30:M30,C28:E29,C27:D27,C26:H26,C25:G25,C24:F24,C23:K23,C22:U22,C21:F21,C20:K20,C19:D19,C18:H18,C17:N17,C16:F16,C15:I15,C14:J14,C13:M13,C12:H12,C11:I11,C10:H10,C9:F9,C7:K8,C6:J6	C2	=LET(searchstr,A2,str,B2,strLen,LEN(B2),str3s,TRANSPOSE(MID(B2,SEQUENCE(strLen-2),3)),FILTER(str3s,ISNUMBER(SEARCH(str3s,searchstr,1))))
Dynamic array formulas.

It is a start...hope that helps,

Doug

 

[massi]

I know this does not meet your results, but this is part way there...the formula in column C returns the three letter strings (generated from column B) that are found in column A. I am still thinking about the approach to writing this in VBA. It is for sure possible since I was able to generate these results. I might not be able to get to that until later today if I am lucky.

Book_2023-10-03.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Column 1	Column 2
2	pantotexsrl	pantotex	pan	ant	nto	tot	ote	tex
3	hgisrl	abbigliamentohgi	hgi
4	modamilanosrl	modamilanosrl	mod	oda	dam	ami	mil	ila	lan	ano	nos	osr	srl
5	bluediffusionsrl	bluediffusion	blu	lue	ued	edi	dif	iff	ffu	fus	usi	sio	ion
6	centromodaruggierobyarconfabbigliamentosumisura	centromoda2	cen	ent	ntr	tro	rom	omo	mod	oda
7	lafogliasrl	lafogliasrl	laf	afo	fog	ogl	gli	lia	ias	asr	srl
8	franzeseabbigliamentosrl	franzeseabb	fra	ran	anz	nze	zes	ese	sea	eab	abb
9	euforiakajalproduzioneabbigliamentodonnabyfdpsrl	fdpsrl	fdp	dps	psr	srl
10	stigmatieltd	stigmati	sti	tig	igm	gma	mat	ati
11	ghirafsrl	ghirafsrl	ghi	hir	ira	raf	afs	fsr	srl
12	cucitex2srlindumentiusati	cucitex2011	cuc	uci	cit	ite	tex	ex2
13	produzioneeingrossoabbigliamentodonnamonicamorelli	monicamorelliofficial	mon	oni	nic	ica	cam	amo	mor	ore	rel	ell	lli
14	atelebabrideelenabarba	elenabarbasposa	ele	len	ena	nab	aba	bar	arb	rba
15	goldjeanssrl	goldjeans	gol	old	ldj	dje	jea	ean	ans
16	lauclecreazionidiguarinoanna	laucle	lau	auc	ucl	cle
17	machodiffusion2ndstreetshowroom	machodiffusion	mac	ach	cho	hod	odi	dif	iff	ffu	fus	usi	sio	ion
18	sitelliskinlaboutletrivadirenobologna	sitellishop	sit	ite	tel	ell	lli	lis
19	nuovaorizzontisrl	orimodasrl	ori	srl
20	soelservicesncditagliatielisaebenatisonia	soelservice	soe	oel	els	lse	ser	erv	rvi	vic	ice
21	ellebisrl	alanellebi	ell	lle	leb	ebi
22	plissettaturacaporalidivalmorritomasecsas	plissettaturacaporali	pli	lis	iss	sse	set	ett	tta	tat	atu	tur	ura	rac	aca	cap	apo	por	ora	ral	ali
23	confezionievi	eviconfezioni	evi	con	onf	nfe	fez	ezi	zio	ion	oni
24	deantonicarnia	dacarnia	car	arn	rni	nia
25	gheshandgoldsrl	commercialegheshgold	ghe	hes	esh	gol	old
26	implosedabbigliamentouomo	implosed	imp	mpl	plo	los	ose	sed
27	centrodeca	decaroma	dec	eca
28	dmyddmsrlu	dmsrl17	dms	msr	srl
29	aretesrl	aretemoda	are	ret	ete
30	ingrossoabbigliamentosportivoroma100x100cotone	100x100cotonesrls	100	00x	0x1	x10	100	00c	0co	cot	oto	ton	one
31	mpmartoranosrl	mpmartoranosrl	mpm	pma	mar	art	rto	tor	ora	ran	ano	nos	osr	srl
32	martinettireadivisescolasticheabitiprimacomunione	martinettirea	mar	art	rti	tin	ine	net	ett	tti	tir	ire	rea
33	lineaecostume	lineacostume	lin	ine	nea	cos	ost	stu	tum	ume
34	blive	blivepromozionale	bli	liv	ive
35	fashiontradesrl	fashiontrade	fas	ash	shi	hio	ion	ont	ntr	tra	rad	ade
36	mffsrlmattmodafashionfursrl	mffsrl	mff	ffs	fsr	srl
37	futurasasdirizzivitoec	futuravitorizzi	fut	utu	tur	ura	vit	ito	riz	izz	zzi
38	annarockmilano	annarockmilano	ann	nna	nar	aro	roc	ock	ckm	kmi	mil	ila	lan	ano
39	almarsrl	almaringrossoabbigliamento	alm	lma	mar
6112292

Cell Formulas
Range	Formula
C2:H2,C39:E39,C38:N38,C37:K37,C36:F36,C35:L35,C34:E34,C33:J33,C32:M32,C31:N31,C30:M30,C28:E29,C27:D27,C26:H26,C25:G25,C24:F24,C23:K23,C22:U22,C21:F21,C20:K20,C19:D19,C18:H18,C17:N17,C16:F16,C15:I15,C14:J14,C13:M13,C12:H12,C11:I11,C10:H10,C9:F9,C7:K8,C6:J6	C2	=LET(searchstr,A2,str,B2,strLen,LEN(B2),str3s,TRANSPOSE(MID(B2,SEQUENCE(strLen-2),3)),FILTER(str3s,ISNUMBER(SEARCH(str3s,searchstr,1))))
Dynamic array formulas.

It is a start...hope that helps,

Doug

Thank you Doug!

 

[massi]

Hi Dugg, thinking about it carefully, I think that starting from column A where spaces are also included, it is easier to search for those words in the corresponding cells in column B. I attach the example sheet, thanks!

Cartel1
	A	B
1	Column 1	Column 2
2	pantotex srl	pantotex
3	hgi srl	abbigliamentohgi
4	modamilano srl	modamilanosrl
5	blue diffusion srl	bluediffusion
6	centromodaruggiero by arconf abbigliamento su misura	centromoda2
7	la foglia srl	lafogliasrl
8	franzese abbigliamento srl	franzeseabb
9	euforiakajal produzione abbigliamento donna by fdp srl	fdpsrl
10	stigmati e ltd	stigmati
11	ghiraf srl	ghirafsrl
12	cucitex 2 srl indumenti usati	cucitex2011
13	produzione e ingrosso abbigliamento donnamonica morelli	monicamorelliofficial
14	ateleba bride elena barba	elenabarbasposa
15	gold jeans srl	goldjeans
16	laucle creazioni di guarino anna	laucle
17	macho diffusion 2ndstreetshowroom	machodiffusion
18	sitelli skinlaboutlet riva di reno bologna	sitellishop
19	nuova orizzonti srl	orimodasrl
20	soel service snc di tagliati elisa e benati sonia	soelservice
21	ellebi srl	alanellebi
22	plissettatura caporali di valmorri tomas e c sas	plissettaturacaporali
23	confezioni evi	eviconfezioni
Foglio1

 

[duggie33]

Fast solution for a return of words found...VBA to highlight will take me more time than I have at the moment.

Book_2023-10-03.xlsm
	A	B	C	D	E	F
1	Column 1	Column 2	Column 1 words found in Column 2
2	pantotex srl	pantotex	pantotex
3	hgi srl	abbigliamentohgi	hgi
4	modamilano srl	modamilanosrl	modamilano	srl
5	blue diffusion srl	bluediffusion	blue	diffusion
6	centromodaruggiero by arconf abbigliamento su misura	centromoda2
7	la foglia srl	lafogliasrl	la	foglia	srl
8	franzese abbigliamento srl	franzeseabb	franzese
9	euforiakajal produzione abbigliamento donna by fdp srl	fdpsrl	fdp	srl
10	stigmati e ltd	stigmati	stigmati
11	ghiraf srl	ghirafsrl	ghiraf	srl
12	cucitex 2 srl indumenti usati	cucitex2011	cucitex	2
13	produzione e ingrosso abbigliamento donnamonica morelli	monicamorelliofficial	e	morelli
14	ateleba bride elena barba	elenabarbasposa	elena	barba
15	gold jeans srl	goldjeans	gold	jeans
16	laucle creazioni di guarino anna	laucle	laucle
17	macho diffusion 2ndstreetshowroom	machodiffusion	macho	diffusion
18	sitelli skinlaboutlet riva di reno bologna	sitellishop	sitelli
19	nuova orizzonti srl	orimodasrl	srl
20	soel service snc di tagliati elisa e benati sonia	soelservice	soel	service	e
21	ellebi srl	alanellebi	ellebi
22	plissettatura caporali di valmorri tomas e c sas	plissettaturacaporali	plissettatura	caporali	e	c
23	confezioni evi	eviconfezioni	confezioni	evi
Sheet6

Cell Formulas
Range	Formula
C2:C3,C6,C8,C10,C16,C18:C19,C21,C23:D23,C22:F22,C20:E20,C17:D17,C11:D15,C9:D9,C7:E7,C4:D5	C2	=LET(searchwrds,TEXTSPLIT(A2," ",,TRUE,0,),searchstr,B2,IFERROR(FILTER(searchwrds,ISNUMBER(SEARCH(searchwrds,searchstr,1))),""))

Doug