Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hello All Experts,
I am using Office XP and have the following problem: I have data in Col AA for examples as follows: AA1 =Text 1 = eg John AA2 =Text 2 = eg Johnny AA3 =Text 3 = eg James AA4 = Text 4 = eg Henry AA5 = Text 5 = eg George ..... ..... a range of unique Text Strings in AA1:AA25 I wish to select the Col A and run the macro. The macro should look up the value in the above range in Col AA and in corresponding ColB it should have the value extracted. for eg. A1 = Abraham John Joseph -- then B2 should have the value John only (as it is in the range in Col AA) A2 = Henry the II -- then B2 should have the value Henry only A3 = The Junior George -- then B2 will have the value George only In other words Col B should have atleast one value from the String in AA1 if found otherwise it should be blank Pls note that no two words are repeated in the String in Col A for eg.. there are no two Johns no two George and so on Can this be achieved thru VBA or a formula. Any suggestions are requrested Many thanks in advance Rashid Khan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi Rashid -
I guess the question is whether the name from column AA has to be an exact full-word match of one of the individual names (i.e. first, middle, last) within a name in column A. In other words, if column A has "Danielle Smith", and column AA has "Daniel", is that considered a match? Or does the name in AA have to be "Danielle"? If you don't need a full-word match, you can just use the InStr function to tell whether a name from AA is a substring of a name from column A. You'll do a nested loop: outer loop iterates through each name in column A. Inner loop iterates through each name in column AA, looking for a match (where the InStr function returns a value greater than zero). When a match is found, the name from column AA is copied to column B. It becomes more complicated if you need an exact match of the full word. In that case, you'll probably want to write a separate function which takes a string (which will be read from column A), and fills an array of strings with the individual words within that string, as separated by spaces. Then you'll have three loops - the same two as described above, plus an innermost loop which iterates through each of the individual words (names) looking for an exact match of the name from column AA. Does that make sense? If you take a first shot at writing some of the code and get stuck, please post a reply with your code and I'll look through it. Mark "Rashid Khan" wrote in message ... Hello All Experts, I am using Office XP and have the following problem: I have data in Col AA for examples as follows: AA1 =Text 1 = eg John AA2 =Text 2 = eg Johnny AA3 =Text 3 = eg James AA4 = Text 4 = eg Henry AA5 = Text 5 = eg George .... .... a range of unique Text Strings in AA1:AA25 I wish to select the Col A and run the macro. The macro should look up the value in the above range in Col AA and in corresponding ColB it should have the value extracted. for eg. A1 = Abraham John Joseph -- then B2 should have the value John only (as it is in the range in Col AA) A2 = Henry the II -- then B2 should have the value Henry only A3 = The Junior George -- then B2 will have the value George only In other words Col B should have atleast one value from the String in AA1 if found otherwise it should be blank Pls note that no two words are repeated in the String in Col A for eg.. there are no two Johns no two George and so on Can this be achieved thru VBA or a formula. Any suggestions are requrested Many thanks in advance Rashid Khan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi Mark,
Thanks for your reply. I am answering he I guess the question is whether the name from column AA has to be an exact full-word match of one of the individual names (i.e. first, middle, last) within a name in column A. In other words, if column A has "Danielle Smith", and column AA has "Daniel", is that considered a match? Or does the name in AA have to be "Danielle"? No it has to just match eg Danielle Smith... in AA then Danielle or Daniel would do. However I do not have any such strings in AA. If you don't need a full-word match, you can just use the InStr function to tell whether a name from AA is a substring of a name from column A. You'll do a nested loop: outer loop iterates through each name in column A. Inner loop iterates through each name in column AA, looking for a match (where the InStr function returns a value greater than zero). When a match is found, the name from column AA is copied to column B. Yes this is what would suffice for my requirement. As far as writing the code ... I am a very very new to macro programming. This NG has been a tremendous help for me and I am sorry that I do not know how to code what u have explained. Rashid Khan "Mark Thorpe" wrote in message ... Hi Rashid - I guess the question is whether the name from column AA has to be an exact full-word match of one of the individual names (i.e. first, middle, last) within a name in column A. In other words, if column A has "Danielle Smith", and column AA has "Daniel", is that considered a match? Or does the name in AA have to be "Danielle"? If you don't need a full-word match, you can just use the InStr function to tell whether a name from AA is a substring of a name from column A. You'll do a nested loop: outer loop iterates through each name in column A. Inner loop iterates through each name in column AA, looking for a match (where the InStr function returns a value greater than zero). When a match is found, the name from column AA is copied to column B. It becomes more complicated if you need an exact match of the full word. In that case, you'll probably want to write a separate function which takes a string (which will be read from column A), and fills an array of strings with the individual words within that string, as separated by spaces. Then you'll have three loops - the same two as described above, plus an innermost loop which iterates through each of the individual words (names) looking for an exact match of the name from column AA. Does that make sense? If you take a first shot at writing some of the code and get stuck, please post a reply with your code and I'll look through it. Mark "Rashid Khan" wrote in message ... Hello All Experts, I am using Office XP and have the following problem: I have data in Col AA for examples as follows: AA1 =Text 1 = eg John AA2 =Text 2 = eg Johnny AA3 =Text 3 = eg James AA4 = Text 4 = eg Henry AA5 = Text 5 = eg George .... .... a range of unique Text Strings in AA1:AA25 I wish to select the Col A and run the macro. The macro should look up the value in the above range in Col AA and in corresponding ColB it should have the value extracted. for eg. A1 = Abraham John Joseph -- then B2 should have the value John only (as it is in the range in Col AA) A2 = Henry the II -- then B2 should have the value Henry only A3 = The Junior George -- then B2 will have the value George only In other words Col B should have atleast one value from the String in AA1 if found otherwise it should be blank Pls note that no two words are repeated in the String in Col A for eg.. there are no two Johns no two George and so on Can this be achieved thru VBA or a formula. Any suggestions are requrested Many thanks in advance Rashid Khan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi Rashid -
This code should work for you. I tried to put in plenty of comments to help you learn more about Excel VBA: Sub SearchNames() Dim lRowCount As Long Dim lRow As Long Dim iRowAA As Integer Dim sFullName As String Dim sMatch As String ' match found in column AA ' Count number of used rows in the sheet: lRowCount = ActiveSheet.UsedRange.Rows.Count ' Outer loop: look at each name in column A: For lRow = 1 To lRowCount sFullName = Cells(lRow, 1).Value iRowAA = 1 sMatch = "" ' Inner loop: look at each name in column AA ' until either a match is found, or run out ' of names: While Cells(iRowAA, 27).Value < "" And sMatch = "" ' InStr function returns value greater than ' zero if string2 is found within string1: If InStr(sFullName, Cells(iRowAA, 27).Value) 0 Then sMatch = Cells(iRowAA, 27).Value End If iRowAA = iRowAA + 1 Wend ' Place the match in column B: Cells(lRow, 2).Value = sMatch Next lRow End Sub Also, you might take a look at a series of articles called "Excel VBA: No Experience Necessary" on TechTrax online magazine. I'm not trying to advertise, but you might find them helpful. http://pubs.logicalexpressions.com/P...ssue.asp?ISI=0 Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks Function to extract values
Hi Mark,
Wow... U r a real magician... The macro ran on 46000 rows in 6 minutes ... Thanks a lot.. I may have some practical problems after running this macro..but I will have to see what it is... I will post back if any further help is required. Thanks a lot for all your help and the wonderful URL u have sent. I will sure go thru it. Rashid Khan "Mark Thorpe" wrote in message ... Hi Rashid - This code should work for you. I tried to put in plenty of comments to help you learn more about Excel VBA: Sub SearchNames() Dim lRowCount As Long Dim lRow As Long Dim iRowAA As Integer Dim sFullName As String Dim sMatch As String ' match found in column AA ' Count number of used rows in the sheet: lRowCount = ActiveSheet.UsedRange.Rows.Count ' Outer loop: look at each name in column A: For lRow = 1 To lRowCount sFullName = Cells(lRow, 1).Value iRowAA = 1 sMatch = "" ' Inner loop: look at each name in column AA ' until either a match is found, or run out ' of names: While Cells(iRowAA, 27).Value < "" And sMatch = "" ' InStr function returns value greater than ' zero if string2 is found within string1: If InStr(sFullName, Cells(iRowAA, 27).Value) 0 Then sMatch = Cells(iRowAA, 27).Value End If iRowAA = iRowAA + 1 Wend ' Place the match in column B: Cells(lRow, 2).Value = sMatch Next lRow End Sub Also, you might take a look at a series of articles called "Excel VBA: No Experience Necessary" on TechTrax online magazine. I'm not trying to advertise, but you might find them helpful. http://pubs.logicalexpressions.com/P...ssue.asp?ISI=0 Mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi Mark,
There is a slight problem with this macro.. It is finding everything with the InStr Function... I need to find the string from within a string but it should from the beginning of a word.. maybe the macro can search a space also. Let me give u an example My list has Mahad so it is displaying from Gurumahadeo as Mahad... note that mahad is within this word Gurumahadeo. The example u quoted earlier about Daniel and Danielle was ok with me as long as it would extract the whole word from the beginning.. this macro would extract Dan, Dani and Niel also. How can we rectify this problem? Any suggestion would be appreciated. Rashid Khan "Mark Thorpe" wrote in message ... Hi Rashid - This code should work for you. I tried to put in plenty of comments to help you learn more about Excel VBA: Sub SearchNames() Dim lRowCount As Long Dim lRow As Long Dim iRowAA As Integer Dim sFullName As String Dim sMatch As String ' match found in column AA ' Count number of used rows in the sheet: lRowCount = ActiveSheet.UsedRange.Rows.Count ' Outer loop: look at each name in column A: For lRow = 1 To lRowCount sFullName = Cells(lRow, 1).Value iRowAA = 1 sMatch = "" ' Inner loop: look at each name in column AA ' until either a match is found, or run out ' of names: While Cells(iRowAA, 27).Value < "" And sMatch = "" ' InStr function returns value greater than ' zero if string2 is found within string1: If InStr(sFullName, Cells(iRowAA, 27).Value) 0 Then sMatch = Cells(iRowAA, 27).Value End If iRowAA = iRowAA + 1 Wend ' Place the match in column B: Cells(lRow, 2).Value = sMatch Next lRow End Sub Also, you might take a look at a series of articles called "Excel VBA: No Experience Necessary" on TechTrax online magazine. I'm not trying to advertise, but you might find them helpful. http://pubs.logicalexpressions.com/P...ssue.asp?ISI=0 Mark |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi Rashid -
You should be able to check to see if the substring is at the beginning of the longer string (InStr will return 1), and, if not, check to see if it appears preceded by a space: If InStr(sFullName, Cells(iRowAA, 27).Value) = 1 Then ' occurs at beginning sMatch = Cells(iRowAA, 27).Value ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0 Then ' occurs preceded by space sMatch = Cells(iRowAA, 27).Value End If "Rashid Khan" wrote in message ... Hi Mark, There is a slight problem with this macro.. It is finding everything with the InStr Function... I need to find the string from within a string but it should from the beginning of a word.. maybe the macro can search a space also. Let me give u an example My list has Mahad so it is displaying from Gurumahadeo as Mahad... note that mahad is within this word Gurumahadeo. The example u quoted earlier about Daniel and Danielle was ok with me as long as it would extract the whole word from the beginning.. this macro would extract Dan, Dani and Niel also. How can we rectify this problem? Any suggestion would be appreciated. Rashid Khan "Mark Thorpe" wrote in message ... Hi Rashid - This code should work for you. I tried to put in plenty of comments to help you learn more about Excel VBA: Sub SearchNames() Dim lRowCount As Long Dim lRow As Long Dim iRowAA As Integer Dim sFullName As String Dim sMatch As String ' match found in column AA ' Count number of used rows in the sheet: lRowCount = ActiveSheet.UsedRange.Rows.Count ' Outer loop: look at each name in column A: For lRow = 1 To lRowCount sFullName = Cells(lRow, 1).Value iRowAA = 1 sMatch = "" ' Inner loop: look at each name in column AA ' until either a match is found, or run out ' of names: While Cells(iRowAA, 27).Value < "" And sMatch = "" ' InStr function returns value greater than ' zero if string2 is found within string1: If InStr(sFullName, Cells(iRowAA, 27).Value) 0 Then sMatch = Cells(iRowAA, 27).Value End If iRowAA = iRowAA + 1 Wend ' Place the match in column B: Cells(lRow, 2).Value = sMatch Next lRow End Sub Also, you might take a look at a series of articles called "Excel VBA: No Experience Necessary" on TechTrax online magazine. I'm not trying to advertise, but you might find them helpful. http://pubs.logicalexpressions.com/P...ssue.asp?ISI=0 Mark |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi Mark,
Pardon me for my ignorance. But the suggestion u sent does not get the result what I want. Let me explain to u further. The strings in Col AA appear either at the beginning in Col A or in between somewhere always preceded by a space in Col A. The suggestion u posted searches for either in the beginning with the value = 1 but the 0 parts extracts part of the string as I already mentioned in my previous post. Hope u get it now Rashid "Mark Thorpe" wrote in message ... Hi Rashid - You should be able to check to see if the substring is at the beginning of the longer string (InStr will return 1), and, if not, check to see if it appears preceded by a space: If InStr(sFullName, Cells(iRowAA, 27).Value) = 1 Then ' occurs at beginning sMatch = Cells(iRowAA, 27).Value ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0 Then ' occurs preceded by space sMatch = Cells(iRowAA, 27).Value End If "Rashid Khan" wrote in message ... Hi Mark, There is a slight problem with this macro.. It is finding everything with the InStr Function... I need to find the string from within a string but it should from the beginning of a word.. maybe the macro can search a space also. Let me give u an example My list has Mahad so it is displaying from Gurumahadeo as Mahad... note that mahad is within this word Gurumahadeo. The example u quoted earlier about Daniel and Danielle was ok with me as long as it would extract the whole word from the beginning.. this macro would extract Dan, Dani and Niel also. How can we rectify this problem? Any suggestion would be appreciated. Rashid Khan "Mark Thorpe" wrote in message ... Hi Rashid - This code should work for you. I tried to put in plenty of comments to help you learn more about Excel VBA: Sub SearchNames() Dim lRowCount As Long Dim lRow As Long Dim iRowAA As Integer Dim sFullName As String Dim sMatch As String ' match found in column AA ' Count number of used rows in the sheet: lRowCount = ActiveSheet.UsedRange.Rows.Count ' Outer loop: look at each name in column A: For lRow = 1 To lRowCount sFullName = Cells(lRow, 1).Value iRowAA = 1 sMatch = "" ' Inner loop: look at each name in column AA ' until either a match is found, or run out ' of names: While Cells(iRowAA, 27).Value < "" And sMatch = "" ' InStr function returns value greater than ' zero if string2 is found within string1: If InStr(sFullName, Cells(iRowAA, 27).Value) 0 Then sMatch = Cells(iRowAA, 27).Value End If iRowAA = iRowAA + 1 Wend ' Place the match in column B: Cells(lRow, 2).Value = sMatch Next lRow End Sub Also, you might take a look at a series of articles called "Excel VBA: No Experience Necessary" on TechTrax online magazine. I'm not trying to advertise, but you might find them helpful. http://pubs.logicalexpressions.com/P...ssue.asp?ISI=0 Mark |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Note that in the line:
ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0 we are searching for the value from column AA preceded by a space. (thus the " " &) For example, it will not find "mahad" in "Gurumahadeo", because it first checks to see whether Gurumahadeo begins with mahad (does not), then searches for " mahad", which it does not find because of the space added to the front. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hello Mark,
Sorry once again for the confusion on my part.. The String is Mahadeoguru .. thats why it is extracting Mahad from it.. as it is preceded by a space. Can this u give u some idea. Rashid "Mark Thorpe" wrote in message ... Note that in the line: ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0 we are searching for the value from column AA preceded by a space. (thus the " " &) For example, it will not find "mahad" in "Gurumahadeo", because it first checks to see whether Gurumahadeo begins with mahad (does not), then searches for " mahad", which it does not find because of the space added to the front. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Rashid,
i think you can forget the VBA.. since you're NOT requiring an exact match THIS simple function should do.. =SUMPRODUCT(--(NOT(ISERROR(FIND($AA$1:$AA$25;W1))))) if it's 0 = no occurance.. if higher the number of matches found :) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Rashid Khan" wrote: Hello Mark, Sorry once again for the confusion on my part.. The String is Mahadeoguru .. thats why it is extracting Mahad from it.. as it is preceded by a space. Can this u give u some idea. Rashid "Mark Thorpe" wrote in message ... Note that in the line: ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0 we are searching for the value from column AA preceded by a space. (thus the " " &) For example, it will not find "mahad" in "Gurumahadeo", because it first checks to see whether Gurumahadeo begins with mahad (does not), then searches for " mahad", which it does not find because of the space added to the front. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi there,
I copied the formula u suggested in B1.. It gives an error :-( By the way what is the W1 in the right hand side of the formula? Rashid "keepITcool" wrote in message ... Rashid, i think you can forget the VBA.. since you're NOT requiring an exact match THIS simple function should do.. =SUMPRODUCT(--(NOT(ISERROR(FIND($AA$1:$AA$25;W1))))) if it's 0 = no occurance.. if higher the number of matches found :) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Rashid Khan" wrote: Hello Mark, Sorry once again for the confusion on my part.. The String is Mahadeoguru .. thats why it is extracting Mahad from it.. as it is preceded by a space. Can this u give u some idea. Rashid "Mark Thorpe" wrote in message ... Note that in the line: ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) 0 we are searching for the value from column AA preceded by a space. (thus the " " &) For example, it will not find "mahad" in "Gurumahadeo", because it first checks to see whether Gurumahadeo begins with mahad (does not), then searches for " mahad", which it does not find because of the space added to the front. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
w1 is the cell reference for the text that needs to be looked up.. you just may have to change that to a1? the error could very well be caused by the fact that i used the ; as list separator... maybe you use a , instead ? keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Rashid Khan" wrote: Hi there, I copied the formula u suggested in B1.. It gives an error :-( By the way what is the W1 in the right hand side of the formula? Rashid "keepITcool" wrote in message ... Rashid, i think you can forget the VBA.. since you're NOT requiring an exact match THIS simple function should do.. =SUMPRODUCT(--(NOT(ISERROR(FIND($AA$1:$AA$25;W1))))) if it's 0 = no occurance.. if higher the number of matches found :) keepITcool |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to extract values
Hi there,
Ok ...Thanks. Your formula works showing me 0 and 1.. But how is that supposed to help my problem Rashid Khan "keepITcool" wrote in message ... w1 is the cell reference for the text that needs to be looked up.. you just may have to change that to a1? the error could very well be caused by the fact that i used the ; as list separator... maybe you use a , instead ? keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Rashid Khan" wrote: Hi there, I copied the formula u suggested in B1.. It gives an error :-( By the way what is the W1 in the right hand side of the formula? Rashid "keepITcool" wrote in message ... Rashid, i think you can forget the VBA.. since you're NOT requiring an exact match THIS simple function should do.. =SUMPRODUCT(--(NOT(ISERROR(FIND($AA$1:$AA$25;W1))))) if it's 0 = no occurance.. if higher the number of matches found :) keepITcool |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract duplicat values | Excel Discussion (Misc queries) | |||
Extract values off a chart??? | Charts and Charting in Excel | |||
Extract values from formulas | Excel Discussion (Misc queries) | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
extract values | Excel Programming |