Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel
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
Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel
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
Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel
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 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
Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel
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
$$Excel-Macros$$ A macro to parse a wordlist in Excel
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
