I want to compare the Sheet1 column A values with Sheet2 column B, if match then i want to put the Sheet1 Column A values in Sheet2 Column B.

[Vinothkumar]

I want to compare the Sheet1 column A values with Sheet2 column B, if match then i want to put the Sheet1 Column A values in Sheet2 Column B. and column C should be popualted with 'True'
So i have written the below code:

Sub val()
Dim sheet1_last_rec_cnt As Long
Dim sheet2_last_rec_cnt As Long
Dim sheet1_col1_val As String
Dim cnt1 As Long
Dim cnt2 As Long
sheet1_last_rec_cnt = Sheet1.UsedRange.Rows.Count
sheet2_last_rec_cnt = Sheet2.UsedRange.Rows.Count
For cnt1 = 2 To sheet1_last_rec_cnt
sheet1_col1_val = Sheet1.Range("A" & cnt1).Value
For cnt2 = 2 To sheet2_last_rec_cnt
If sheet1_col1_val = Sheet2.Range("A" & cnt2).Value Then
Sheet2.Range("B" & cnt2).Value = sheet1_col1_val
Sheet2.Range("C" & cnt2).Value = "True"
Exit For
End If
Next
Next
End Sub


Problem is i have one millions of records in both the sheets. if i use the above code then For loop is running (One million * One million) times. So excel is hanging like anything. Can someone please help me to optimize the code?

 

[oldbrewer]

I want to compare the Sheet1 column A values with Sheet2 column B, if match then i want to put the Sheet1 Column A values in Sheet2 Column B. and column C should be popualted with 'True'

so if A2 = green and sheet 2 B2 = green you want to put green where green alrady is ?

 

[Vinothkumar]

Update:I want to compare the Sheet1 column A values with Sheet2 column B, if match then i want to put the Sheet1 Column A values in Sheet2 Column C. and column D should be popualted with 'True'
So i have written the below code:

Sub val()
Dim sheet1_last_rec_cnt As Long
Dim sheet2_last_rec_cnt As Long
Dim sheet1_col1_val As String
Dim cnt1 As Long
Dim cnt2 As Long
sheet1_last_rec_cnt = Sheet1.UsedRange.Rows.Count
sheet2_last_rec_cnt = Sheet2.UsedRange.Rows.Count
For cnt1 = 2 To sheet1_last_rec_cnt
sheet1_col1_val = Sheet1.Range("A" & cnt1).Value
For cnt2 = 2 To sheet2_last_rec_cnt
If sheet1_col1_val = Sheet2.Range("B" & cnt2).Value Then
Sheet2.Range("C" & cnt2).Value = sheet1_col1_val
Sheet2.Range("D" & cnt2).Value = "True"
Exit For
End If
Next
Next
End Sub


Problem is i have one millions of records in both the sheets. if i use the above code then For loop is running (One million * One million) times. So excel is hanging like anything. Can someone please help me to optimize the code?

 

[Vinothkumar]

I just updated my requirement below.
shhet2 column B= sheet1 column A
shhet2 column D = True/false

 

[kalak]

Is the below code something like you're after?

Sub comparez()

Dim DObj As Object
Dim a1, a2, u()
Dim s1 As Worksheet, s2 As Worksheet
Dim i As Long, rs1 As Long, rs2 As Long

Set DObj = CreateObject("scripting.dictionary")
Set s1 = Sheets("sheet1")
Set s2 = Sheets("sheet2")
rs1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
rs2 = s2.Cells(Rows.Count, "B").End(xlUp).Row
ReDim u(1 To rs2, 1 To 2)
a1 = s1.Cells(1).Resize(rs1)
a2 = s2.Cells(2).Resize(rs2)

For i = 2 To rs1
    DObj(a1(i, 1)) = True
Next i

For i = 2 To rs2
    If DObj(a2(i, 1)) Then
        u(i, 1) = a2(i, 1)
        u(i, 2) = "TRUE"
    Else
        u(i, 2) = "False"
    End If
Next i

s2.Cells(4).Resize(rs2, 2) = u

End Sub

Are your data integers? Code (slightly modified) runs much faster if so.

 

[oldbrewer]

			sheet1	sheet2	sheet2	sheet2
			colA	colB	colC	colD
			7	5
			6	6	6	TRUE
			5	7
			4	8
		this is what you now say you want

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

			sheet1	sheet2	sheet2	sheet2
			colA	colB	colC	colD
			7	5
			6	6		TRUE
			5	7
			4	8
		maybe you want this
		true is obtained by
		=IF(B13=Sheet1!A13,"TRUE","FALSE")

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Vinothkumar]

[Yes this is want i want.

			sheet1	sheet2	sheet2	sheet2
			colA	colB	colC	colD
			7	5
			6	6	6	TRUE
			5	7
			4	8
		this is what you now say you want

<TBODY>
</TBODY>

[/QUOTE]

 

[Vinothkumar]

Yes this is what i want.

			sheet1	sheet2	sheet2
			colA	colB	colC	colD
			7	5
			6	6	6	TRUE
			5	7
			4	8

<TBODY>
</TBODY>

 

[oldbrewer]

sheet1	sheet2	sheet2	sheet2
			colA	colB	colC	colD
			7	5
			6	6	6	TRUE
			5	7
			4	8

<tbody>
</tbody>

so in sheet2 colc say C10
=if(sheet1!B10=B10,b10,"")
and in D10
=if(c10="","","TRUE")