topic Re: How to search a text within another column ? in Discussions

How to search a text within another column ?

LogitTurtle576 — Sat, 10 Jun 2023 23:44:38 GMT

Hi,

I can't find a solution for the following problem.

I have 2 columns, both containing text. I would like to create a new column to tell me if the text in column 2 can be found in column 1.

How can I do this ? I started in Excel but seems too complex.

Example :

Col1 is the whole list

Col2 are the text I want to find in col1.

Col3 will tell me if the text in the col2 on the same row can be found in the list in col1

col1	col2	col3
GCF055.TOP	FCN4222	not found
SCF015.DOWN	FCNV4222	found
SCFCNV422.TOP	FCNV4222FF	not found
SCF866.TOP	GCF009	found
SCGCF009.DOWN
SCF866.UP
SCF015.UP

Thanks for your help.

Hope being clear enough in my question.

Re: How to search a text within another column ?

txnelson — Wed, 23 Feb 2022 10:25:31 GMT

Here is one way to approach this, using a column formula. Create a Character column and then paste this into the formula for the column.

x = "not found";
For( i = 1, i <= N Rows( Current Data Table() ), i++,
	If( Contains( :col1[i], :col2[Row()] ),
		x = "found";
		Break();
	)
);
x;

Please note, your sample data table's entry for row 2 is incorrect. FCNV4222 is not found in any of the rows for co1. Row 3 value contains FCNV422 but does not contain FCNV4222

Re: How to search a text within another column ?

jthi — Wed, 23 Feb 2022 11:02:14 GMT

There are many many ways to do this, solution will differ depending on if high performance is required or not. Below is one example using associative array to get unique values, concat items to make string to compare to and then Contains to look for matches.

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(7),
	New Column("col1",
		Character,
		"Nominal",
		Set Values({"GCF055.TOP", "SCF015.DOWN", "SCFCNV422.TOP", "SCF866.TOP", "SCGCF009.DOWN", "SCF866.UP", "SCF015.UP"})
	),
	New Column("col2", Character(16), "Nominal", Set Values({"FCN4222", "FCNV4222", "FCNV4222FF", "GCF009", "", "", ""})),
	New Column("col3", Character(16), "Nominal", Set Values({"not found", "found", "not found", "found", "", "", ""}))
);


dt << New Column("Found", Character, Nominal, << Formula(
	If(Row() == 1,
		col_vals = Associative Array(:col1 << get values) << get keys; // to eliminate dublicates
		search_str = Concat Items(col_vals, " "); // should use some character not found as separator
	);
	If(!IsMissing(:col2),
		If(Contains(search_str, :col2),
			"found",
			"not found"
		);
	,
		""
	);
));

Re: How to search a text within another column ?

LogitTurtle576 — Wed, 23 Feb 2022 12:01:35 GMT

Many thanks for your quick reply.

Note : you are right. My example was not completely correct.

Re: How to search a text within another column ?

LogitTurtle576 — Wed, 23 Feb 2022 12:02:03 GMT

Thanks for your quick reply.