what in case of address
which one it should pic
absolute address access address
On Fri, May 13, 2011 at 8:42 PM, Ivaylo <ivanov.iva...@gmail.com> wrote:
> 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
>
--
*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.
--
----------------------------------------------------------------------------------
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
