I have some spreadsheets where there are large number of cells that have essentially been used for free text.

There is a finite set of values for this free text and most, if not all repeat.

eg.

   A        B       C       D
1  Monkey   Gorilla Cat     Dog
2  Dog      Cat     Gorilla Gorilla
3  Dog      Dog     Dog     Cat

There are probably 50 or so different cell values spread over multiple sheets and hundreds of rows and columns.

I need to analyse this data and count occurancies, which is not a problem other than getting a list of unique values to start with and this has been driving me up the wall.

What is the best way to produce this list.

So from the above we would have

Monkey
Dog
Cat
Gorilla

In order of preferred solutions, as this will need to be done monthly.

1. Dynamic formula based
2. VB Script
3. Other ( Advanced filtering or other manual steps )