Compare values from two excel sheets(columns) and check values one by one

[rkulasekaran]

Hello all,

I am having two excel sheets and the values from one excel sheet(columns) must be compared with the other column in the second excel sheet and if the same values are found print in new column in the first excel sheet with the corresponding values in the same row.

The first excel sheet has 500 rows named( S_DataStore) and the second excel sheet has 2500 named (C_Datastore), how to check and loop through one value from the excel sheet with all the values in the other excel sheet and if the same value exists copy the value from the second sheet( C_Datastore) to the first sheet(S_Datastore).


The below code code compares two columns of two excel sheets.
I have tried with the below code, but it doesn't work properly, kindly any one can help me out.Thanks in advance.



Dim typ, l, rc, typ2 As Integer
Dim st, pn, prob_type_func, prob_type_cas As String
Dim example, example1 As Range


rc = 9
typ = 9
typ2 = 6



For Each example In C_DataStore.Sheet.Range("F5:F2500")
For Each example1 In C_DataStore.Sheet.Range("E6:E2500")

st = S_DataStore.Sheet.Cells(typ, 17).Value
pn = S_DataStore.Sheet.Cells(typ, 18).Value

If example.Value = st Then
S_DataStore.Sheet.Cells(rc, 74).Value = C_DataStore.Sheet.Cells(typ2, 6).Value
S_DataStore.Sheet.Cells(rc, 75).Value = C_DataStore.Sheet.Cells(typ2, 5).Value
S_DataStore.Sheet.Cells(rc, 76).Value = C_DataStore.Sheet.Cells(typ2, 3).Value
S_DataStore.Sheet.Cells(rc, 77).Value = C_DataStore.Sheet.Cells(typ2, 12).Value & " ---" & S_DataStore.Sheet.Cells(rc, 8).Value
S_DataStore.Sheet.Cells(rc, 78).Value = C_DataStore.Sheet.Cells(typ2, 10).Value & "---" & S_DataStore.Sheet.Cells(rc, 20).Value
S_DataStore.Sheet.Cells(rc, 79).Value = C_DataStore.Sheet.Cells(typ2, 24).Value
rc = rc + 1
typ2 = typ2 + 1


ElseIf example.Value <> st Then


S_DataStore.Sheet.Cells(rc, 74).Value = ""
S_DataStore.Sheet.Cells(rc, 75).Value = ""
S_DataStore.Sheet.Cells(rc, 76).Value = ""
S_DataStore.Sheet.Cells(rc, 77).Value = ""
S_DataStore.Sheet.Cells(rc, 78).Value = ""
S_DataStore.Sheet.Cells(rc, 79).Value = ""


rc = rc + 1
typ = typ + 1
End If
typ = typ + 1

Next
'Next