Hi Ashish,
Thank you very much for your efforts!
Your macro is just what I needed.
However, it seems that it doesn't handle the exceptions:
===========================================
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").
===========================================
Is it possible for you to add this functionality?
On May 13, 4:26 pm, ashish koul <koul.ash...@gmail.com> wrote:
> try this code
>
> Sub tests()
> Dim i As Long
>
> Dim z As String
>
> For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
>
> If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") > 0 Then
>
> Cells(i, 2) =
> Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim(Cells(i,
> 1)), " ", "|")
>
> End If
>
> Next i
>
> For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
>
> z = ""
> If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") = 0 Then
>
> For swa = 1 To Sheets(1).Range("a65356").End(xlUp).Row
>
> If swa <> i Then
>
> If InStr(1, Application.WorksheetFunction.Trim(Cells(swa, 1)),
> Application.WorksheetFunction.Trim(Cells(i, 1))) > 0 Then
>
> z = z & Application.WorksheetFunction.Trim(Cells(swa, 1)) & "|"
>
> End If
>
> End If
>
> Next swa
>
> If Right(z, 1) = "|" Then
> Cells(i, 2) = Left(z, Len(z) - 1)
> Else
>
> Cells(i, 2) = z
> End If
>
> End If
>
> Next i
>
> End Sub
>
>
>
>
>
> On Wed, May 11, 2011 at 6:03 PM, Ivaylo <ivanov.iva...@gmail.com> 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.
>
> > --
>
> > ----------------------------------------------------------------------------------
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
> P Before printing, think about the environment.- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
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
