Extract the most common words taken N at a time

I have a single column where each cell contains multiple words in any order.

I need to find which group of N words in a cell exist in the greatest number of rows (or cells for this 1-column case).

Example: (using letters for simplicity)
In the data below, each row of letters is one cell.

I want to determine which 3 letters in a cell exist in the greatest number of cells?
I believe B, E, and F (rows 1, 3 and last row).
Edit: (also B, F, H in rows 1, 4 and last row - discovered later)

F H E B A
D B A G
E B A F C
B H C F
C G D
D F A H
G E F D
C B H D A
B H F E D

Like this example, each cell contains only a few words (maybe 10 max), but there are many different words and hundreds of rows, however all the words could be extracted and individually listed if necessary, e.g. used in a lookup table, separate array, etc.

Any insights appreciated!

Hi Aldrin,

Try looking at Advanced Filter feature of Excel with wildcard usage. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi

I have probably made this far more complex and ugly than it needs to be, but it seems to work. The trick seems to be to eliminate words that appear infrequently to reduce the word list to a manageable level. If all words occur with about the same frequency this will not work.

You need to have your source words in a worksheet named "source" and a separate blank sheet in the workbook called "words" to list and manipulate data.

the macros should run in the order listed (or the macro a_all_macros will run them for you):

a_words_list
copies each word in the cells in sheet "source" to a separate row in sheet "words"

b_sort_words
sorts them alphabetically

c_count_frequencies
counts the frequencies with which each word occurs

d_delete_duplicates
deletes all words that occur only once (these can't be the most frequent) and all duplicates, leaving just one instance of each word in the list

e_sort_by_frequency
sorts the remaining words by descending order of frequency

f_find_most_frequent_delete_others
for the top 3 most frequent words, counts the number of occasions on which all three words occur in one cell in sheet "source". Deletes all words that occur less frequently than this (they cannot be in the most frequent 3)

hopefully, this will reduce the list of words to a manageable length (20 or less words). Otherwise, it will probably not work.

g_check_nos
If so, the final macro determines all permutations of 3 words in the list and checks their frequency in sheet “source”, returning the most frequent

Please Login or Register  to view this content.

Hi

Slightly different approach. This will allow you to nominate the number of words in your groupings. I've only programmed for 3 and 4 word combinations just so you get the idea.

I've also hard coded the range to process - again just to give you an idea. This can be automated, but the output will have to be put into a different place.

Please Login or Register  to view this content.

Using the above for a 3 word group, I noticed that F, B and H also have 3 rows...

rylo