I have 2 lists of words in Excel. List A has 3000 words: 3-6 words in
each cell. These words are used for writing children's stories.

List B has 400 words: one word per cell. These words are those words
which have already been used in writing. Thus, the words in List B are
all present in List A.

To illustrate:
LIST A (several words in one cell)
cat, dog, cow
bread, fish, egg, jam
hand mouth, eye, finger

LIST B (one word in one cell)
dog
cow
fish
eye
finger

The writers need to know which words in List A have been used up.  But
the writers do not want to refer to List B.

QUESTION:
Suggest a way (eg an Excel function or by writing a macro) to mark off
common words (words in both lists) by, say, changing the color of the
font of words in List A or changing the font to strikethrough. Please
suggest a SIMPLE METHOD.

---

Clarification of Question by aaz-ga on 16 Oct 2003 21:20 PDT

Many thanks for your responses and suggestions.

The list is huge and I don't know where to upload it in order for you
to download it. (Also, the list is kind of proprietary and hence
confidential.) Pl see if this helps.
Kindly construct two tiny spreadsheets with the help of the following:

LIST A
A1 = Heading1
A2 = Heading1

B1 = Heading1A
B2 = Heading1B

C1 = leg
C2 = head, hair, face, eye, ear, nose, mouth,

D1 = arm, shoulder, neck, tail,
D2 = lip, brain, tooth, throat,

E1 = joint

F1 = chest, breast, muscle, stomach, bone,

[Notes: Column A and Column B are headings. The words are in column C,
D, E and F.
In this sample Cell E2 and F2 are blank. LIST A is arranged in about
1000 rows.]

LIST B
ear
nose
hair
tail

Please keep the purpose of this exercise in mind. 
When writers look at LIST A, they would know which words have been
used up so that they can use other unused words in writing the
material. The words used up will go to LIST B. Thus, LIST B will keep
expanding. Then through the macro, the words used up will have to be
marked off again in LIST A. That is, the process will have to be
repeated time and again as the writing progresses.

LIST A's structure will remain unchanged ie, I do not want the words
in LIST A to go in one column etc. The only change in LIST A should be
change of colors (or fonts, say strikethrough font) for words used up.

Once you write the macro, through some guidance from you, I can modify
it to customize it for my purpose.

Best regards

If you can post the .xls file somewhere on the internet, I can write a
macro that will do the required task. Its hard to write a macro
without having the workbook. You could probably do it with worksheet
functions but it would get a little messy. I think a macro would be
better and provide more flexibility (i.e. it will work if the list is
expanded). -BW

If you have a fair understanding of Excel's (intermediate) functions,
this suggestion should work (without the use of macro's), but could be
a little messy as blinkwilliams has suggested.

Copy List A from Excel to a simple text (.txt) file in Notepad (I'm
assuming your using Windows).  Save that to a temporary file (.txt). 
Then open that text file in Excel.  Excel will prompt you to answer
some questions on how to open the file.

1. Choose "next"
2. Select your delimiter.  In this case your words are separated by a
comma, so choose "comma"
3. Click "next", then "finish"

Excel will place each of your words into separate cells.

4. Now move all of your words into one Column (i.e. contents of column
B to column A, column C to column A, etc.) so that all your words are
'stacked' one, below the other.

5. Trim off any extraneous spaces by using the =trim function (place
answers in column B).

6.  Use the =vlookup function to compare column B with List B (let's
say this information is in Column D).

7.  All the information from column B 'matches' to column D (List B)
will display with the lookup function, which will be the words that
have 'been used up.'

A bit of work, but this should work.  

Hope that helps.