Hi All,

I have a excel file with two sheets.

Sheet1 has data like this
IN134566
IN455544

Sheet2 has data like this
IN00000134566
IN00000455544

So, I need to match sheet1 data is available or not in sheet2, if its
available then in the
next cell i need value "YES"

FYI - Sheet2 - there may be 4 zero's or 6 zero's
Sheet2 has duplicate values,

Thanks in advance.

Where is the next cell in relation to the data cell in sheet1?

If ToRight:

Sheet1:
A1: IN134566
A2: IN455544

Sheet2:
A1: IN00000134566
A2: IN00000455544

Sub MatchValues()
Dim lLastRow As Long, j As Long, k As Long
Dim vData1 As Variant, vData2 As Variant

lLastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count ,
1).End(xlUp).Row
vData1 = Sheets("Sheet1").Range("$A$1:$A$" & lLastRow)
lLastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count ,
1).End(xlUp).Row
vData2 = Sheets("Sheet2").Range("$A$1:$A$" & lLastRow)

For j = LBound(vData1) To UBound(vData1)
For k = LBound(vData2) To UBound(vData2)
If InStr(1, vData2(k, 1), Mid$(vData1(j, 1), 3)) 0 Then
Sheets("Sheet1").Cells(j, 2) = "Yes": Exit For
End If
Next 'k
Next 'j
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

If you prefer an in-cell formula...

In Sheet1!B1: (or any column you choose)
=IF(COUNTIF(Sheet2!$A:$A,$A1)0,"Yes","")

...and copy down as required.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I want to match from sheet2 to Sheet1.

Awaiting for your reply



On Jul 7, 8:40*am, GS wrote:
Where is the next cell in relation to the data cell in sheet1?

If ToRight:

Sheet1:
* A1: *IN134566
* A2: *IN455544

Sheet2:
* A1: *IN00000134566
* A2: *IN00000455544

Sub MatchValues()
* Dim lLastRow As Long, j As Long, k As Long
* Dim vData1 As Variant, vData2 As Variant

* lLastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count ,
1).End(xlUp).Row
* vData1 = Sheets("Sheet1").Range("$A$1:$A$" & lLastRow)
* lLastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count ,
1).End(xlUp).Row
* vData2 = Sheets("Sheet2").Range("$A$1:$A$" & lLastRow)

* For j = LBound(vData1) To UBound(vData1)
* * For k = LBound(vData2) To UBound(vData2)
* * * If InStr(1, vData2(k, 1), Mid$(vData1(j, 1), 3)) 0 Then
* * * * Sheets("Sheet1").Cells(j, 2) = "Yes": Exit For
* * * End If
* * Next 'k
* Next 'j
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

fi.or.jp.de wrote on 8/2/2011 :
I want to match from sheet2 to Sheet1.

Awaiting for your reply

I believe the code I provided does what you want. So does the in cell
formula I provided in my 2nd reply. So using your example, the code
generates the following:

On Sheet1
A1: IN134566 B1: Yes (in-cell formula also returns "Yes")
A2: IN455544 B1: Yes (in-cell formula also returns "Yes")

If what you want is other than this then post an example of the
expected results.



On Jul 7, 8:40*am, GS wrote:
Where is the next cell in relation to the data cell in sheet1?

If ToRight:

Sheet1:
* A1: *IN134566
* A2: *IN455544

Sheet2:
* A1: *IN00000134566
* A2: *IN00000455544

Sub MatchValues()
* Dim lLastRow As Long, j As Long, k As Long
* Dim vData1 As Variant, vData2 As Variant

* lLastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count ,
1).End(xlUp).Row
* vData1 = Sheets("Sheet1").Range("$A$1:$A$" & lLastRow)
* lLastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count ,
1).End(xlUp).Row
* vData2 = Sheets("Sheet2").Range("$A$1:$A$" & lLastRow)

* For j = LBound(vData1) To UBound(vData1)
* * For k = LBound(vData2) To UBound(vData2)
* * * If InStr(1, vData2(k, 1), Mid$(vData1(j, 1), 3)) 0 Then
* * * * Sheets("Sheet1").Cells(j, 2) = "Yes": Exit For
* * * End If
* * Next 'k
* Next 'j
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc