VBA Code to perform inner join on 2 worksheets.

[mvandhu]

Hi All ,

I have an excel workbook with 2 worksheets in it.
Worksheet name : Student, Details

Join Keys are Stu_Id, Name, DOB, Std.

Column names

Student : Stu_Id, Name, DOB, Std, Location
Details : Stu_Id, Name, DOB, Std, Mark1, Mark2, Grade

After join operation is performed, my sheet3 should have the columns as
Stu_Id, Name, DOB, Std, Location, Mark1, Mark2, Grade.

Each time the number of records/row in the 2 worksheets (Student, Details) always varies.
Sometimes the row count may be more than 3000 also.

So need a code that can perform well and takes less time to populate the sheet3 worksheet.

Can someone help me with this scenario....

Another doubt.
Can SQL query be used in VBA to access the worksheets??
Can the above scenario done using SQL in VBA code??
If so can you post the code for that also???

[mikerickson]

Copy paste all the data from Student onto Sheet3

Then in Sheet3!F1, put the formula

=VLOOKUP(A1, Details!$A:$G, COLUMN(E1), False)

Drag it right and down.

[mvandhu]

Hi Mike..
Thanks for ur post...
I need a macro VBA code so that when I run the macro, the result should automatically get populated in sheet3.

[mikerickson]

With Sheets("Student")
    With Range(.Cells(1, 5), .Cells(.Rows.Count, 1).End(xlup))
        Sheets("Sheet3").Range(.Address).Value = .Value
    End With
End With

With Sheets("Sheet3")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlup)).Offset(0,5).Resize(.Rows.Count, 3)
        .FormulaR1C1 = "=VLOOKUP(RC1, Details!C1:C8, COLUMN(RC[-1]),False)"
        .Value = .Value
    End With
End With

This is untested, the underlined portion might be RC[1]