Hi again,
If I input this list to be processed by the macro:
abnormal termination
abnormal
abort
about
absolute
absolute address
absolute code
accent
access
access address
access mode
access control
access time
address
The case with "address" will be handled as follows:
address - absolute address|access address
Here both phrases contain the word "address", therefore the two must
be put next to "address"
What I mean is the cases where we have a phrase of 2, 3 or more words
where some of the words of the phrases is not present in the list.
If we have the following list:
abnormal termination
abnormal
abort
about
absolute
absolute address
absolute code
accent
access
access address
access mode
access control
access time
It doesn't contain the word "address" on its own. In this case, the
phrases "absolute address" and "access address" must be processes as
follows:
absolute address - absolute|address
1. The phrase must be split - i.e. the character "|" must be put in
the places of the spaces.
2. The macro must check if easch word of the split phrase exists in
the list on its own - i.e. if "absolute" exists in the list, then the
whole phrase "absolute address" must be put in the column next to it:
absolute address - absolute|address
absolute - absolute address
3. Then the macro must check the next word in the split phrase (in
this case "address"). In the list above, it doesn't exist. All these
cases must be listed in Sheet2. The word "address" in column A, and
the phrase from which it comes - in column B.
address - absolute address
4. The macro continues to process the list in Sheet1 and if it comes
accross a similar record (e.g. "access address"), it splits the phrase
(access address - access|address), copies the phrase next to its
constituent words that exist in the list (access - access address),
and copies the phrase in Sheet2 when its constituent word doesn't
exist in the list (address - absolute address|access address => in
Sheet2 "address - absolute address" already exists, it was created
when the phrase "absolute address" is created, therefore "access
address" is just added next to "absolute address" separated by "|").
Thank you very much for helping me with this task!
On May 14, 8:10 am, ashish koul <koul.ash...@gmail.com> wrote:
> 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 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
