Search for word and sum count

[123love55448]

Set	Word 1	Word 2	Word 3
A	ablation	Cat	Equation
B	That	Cattle	Slow
C	lotion	Creative	Stock

I want to search in each column for word that contains "at" and want the output to summarize number of words in each column that contains "at"

Word 1	2
Word 2	3
Word 3	1

Appreciated for all the helps :) 

[Luke_C]

Hi @123love55448 

Transpose the data, filter on records that contain 'at' and summarize:

[IraWatt]

Hey @123love55448,

Here is one way to do this:

I put all the Word columns on one line and use a contains function to fill a column with 1 if it contains at. Then I just group by words and sum the number of 1's.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[mceleavey]

Hi @123love55448 ,

this is a simple Find and Replace function.

I've built it so you can change the input you want to find in the word:

You can simply pivot the data using the transpose tool, which gives you the words in a single column.

You can then use the Find and Replace tool to find "at" in any part of the word in the Name column.

Then you can use the sum tool to get the count of non-null records.

I hope this helps,

M.