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.

Answer 1

(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)