How to count occurrence of each sentence in Excel in this specific case?

Question

The challenge here is the sentence is not split by cell. They are in the same column, but they might appear in the same cell. One sentence per line.

I need to count the occurrence of each sentence, for example, occurrence of "The cat is pink" is 2 and occurrence of "The dog is green" is 1.

I can also do Access 2016 if needed.

What have you tried so far? Are you looking for the number of occurrences of some sentences, or all of them? If the latter, do you have a list of unique sentences? What determines a new sentence within a cell, a carriage return? — cybernetic.nomad, Aug 14 '18 at 18:10
I am looking for the number of occurrences of all of them. I do not have a list of unique sentences. A carriage return determines a new sentence. — Ice s. Woo, Aug 14 '18 at 18:32

score 1 · Accepted Answer · answered Aug 14 '18 at 19:02

(Assuming you can split multi-sentence cells into multiple cells)

1) Split the cells with multiple sentences, you should be able to adapt this code to do this.

2) Make a copy of the column (elsewhere on the same sheet or in another sheet -- used column B in the same sheet),

3) Remove duplicate values for the copied column

4) Next to the column use the following array formula:

{=SUM(LEN(A$1:A$5)-LEN(SUBSTITUTE(A$1:A$5,B1,"")))/LEN(B1)}

(press <CTRL><SHIFT><ENTER> when entering an array formula)

score 1 · Answer 2 · answered Aug 14 '18 at 19:41

1

sort column A --> a-z then add a header to column A (to use in subtotals) then from the Data tab, use "subtotals" using "count" as the function

answered Aug 14 '18 at 19:41

candice

score 0 · Answer 3 · answered Aug 14 '18 at 18:14

0

=COUNTIF(A:A,"*Cat is Yellow*")

The same formula can be applied for all the rows.

answered Aug 14 '18 at 18:14

Ramji

3 Answers3