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. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel