How do I find duplicate words in a Numbers spreadsheet?

It's easy enough to find and count duplicate entries, especially if they are all in a single column, but if you want to count individual words within multi word entries, the problem is more complicated.

Here's an example for the 'simple' case, using a familiar 155 word passage from English literature.

The words in the passage are separated into a single line for each word, and stripped of all punctuation. This may be done in Pages, or in Text edit, or in pretty much any word processing software or text editor. Paste the passage into a WP or text document, then use the application's Find/Replace feature to replace all of the spaces with returns. I would also do a second pass replacing all double returns with single returns, then repeat that until Find/Replace reported replacing zero occurrences.

Punctuation was stripped using Find/Replace

The prepared list was pasted into column A of a Numbers table.

Formulas:

B2: =COUNTIF($A$1:A2,A2)

C2: =IF(AND(B>1,B=COUNTIF($A,A2)),ROW()-1,999999)

Fill both down their respective columns to the end of the table.

The small table is inserted as a Basic table using the Tables button. It contains a Header row, but no Header columns.

Formula:

A2: =IFERROR(OFFSET(Table 1 :: $A$1,SMALL(Table 1 :: $C,ROW()-1),COLUMN()-1),"")

Fill right into B2, then fill both down to the end of the table.

Descriptions of the functions used, along with their syntax and at least one example of their use in a table are available in the Numbers '09 User Guide, which may be downloaded via the Help menu in Numbers.

Regards,

Barry

PS: Regards to the late Mr. Shakespeare, and thank you for providing the text used.

If your word list is laid out differently, a screen shot would be useful toward providing an alternate solution.

Regards,

Barry