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