Hi to all VBA gurus,

I need help with an Excel macro which can do the following:

I have a long list of dictionary headwords in column A (sorted
alphabetically), something like this:

abnormal termination
abnormal
abort
about
absolute
absolute address
absolute code
accent
access
access address
access mode
access control
access time
address

....

The list consists of single words and multiword phrases.

My task is to put in column B reference to the single words that
constitute the multiword phrases. For example, for "abnormal
termination" column B must be populated with "abnormal" and
"termination", separated by "|":
abnormal termination abnormal|termination

The second part of this task is that for the single-word headwords I
must put references to their corresponding multiword phrases. For
example, for "absolute" column B must be populated with "absolute
address" and "absolute code", separated by "|":
absolute absolute address|absolute code


Here is a desired output after the processing:


Column A - Column B
abnormal - termination abnormal|termination
abnormal - abnormal termination
abort -
about -
absolute - absolute address|absolute code
absolute address - absolute|address
absolute code - absolute|code
accent -
access access - address|access mode|access control|access time
access address - access|address
access mode - access|mode
access control - access|control
access time - access|time
address - absolute address|access address

How to handle exceptions:

If the consituent word doesn't have its own headword in the list, then
the missing word must be copied in Sheet 2, column A and the phrase
from which it comes from in column B - i.e. Sheet 2 must be list all
the words which are part of a multiword phrases but don't have their
own headwords.

This is the case with "abnormal termination". The word "abnormal"
exists as a headword, and the phrase "abnormal termination" is copied
in column B next to abnormal. However, the word "termination" doesn't
exist in column A (as a headword), then "termination" must be copied
to column A in Sheet 2 and the phrase "abnormal termination" to column
B in Sheet 2 (next to "termination").

I'll be very grateful if anyone can help me writing such a macro.

On May 11, 7:35*am, Ivaylo wrote:
Hi to all VBA gurus,

I need help with an Excel macro which can do the following:

I have a long list of dictionary headwords in column A (sorted
alphabetically), something like this:

abnormal termination
abnormal
abort
about
absolute
absolute address
absolute code
accent
access
access address
access mode
access control
access time
address

...

The list consists of single words and multiword phrases.

My task is to put in column B reference to the single words that
constitute the multiword phrases. For example, for "abnormal
termination" column B must be populated with "abnormal" and
"termination", separated by "|":
abnormal termination abnormal|termination

The second part of this task is that for the single-word headwords I
must put references to their corresponding multiword phrases. For
example, for "absolute" column B must be populated with "absolute
address" and "absolute code", separated by "|":
absolute absolute address|absolute code

Here is a desired output after the processing:

Column A - Column B
abnormal - termination abnormal|termination
abnormal - abnormal termination
abort -
about -
absolute - absolute address|absolute code
absolute address - absolute|address
absolute code - absolute|code
accent -
access access - address|access mode|access control|access time
access address - access|address
access mode - access|mode
access control - access|control
access time - access|time
address - absolute address|access address

How to handle exceptions:

If the consituent word doesn't have its own headword in the list, then
the missing word must be copied in Sheet 2, column A and the phrase
from which it comes from in column B - i.e. Sheet 2 must be list all
the words which are part of a multiword phrases but don't have their
own headwords.

This is the case with "abnormal termination". The word "abnormal"
exists as a headword, and the phrase "abnormal termination" is copied
in column B next to abnormal. However, the word "termination" doesn't
exist in column A (as a headword), then "termination" must be copied
to column A in Sheet 2 and the phrase "abnormal termination" to column
B in Sheet 2 (next to "termination").

I'll be very grateful if anyone can help me writing such a macro.

A macro can be developed to do this.
"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."