Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Hi All,
I have 7 columns in sheet1 & in sheet2 i have 2 columns with some data. In sheet2 Col A I have some codes like 001 125 4563 Same codes repeated in sheet 1 in any of the columns ( Col B to Col G ) I need to match sheet2 col A data with sheet1 Col B to col G, if it matches i need data available in sheet1 col A in sheet 2 col B I am using this code. For i = 2 to 50 For a = 2 To 100 Frst = UCase(sheets("Sheet2").Cells(i, "A").Value) with sheets("Sheet1") If Frst = UCase(.Cells(a, "B").Value) Or _ Frst = UCase(.Cells(a, "C").Value) Or _ Frst = UCase(.Cells(a, "D").Value) Or _ Frst = UCase(.Cells(a, "E").Value) Or _ Frst = UCase(.Cells(a, "F").Value) Or _ Frst = UCase(.Cells(a, "G").Value) then Temp = .Cells(a, "A") res = res & "," & Temp End If end with Next a If res < "" Then sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute (res, ",", "", 1) res = "" next i Above vba works fine, but I have some exceptions. Some code in sheet1 is like W001 or F001 or L001 or T001 but in sheet2 has 001, so exact will not find this cases. How can i modify my code, Even i thought about wild seraches like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a, "D").Value) & "*" But pulls out the results where the code say 123001 or ABCED001 etc.... Please help me !! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Can you answer these questions for us please?
1) Can your Sheet2 codes appear anywhere within the values in your Sheet1 cells, or will they always occur at the end of the values as your examples show? 2) Are there any cases where a code can be embedded within a Sheet1 cell value where you would not consider it a match? If so, can you describe how you would know they shouldn't be considered a match? 3) Your codes are shown as being all numbers, so why are you applying the UCase function to them and the cell values you are trying to match them with in your If..Then test? Are your actual codes different than you showed us? If so, can you describe the structure behind the for us? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi All, I have 7 columns in sheet1 & in sheet2 i have 2 columns with some data. In sheet2 Col A I have some codes like 001 125 4563 Same codes repeated in sheet 1 in any of the columns ( Col B to Col G ) I need to match sheet2 col A data with sheet1 Col B to col G, if it matches i need data available in sheet1 col A in sheet 2 col B I am using this code. For i = 2 to 50 For a = 2 To 100 Frst = UCase(sheets("Sheet2").Cells(i, "A").Value) with sheets("Sheet1") If Frst = UCase(.Cells(a, "B").Value) Or _ Frst = UCase(.Cells(a, "C").Value) Or _ Frst = UCase(.Cells(a, "D").Value) Or _ Frst = UCase(.Cells(a, "E").Value) Or _ Frst = UCase(.Cells(a, "F").Value) Or _ Frst = UCase(.Cells(a, "G").Value) then Temp = .Cells(a, "A") res = res & "," & Temp End If end with Next a If res < "" Then sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute (res, ",", "", 1) res = "" next i Above vba works fine, but I have some exceptions. Some code in sheet1 is like W001 or F001 or L001 or T001 but in sheet2 has 001, so exact will not find this cases. How can i modify my code, Even i thought about wild seraches like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a, "D").Value) & "*" But pulls out the results where the code say 123001 or ABCED001 etc.... Please help me !! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Hi Rick,
1) sheet2 codes appears in sheet1 - more than once. codes are alpha numeric , in my example i have given only numbers. 2) No 3) Sorry for that, I have alpha numeric. Eg., Sheet 2 Columns Data as follow A B C D E F G FRexXX001 F124 Alfred Fund Code Alias124 blank blank DExeXXX75 00075 blank W075 AliasDE 124 blank REDer7823 RED78 7823 R7823 AliasRED 75 Blank Sheet1 Columns Data As follow A 124 75 red78 In Col B i need data. Sheet 1 Col A data 124 matches with sheet2 Col B to Col G 1. 124 matches with F124 (Cell B1) & 124 (F2) - result will FRexXX001, DExeXXX75 2. 75 matches with 00075 (Cell B2) & (Cell D2) & (Cell F3) - result will be DExeXXX75,REDer7823 Thanks in advance Rick. On Oct 3, 7:16*pm, "Rick Rothstein" wrote: Can you answer these questions for us please? 1) Can your Sheet2 codes appear anywhere within the values in your Sheet1 cells, or will they always occur at the end of the values as your examples show? 2) Are there any cases where a code can be embedded within a Sheet1 cell value where you would not consider it a match? If so, can you describe how you would know they shouldn't be considered a match? 3) Your codes are shown as being all numbers, so why are you applying the UCase function to them and the cell values you are trying to match them with in your If..Then test? Are your actual codes different than you showed us? If so, can you describe the structure behind the for us? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi All, I have 7 columns in sheet1 & in sheet2 i have 2 columns with some data. In sheet2 Col A I have some codes like 001 125 4563 Same codes repeated in sheet 1 in any of the columns ( Col B to Col G ) I need to match sheet2 col A data with sheet1 Col B to col G, if it matches i need data available in sheet1 col A in sheet 2 col B I am using this code. For i = 2 to 50 For a = 2 To 100 * * * * * * * * * * * Frst = UCase(sheets("Sheet2").Cells(i, "A").Value) * * * * * * * * * * * * * * * *with sheets("Sheet1") * * * * * * * * * * * * * If Frst = UCase(.Cells(a, "B").Value) Or _ * * * * * * * * * * * * * * *Frst = UCase(.Cells(a, "C").Value) Or _ * * * * * * * * * * * * * * *Frst = UCase(.Cells(a, "D").Value) Or _ * * * * * * * * * * * * * * *Frst = UCase(.Cells(a, "E").Value) Or _ * * * * * * * * * * * * * * *Frst = UCase(.Cells(a, "F").Value) Or _ * * * * * * * * * * * * * * *Frst = UCase(.Cells(a, "G").Value) then * * * * * * * * * * * * * * * * * * *Temp = .Cells(a, "A") * * * * * * * * * * * * * * * * * * * res = res & "," & Temp * * * * * * * * * * * * *End If * * * * * * * * * * * * * * * end with * * * * * * * * Next a * * If res < "" Then * * * *sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute (res, ",", "", 1) * * * *res = "" next i Above vba works fine, but I have some exceptions. Some code in sheet1 is like W001 or F001 or L001 or T001 but in sheet2 has 001, so exact will not find this cases. How can i modify my code, Even i thought about wild seraches like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a, "D").Value) & "*" But pulls out the results where the code say 123001 or ABCED001 etc.... Please help me !! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
You did not answer the actual question I asked in Question 1, but I am glad
you gave the answer that you did as it highlight what the concatenations you were doing was for. However, back to Question 1... your examples showed that if your code was 001, you wanted it to match itself (001), obviously, and things like W001, F001, L001, etc. also... my question was, would it also be considered a match for something like 001X, or even A001B, where the code part (the 001) does not occur only at the end of the text in the cell? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi Rick, 1) sheet2 codes appears in sheet1 - more than once. codes are alpha numeric , in my example i have given only numbers. 2) No 3) Sorry for that, I have alpha numeric. Eg., Sheet 2 Columns Data as follow A B C D E F G FRexXX001 F124 Alfred Fund Code Alias124 blank blank DExeXXX75 00075 blank W075 AliasDE 124 blank REDer7823 RED78 7823 R7823 AliasRED 75 Blank Sheet1 Columns Data As follow A 124 75 red78 In Col B i need data. Sheet 1 Col A data 124 matches with sheet2 Col B to Col G 1. 124 matches with F124 (Cell B1) & 124 (F2) - result will FRexXX001, DExeXXX75 2. 75 matches with 00075 (Cell B2) & (Cell D2) & (Cell F3) - result will be DExeXXX75,REDer7823 Thanks in advance Rick. On Oct 3, 7:16 pm, "Rick Rothstein" wrote: Can you answer these questions for us please? 1) Can your Sheet2 codes appear anywhere within the values in your Sheet1 cells, or will they always occur at the end of the values as your examples show? 2) Are there any cases where a code can be embedded within a Sheet1 cell value where you would not consider it a match? If so, can you describe how you would know they shouldn't be considered a match? 3) Your codes are shown as being all numbers, so why are you applying the UCase function to them and the cell values you are trying to match them with in your If..Then test? Are your actual codes different than you showed us? If so, can you describe the structure behind the for us? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi All, I have 7 columns in sheet1 & in sheet2 i have 2 columns with some data. In sheet2 Col A I have some codes like 001 125 4563 Same codes repeated in sheet 1 in any of the columns ( Col B to Col G ) I need to match sheet2 col A data with sheet1 Col B to col G, if it matches i need data available in sheet1 col A in sheet 2 col B I am using this code. For i = 2 to 50 For a = 2 To 100 Frst = UCase(sheets("Sheet2").Cells(i, "A").Value) with sheets("Sheet1") If Frst = UCase(.Cells(a, "B").Value) Or _ Frst = UCase(.Cells(a, "C").Value) Or _ Frst = UCase(.Cells(a, "D").Value) Or _ Frst = UCase(.Cells(a, "E").Value) Or _ Frst = UCase(.Cells(a, "F").Value) Or _ Frst = UCase(.Cells(a, "G").Value) then Temp = .Cells(a, "A") res = res & "," & Temp End If end with Next a If res < "" Then sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute (res, ",", "", 1) res = "" next i Above vba works fine, but I have some exceptions. Some code in sheet1 is like W001 or F001 or L001 or T001 but in sheet2 has 001, so exact will not find this cases. How can i modify my code, Even i thought about wild seraches like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a, "D").Value) & "*" But pulls out the results where the code say 123001 or ABCED001 etc.... Please help me !! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Rick,
only 5 Alphabets will be prefixed with numbers. ie., W, A, F, T, L or prefixed with zeros (00 or 0000 or 0 ) Even the number is part of it, that will be ok. like you said, 124 is matches with 124, A124 , 00124 , A12400 On Oct 3, 8:26*pm, "Rick Rothstein" wrote: You did not answer the actual question I asked in Question 1, but I am glad you gave the answer that you did as it highlight what the concatenations you were doing was for. However, back to Question 1... your examples showed that if your code was 001, you wanted it to match *itself (001), obviously, and things like W001, F001, L001, etc. also... my question was, would it also be considered a match for something like 001X, or even A001B, where the code part (the 001) does not occur only at the end of the text in the cell? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi Rick, 1) sheet2 codes appears in sheet1 - more than once. * * codes are alpha numeric , in my example i have given only numbers.. 2) No 3) Sorry for that, I have alpha numeric. Eg., Sheet 2 Columns Data as follow * A * * * * * * * * * * B * * * * * * C * * * * * * D E * * * * * * * F * * * * * G FRexXX001 * * * F124 * * * Alfred * * Fund Code * Alias124 blank * * *blank DExeXXX75 * * * 00075 * blank * * * *W075 * * * * * *AliasDE 124 * * *blank REDer7823 * * * RED78 * *7823 * * *R7823 * * * * AliasRED * * 75 Blank Sheet1 Columns Data As follow A 124 75 red78 In Col B i need data. Sheet 1 Col A data 124 matches with sheet2 Col B to Col G 1. 124 matches with F124 (Cell B1) & 124 (F2) - result will FRexXX001, DExeXXX75 2. 75 matches with 00075 (Cell B2) & (Cell D2) & (Cell F3) - result will be DExeXXX75,REDer7823 Thanks in advance Rick. On Oct 3, 7:16 pm, "Rick Rothstein" wrote: Can you answer these questions for us please? 1) Can your Sheet2 codes appear anywhere within the values in your Sheet1 cells, or will they always occur at the end of the values as your examples show? 2) Are there any cases where a code can be embedded within a Sheet1 cell value where you would not consider it a match? If so, can you describe how you would know they shouldn't be considered a match? 3) Your codes are shown as being all numbers, so why are you applying the UCase function to them and the cell values you are trying to match them with in your If..Then test? Are your actual codes different than you showed us? If so, can you describe the structure behind the for us? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message .... Hi All, I have 7 columns in sheet1 & in sheet2 i have 2 columns with some data. In sheet2 Col A I have some codes like 001 125 4563 Same codes repeated in sheet 1 in any of the columns ( Col B to Col G ) I need to match sheet2 col A data with sheet1 Col B to col G, if it matches i need data available in sheet1 col A in sheet 2 col B I am using this code. For i = 2 to 50 For a = 2 To 100 Frst = UCase(sheets("Sheet2").Cells(i, "A").Value) with sheets("Sheet1") If Frst = UCase(.Cells(a, "B").Value) Or _ Frst = UCase(.Cells(a, "C").Value) Or _ Frst = UCase(.Cells(a, "D").Value) Or _ Frst = UCase(.Cells(a, "E").Value) Or _ Frst = UCase(.Cells(a, "F").Value) Or _ Frst = UCase(.Cells(a, "G").Value) then Temp = .Cells(a, "A") res = res & "," & Temp End If end with Next a If res < "" Then sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute (res, ",", "", 1) res = "" next i Above vba works fine, but I have some exceptions. Some code in sheet1 is like W001 or F001 or L001 or T001 but in sheet2 has 001, so exact will not find this cases. How can i modify my code, Even i thought about wild seraches like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a, "D").Value) & "*" But pulls out the results where the code say 123001 or ABCED001 etc.... Please help me !! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search
Try using the InStr function in your code, like this...
For i = 2 To 50 For a = 2 To 100 Frst = Sheets("Sheet2").Cells(i, "A").Value With Sheets("Sheet1") If InStr(1, .Cells(a, "B").Value, Frst, vbTextCompare) 0 Or _ InStr(1, .Cells(a, "B").Value, Frst, vbTextCompare) 0 Or _ InStr(1, .Cells(a, "B").Value, Frst, vbTextCompare) 0 Or _ InStr(1, .Cells(a, "B").Value, Frst, vbTextCompare) 0 Or _ InStr(1, .Cells(a, "B").Value, Frst, vbTextCompare) 0 Or _ InStr(1, .Cells(a, "B").Value, Frst, vbTextCompare) 0 Then res = res & "," & .Cells(a, "A") End If End With Next a If res < "" Then Sheets("Sheet2").Cells(i, "B") = Mid(res, 2) res = "" End If Next i Note that I removed the UCase function calls you had in your original code... that is because the InStr function has an optional 4th argument you can specify to make it do a case insensitive search. Also note that how I changed this line from your original code... Sheets("Sheet2").Cells(i,"B")=WorksheetFunction.Su bstitute(res,",","",1) to this simpler, more efficient statement... Sheets("Sheet2").Cells(i, "B") = Mid(res, 2) You are simply trying to remove the leading comma... the above Mid function call returns the substring formed by starting at the 2nd character (and continuing to the end) of the text contained in the res variable (it returns the rest of the text starting from the 2nd character because the optional length argument is omitted... this is a different functionality than the worksheet's MID function provides and comes in very handy for situations like this). -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Rick, only 5 Alphabets will be prefixed with numbers. ie., W, A, F, T, L or prefixed with zeros (00 or 0000 or 0 ) Even the number is part of it, that will be ok. like you said, 124 is matches with 124, A124 , 00124 , A12400 On Oct 3, 8:26 pm, "Rick Rothstein" wrote: You did not answer the actual question I asked in Question 1, but I am glad you gave the answer that you did as it highlight what the concatenations you were doing was for. However, back to Question 1... your examples showed that if your code was 001, you wanted it to match itself (001), obviously, and things like W001, F001, L001, etc. also... my question was, would it also be considered a match for something like 001X, or even A001B, where the code part (the 001) does not occur only at the end of the text in the cell? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi Rick, 1) sheet2 codes appears in sheet1 - more than once. codes are alpha numeric , in my example i have given only numbers. 2) No 3) Sorry for that, I have alpha numeric. Eg., Sheet 2 Columns Data as follow A B C D E F G FRexXX001 F124 Alfred Fund Code Alias124 blank blank DExeXXX75 00075 blank W075 AliasDE 124 blank REDer7823 RED78 7823 R7823 AliasRED 75 Blank Sheet1 Columns Data As follow A 124 75 red78 In Col B i need data. Sheet 1 Col A data 124 matches with sheet2 Col B to Col G 1. 124 matches with F124 (Cell B1) & 124 (F2) - result will FRexXX001, DExeXXX75 2. 75 matches with 00075 (Cell B2) & (Cell D2) & (Cell F3) - result will be DExeXXX75,REDer7823 Thanks in advance Rick. On Oct 3, 7:16 pm, "Rick Rothstein" wrote: Can you answer these questions for us please? 1) Can your Sheet2 codes appear anywhere within the values in your Sheet1 cells, or will they always occur at the end of the values as your examples show? 2) Are there any cases where a code can be embedded within a Sheet1 cell value where you would not consider it a match? If so, can you describe how you would know they shouldn't be considered a match? 3) Your codes are shown as being all numbers, so why are you applying the UCase function to them and the cell values you are trying to match them with in your If..Then test? Are your actual codes different than you showed us? If so, can you describe the structure behind the for us? -- Rick (MVP - Excel) "fi.or.jp.de" wrote in message ... Hi All, I have 7 columns in sheet1 & in sheet2 i have 2 columns with some data. In sheet2 Col A I have some codes like 001 125 4563 Same codes repeated in sheet 1 in any of the columns ( Col B to Col G ) I need to match sheet2 col A data with sheet1 Col B to col G, if it matches i need data available in sheet1 col A in sheet 2 col B I am using this code. For i = 2 to 50 For a = 2 To 100 Frst = UCase(sheets("Sheet2").Cells(i, "A").Value) with sheets("Sheet1") If Frst = UCase(.Cells(a, "B").Value) Or _ Frst = UCase(.Cells(a, "C").Value) Or _ Frst = UCase(.Cells(a, "D").Value) Or _ Frst = UCase(.Cells(a, "E").Value) Or _ Frst = UCase(.Cells(a, "F").Value) Or _ Frst = UCase(.Cells(a, "G").Value) then Temp = .Cells(a, "A") res = res & "," & Temp End If end with Next a If res < "" Then sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute (res, ",", "", 1) res = "" next i Above vba works fine, but I have some exceptions. Some code in sheet1 is like W001 or F001 or L001 or T001 but in sheet2 has 001, so exact will not find this cases. How can i modify my code, Even i thought about wild seraches like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a, "D").Value) & "*" But pulls out the results where the code say 123001 or ABCED001 etc.... Please help me !! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set up search box to, by default, search workbook in the extraoptions? | Excel Programming | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
Search lastname + firstname (search on uppercase) | Excel Programming | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Create a search Field within a worksheet to search command buttons | Excel Programming |