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?

 

[kalak]

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'
...
...
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?

There's a code in Post#5 that optimizes your code just as you requested.

Seems you no longer want what you requested, so I leave it to others to guess at what you really wanted.

 

[oldbrewer]

I don't think you need code as very simple formulas do the job ok.

 

[kalak]

So i have written the below code. 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?

Yeah. Agree with that bit.
But I tend to assume, perhaps wrongly, that people want what they ask for rather than wanting be told what they want.
Is the usual practice on this forum to tell contributors they need formulae when they specifically ask how to optimize codes?
Sometimes they may just want to know how to optimize codes.
Seems like I just assessed the OP request wrongly in this case.

 

[oldbrewer]

kalak - there is no usual practice here

I offer my opinion - sometimes on alternative ways of achieving the object - the OP almost always indicates THEN if alternative ways are acceptable.

 

[kalak]

kalak - there is no usual practice here

I offer my opinion - sometimes on alternative ways of achieving the object - the OP almost always indicates THEN if alternative ways are acceptable.

@oldbrewer
I've never really envisaged simple formulas as a means of optimizing codes, which seemed to me OP's objective.

Never that enamoured either about using formulas for large projects of 1 million rows (like OP's) or so without using VBA codes.
Seems to me setup time, filling down, preventing redundant calculations etc are required. If a VBA code is already there, say to handle repeated large problems, it only requires a single click on a worksheet. Not simple?
Couple of codes below, one array-based, the other formuls-based. I ran both on some sample data of 1 million rows. Array code took 2.0 secs to complete, formula-based code took 3.5 secs. Should be optimized enough for the OP, although I doubt if he/she is still interested.
But hey, "de gustibus non disputandum est".

Sub one()

Dim sh1 As Worksheet, sh2 As Worksheet
Dim u1, u2, i As Long
Dim a()

Set sh1 = Sheets("sheet1")
Set sh2 = Sheets("sheet2")

u1 = sh1.Range(sh1.Cells(1, "a"), sh1.Cells(Rows.Count, "a").End(3))
u2 = sh2.Range(sh2.Cells(1, "b"), sh2.Cells(Rows.Count, "b").End(3))
m = Application.Min(UBound(u1), UBound(u2))

ReDim a(1 To m, 1 To 2)
For i = 1 To m
    If u1(i, 1) = u2(i, 1) Then
        a(i, 1) = u2(i, 1)
        a(i, 2) = "True"
    End If
Next i

sh2.Cells(4).Resize(m, 2) = a

End Sub

Sub two()

Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheets("sheet1")
Set sh2 = Sheets("sheet2")
Application.Calculation = xlManual
sh1.Range(sh1.Cells(1, "a"), sh1.Cells(Rows.Count, "a").End(3)).Name = "a"
sh2.Range(sh2.Cells(1, "b"), sh2.Cells(Rows.Count, "b").End(3)).Name = "b"
Range("b").Offset(, 2) = "=if(a=b,b,"""")"
Range("b").Offset(, 3) = "=if(a=b,True,"""")"
Application.Calculation = xlAutomatic

End Sub

 

[oldbrewer]

I have now pinched your solutions and put them in my quiver.....