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 !!

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 !!

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 !!

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 !!

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 !!

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 !!

Hi Rick,

Thanks a lot for your help.


On Oct 3, 10:54*pm, "Rick Rothstein"
wrote:
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 !!

Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46*pm, "fi.or.jp.de" wrote:
Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54*pm, "Rick Rothstein"



wrote:
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 !!- Hide quoted text -

- Show quoted text -

First off, I guess you saw that I copied the same InStr function test for
all your columns of interest and then forgot to change the column letters.
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead of a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)


"fi.or.jp.de" wrote in message
...
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:
Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"



wrote:
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 !!- Hide quoted text -

- Show quoted text -

Actually speaking i Have B_12 and this should match
with
B_12
B 12
B-12
B12
some text B_12 some text
some text B 12 some text
some text B-12 some text
some text B12 some text




On Oct 5, 8:38*pm, "Rick Rothstein"
wrote:
First off, I guess you saw that I copied the same InStr function test for
all your columns of interest and then forgot to change the column letters..
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
* * * * * * InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
* * * * * * InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
* * * * * * InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
* * * * * * InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead of a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:



Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"

wrote:
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 !!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Okay, so your code is B_12 and **not** B<space12, right? (Note: It is
important to know exactly what your code possibilities are.) Then for me to
be able to create the correct filter, I need to know if any of your cells
could have something other than an underline, space, dash between the "B"
and the "12" which should not be matched. For example, could your cell have
this...

sometext B*12 sometext

in it that should not be matched?

--
Rick (MVP - Excel)


"fi.or.jp.de" wrote in message
...
Actually speaking i Have B_12 and this should match
with
B_12
B 12
B-12
B12
some text B_12 some text
some text B 12 some text
some text B-12 some text
some text B12 some text




On Oct 5, 8:38 pm, "Rick Rothstein"
wrote:
First off, I guess you saw that I copied the same InStr function test for
all your columns of interest and then forgot to change the column letters.
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead of
a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:



Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"

wrote:
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 !!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

sorry Rick, I didn't give you the full details.

first code what you gave that exception is working fine.

Apart from that i have one more, so i need both exceptions in single
code.

in my second exception there are many cases
1. B_12
2. B_12-23
3. B_12_23
4. B-12 23

There may be possibility of having some text prefixed or suffixed with
the above cases.


On Oct 5, 9:54*pm, "Rick Rothstein"
wrote:
Okay, so your code is B_12 and **not** B<space12, right? (Note: It is
important to know exactly what your code possibilities are.) Then for me to
be able to create the correct filter, I need to know if any of your cells
could have something other than an underline, space, dash between the "B"
and the "12" which should not be matched. For example, could your cell have
this...

sometext B*12 sometext

in it that should not be matched?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
Actually speaking i Have B_12 and this should match
with
B_12
B 12
B-12
B12
some text B_12 some text
some text B 12 some text
some text B-12 some text
some text B12 some text

On Oct 5, 8:38 pm, "Rick Rothstein"

wrote:
First off, I guess you saw that I copied the same InStr function test for
all your columns of interest and then forgot to change the column letters.
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead of
a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

....
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:

Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"

wrote:
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 !!-

...

read more »

