I have 78 discrete cells over 13 tabs (6 @12x12tables per sheet). The information would be taken from the same relative position in each table. For example, R2C3. At any given time some of the cells are blank and some are filled with text only. I would like to do two things:

1. combine all the words into a list in one cell leaving just a comma or char(10) between each word. The empty cells would be eliminated, but I would like to be able to still reference which table the word actually came from (example tab 4, table 5). The text could be names one time or objects the next time. (eg. apple, pear, banana)

The result would be a list of words; apple^banana^^^^^pear^^^apple^ banana converted to apple,banana,pear,apple,banana,...

2. Determine how many times the word appears in this group of cells and then reference which tables the word appears. For example, apple appears in tab 4, table 5. It also appears in tab 7, table 3 & 6.

The result would be listed in a 12x12 titled table (apple) with same layout as the others that gave the references to the tables where (apple) appeared in the cell R2C3--eg: T4-5, T7-3, 7-6. This would save having to manually check each table every time a change occurs in one table.

Any suggestions as to the most efficient way to begin to approach these challenges.

I thought it would be quickest to concatenate the cells with a "^" between each word then substitute the multiple "^"for single characters (not quite sure the syntax to do in just 2 steps)

Then I thought I'd return to the concatenated string with the "^" and count "^'s" to reference the tablea (#'d 1-78) but not sure of the syntax that would be most efficient there either.

Thanks in advance, all!
Calman