Still not detailed enough (for me, at least... I need a general rule and not
a single specific example). Let me try asking my question in a different
way. If your code has a space, underline or dash (and ONLY those characters,
do you want be able to find text with ANY of those characters between the
letters/numbers. For example, would X_Y match **all** of these... "XY", "X
Y", "X-Y", "X_Y", "sometextXYsometext", "sometextX Ysometext",
"sometextX-Ysometext" and "sometextX_Ysometext"... and ONLY those
constructions meaning "X*Y", "X/Y" and so on would NOT match?
--
Rick (MVP - Excel)


"fi.or.jp.de" wrote in message
...
sorry Rick, I didn't give you the full details.

first code what you gave that exception is working fine.

Apart from that i have one more, so i need both exceptions in single
code.

in my second exception there are many cases
1. B_12
2. B_12-23
3. B_12_23
4. B-12 23

There may be possibility of having some text prefixed or suffixed with
the above cases.


On Oct 5, 9:54 pm, "Rick Rothstein"
wrote:
Okay, so your code is B_12 and **not** B<space12, right? (Note: It is
important to know exactly what your code possibilities are.) Then for me
to
be able to create the correct filter, I need to know if any of your cells
could have something other than an underline, space, dash between the "B"
and the "12" which should not be matched. For example, could your cell
have
this...

sometext B*12 sometext

in it that should not be matched?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
Actually speaking i Have B_12 and this should match
with
B_12
B 12
B-12
B12
some text B_12 some text
some text B 12 some text
some text B-12 some text
some text B12 some text

On Oct 5, 8:38 pm, "Rick Rothstein"

wrote:
First off, I guess you saw that I copied the same InStr function test
for
all your columns of interest and then forgot to change the column
letters.
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is
B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead
of
a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:

Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"

wrote:
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 !!-

...

read more »

Yes Rick,

This is what i am looking for

X_Y match **all** of these... "XY", "X Y", "X-Y", "X_Y",
"sometextXYsometext", "sometextX Ysometext",
"sometextX-Ysometext" and "sometextX_Ysometext"...




On Oct 5, 11:58*pm, "Rick Rothstein"
wrote:
Still not detailed enough (for me, at least... I need a general rule and not
a single specific example). Let me try asking my question in a different
way. If your code has a space, underline or dash (and ONLY those characters,
do you want be able to find text with ANY of those characters between the
letters/numbers. For example, would X_Y match **all** of these... "XY", "X
Y", "X-Y", "X_Y", "sometextXYsometext", "sometextX Ysometext",
"sometextX-Ysometext" and "sometextX_Ysometext"... and ONLY those
constructions meaning "X*Y", "X/Y" and so on would NOT match?
--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
sorry Rick, I didn't give you the full details.

first code what you gave that exception is working fine.

Apart from that i have one more, so i need both exceptions in single
code.

in my second exception there are many cases
* * *1. B_12
* * *2. B_12-23
* * *3. B_12_23
* * *4. B-12 23

There may be possibility of having some text prefixed or suffixed with
the above cases.

On Oct 5, 9:54 pm, "Rick Rothstein"



wrote:
Okay, so your code is B_12 and **not** B<space12, right? (Note: It is
important to know exactly what your code possibilities are.) Then for me
to
be able to create the correct filter, I need to know if any of your cells
could have something other than an underline, space, dash between the "B"
and the "12" which should not be matched. For example, could your cell
have
this...

sometext B*12 sometext

in it that should not be matched?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

....
Actually speaking i Have B_12 and this should match
with
B_12
B 12
B-12
B12
some text B_12 some text
some text B 12 some text
some text B-12 some text
some text B12 some text

On Oct 5, 8:38 pm, "Rick Rothstein"

wrote:
First off, I guess you saw that I copied the same InStr function test
for
all your columns of interest and then forgot to change the column
letters.
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is
B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead
of
a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

....
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:

Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"

wrote:
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

...

read more »- Hide quoted text -

- Show quoted text -

rick, please help me


On Oct 6, 11:23*am, "fi.or.jp.de" wrote:
Yes Rick,

This is what i am looking for

*X_Y match **all** of these... "XY", "X Y", "X-Y", "X_Y",
"sometextXYsometext", "sometextX Ysometext",
"sometextX-Ysometext" and "sometextX_Ysometext"...

On Oct 5, 11:58*pm, "Rick Rothstein"



wrote:
Still not detailed enough (for me, at least... I need a general rule and not
a single specific example). Let me try asking my question in a different
way. If your code has a space, underline or dash (and ONLY those characters,
do you want be able to find text with ANY of those characters between the
letters/numbers. For example, would X_Y match **all** of these... "XY", "X
Y", "X-Y", "X_Y", "sometextXYsometext", "sometextX Ysometext",
"sometextX-Ysometext" and "sometextX_Ysometext"... and ONLY those
constructions meaning "X*Y", "X/Y" and so on would NOT match?
--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
sorry Rick, I didn't give you the full details.

first code what you gave that exception is working fine.

Apart from that i have one more, so i need both exceptions in single
code.

in my second exception there are many cases
* * *1. B_12
* * *2. B_12-23
* * *3. B_12_23
* * *4. B-12 23

There may be possibility of having some text prefixed or suffixed with
the above cases.

On Oct 5, 9:54 pm, "Rick Rothstein"

wrote:
Okay, so your code is B_12 and **not** B<space12, right? (Note: It is
important to know exactly what your code possibilities are.) Then for me
to
be able to create the correct filter, I need to know if any of your cells
could have something other than an underline, space, dash between the "B"
and the "12" which should not be matched. For example, could your cell
have
this...

sometext B*12 sometext

in it that should not be matched?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

....
Actually speaking i Have B_12 and this should match
with
B_12
B 12
B-12
B12
some text B_12 some text
some text B 12 some text
some text B-12 some text
some text B12 some text

On Oct 5, 8:38 pm, "Rick Rothstein"

wrote:
First off, I guess you saw that I copied the same InStr function test
for
all your columns of interest and then forgot to change the column
letters.
The code I posted should have read...

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, "C").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "D").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "E").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "F").Value, Frst, vbTextCompare) 0 Or _
InStr(1, .Cells(a, "G").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

But, of course, that doesn't handle your current question. To answer it,
though, I need to know... is that code "B 12" (B/space/12)? If so, is
B12
and B_12 the only possible things it can possibly match? In other words,
could the be a B/12 or B-12 or B*12 in your cells that you would **not**
want to match? Also, could your code have some other character instead
of
a
space (assuming that is a space) between the "B" and the "12"?

--
Rick (MVP - Excel)

"fi.or.jp.de" wrote in message

...
Rick, I have tested.

It is working absolutely fine.

I got another exception.

Suppose I have B 12 it should match with B_12 or B12.

How can I achieve this.

Please rick..

On Oct 3, 11:46 pm, "fi.or.jp.de" wrote:

Hi Rick,

Thanks a lot for your help.

On Oct 3, 10:54 pm, "Rick Rothstein"

wrote:
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

...

read more »- Hide quoted text -

- Show quoted text -