How to Map 2 different columns in from 2 different excel files?Closed

Question

Dear All,

I am trying to Map values from 2 different columns from 2 different excel files. I am unsure as how to invoke the sheets and print the result(Yes/No) in either of the workbook. Could any of you please assist me with the start code as to assign and invoke the sheets and workbooks. 

I browsed various tutorials, but they are not much useful. Looking forward to your help.

Anjana

anjanasundaram · Answer

I have come up with the below code. But this works for a sheet with less number of rows. For a big file, the excel crashes and freezes. Though after doing some research, used and yet it freezes and goes to "Not Responding" in some time.

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Can someone please check and assist me on this ?

-----------------------------------------------------------------------------------
Option Compare Text
Dim Sheet1ID, Sheet2ID, sheet1FName, sheet2FName, sheet1Reltype, sheet2Reltype, sheet1sex, sheet2sex, sheet1salcode, sheet2salcode As String
Dim sheet1mname, sheet2mname, sheet1scode, sheet2scode, sheet1fullname, sheet2fullname, Sheet1DOB, Sheet2DOB As String
Dim wks As Worksheet, wksb As Worksheet, wks3 As Worksheet
Dim cell As Range
Dim rng, rngsheet1salcode, rngsheet2Fname, rngsheet1mname, rngNew, rngSheet1Name, rngSheet2Name, rngSheet1DOB, rngSheet2DOB, rngsheet1reltype, rngsheet2reltype As Range
Dim rngsheet2salcode, rngsheet1fname, rngsheet2mname, rngsheet1scode, rngsheet2scode, rngsheet2sex, rngsheet1fullname, rngsheet2fullname, rngsheet1sex As Range
Dim intCount As Integer, intCount1 As Integer
Dim n As Integer, m As Integer, i As Integer, j As Integer, K As Integer, intSheet1IDRow As Integer, intSheet1IDCol As Integer
Dim intSheet1DOB As Integer, intSheet2DOB, intsheet1reltype, intsheet2reltype, intsheet1sex, intsheet2sex, intsheet1salcode, intSheet2IDRow, intsheet2salcode, intSheet2IDCol As Integer
Dim intsheet1fname, intsheet2fname, intsheet1mname, intsheet2mname, intsheet1scode, intsheet2scode, intsheet1fullname, intsheet2fullname As Integer

Sub Mapping()

'With Excel.Application
'        .ScreenUpdating = False
'        .Calculation = Excel.xlCalculationManual
'        .EnableEvents = False
'End With

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Set wks = Worksheets("sheet1")
Set wksb = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")
    
Sheet1ID = wks3.Range("B2").Value
Sheet2ID = wks3.Range("C2").Value
sheet1Reltype = wks3.Range("B3").Value
sheet2Reltype = wks3.Range("C3").Value
sheet1sex = wks3.Range("B4").Value
sheet2sex = wks3.Range("C4").Value
sheet1salcode = wks3.Range("B5").Value
sheet2salcode = wks3.Range("C5").Value
sheet1FName = wks3.Range("B6").Value
sheet2FName = wks3.Range("C6").Value
sheet1mname = wks3.Range("B7").Value
sheet2mname = wks3.Range("C7").Value
sheet1scode = wks3.Range("B8").Value
sheet2scode = wks3.Range("C8").Value
sheet1fullname = wks3.Range("B9").Value
sheet2fullname = wks3.Range("C9").Value
Sheet1DOB = wks3.Range("B10").Value
Sheet2DOB = wks3.Range("C10").Value

intSheet1IDCol = Range(Sheet1ID).Column
intSheet1IDRow = Range(Sheet1ID).Row
intSheet2IDCol = Range(Sheet2ID).Column
intSheet2IDRow = Range(Sheet2ID).Row
intsheet1reltype = Range(sheet1Reltype).Column
intsheet2reltype = Range(sheet2Reltype).Column
intsheet1sex = Range(sheet1sex).Column
intsheet2sex = Range(sheet2sex).Column
intsheet1salcode = Range(sheet1salcode).Column
intsheet2salcode = Range(sheet2salcode).Column
intsheet1fname = Range(sheet1FName).Column
intsheet2fname = Range(sheet2FName).Column
intsheet1mname = Range(sheet1mname).Column
intsheet2mname = Range(sheet2mname).Column
intsheet1scode = Range(sheet1scode).Column
intsheet2scode = Range(sheet2scode).Column
intsheet1fullname = Range(sheet1fullname).Column
intsheet2fullname = Range(sheet2fullname).Column
intSheet1DOB = Range(Sheet1DOB).Column
intSheet2DOB = Range(Sheet2DOB).Column

Set rng = wks.Range(Sheet1ID).CurrentRegion
Set rngNew = wksb.Range(Sheet2ID).CurrentRegion
Set rngsheet1reltype = wks.Range(sheet1Reltype).CurrentRegion
Set rngsheet2reltype = wksb.Range(sheet2Reltype).CurrentRegion
Set rngsheet1sex = wks.Range(sheet1sex).CurrentRegion
Set rngsheet2sex = wksb.Range(sheet2sex).CurrentRegion
Set rngsheet1salcode = wks.Range(sheet1salcode).CurrentRegion
Set rngsheet2salcode = wksb.Range(sheet2salcode).CurrentRegion
Set rngsheet1fname = wks.Range(sheet1FName).CurrentRegion
Set rngsheet2Fname = wksb.Range(sheet2FName).CurrentRegion
Set rngsheet1mname = wks.Range(sheet1mname).CurrentRegion
Set rngsheet2mname = wksb.Range(sheet2mname).CurrentRegion
Set rngsheet1scode = wks.Range(sheet1scode).CurrentRegion
Set rngsheet2scode = wksb.Range(sheet2scode).CurrentRegion
Set rngsheet1fullname = wks.Range(sheet1fullname).CurrentRegion
Set rngsheet2fullname = wksb.Range(sheet2fullname).CurrentRegion
Set rngSheet1DOB = wks.Range(Sheet1DOB).CurrentRegion
Set rngSheet2DOB = wksb.Range(Sheet2DOB).CurrentRegion


'rngSheet1Name.IgnoreCase = True
'rngSheet2Name.IgnoreCase = True

intCount = rng.Rows.Count: intCount1 = rngNew.Rows.Count


For Each Row In rng.Rows
    For Each cell In Row.Cells
        For i = 2 To intCount
            For j = 2 To intCount1
            
             
                    If (rng.EntireRow.Cells(i, intSheet1IDCol) = rngNew.EntireRow.Cells(j, intSheet2IDCol)) Then
                    rng.Cells(i, intSheet1IDCol).Interior.ColorIndex = 4
'                    cell.Value = LCase(cell.Value)
                    
                    If (rngsheet1fname.EntireRow.Cells(i, intsheet1fname) = rngsheet2Fname.EntireRow.Cells(j, intsheet2fname)) Then
                        rngsheet1fname.Cells(i, intsheet1fname).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1reltype.EntireRow.Cells(i, intsheet1reltype) = rngsheet2reltype.EntireRow.Cells(j, intsheet2reltype)) Then
                        rngsheet1reltype.Cells(i, intsheet1reltype).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1sex.EntireRow.Cells(i, intsheet1sex) = rngsheet2sex.EntireRow.Cells(j, intsheet2sex)) Then
                        rngsheet1sex.Cells(i, intsheet1sex).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1salcode.EntireRow.Cells(i, intsheet1salcode) = rngsheet2salcode.EntireRow.Cells(j, intsheet2salcode)) Then
                        rngsheet1salcode.Cells(i, intsheet1salcode).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1fname.EntireRow.Cells(i, intsheet1fname) = rngsheet2Fname.EntireRow.Cells(j, intsheet2fname)) Then
                        rngsheet1fname.Cells(i, intsheet1fname).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1mname.EntireRow.Cells(i, intsheet1mname) = rngsheet2mname.EntireRow.Cells(j, intsheet2mname)) Then
                        rngsheet1mname.Cells(i, intsheet1mname).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1scode.EntireRow.Cells(i, intsheet1scode) = rngsheet2scode.EntireRow.Cells(j, intsheet2scode)) Then
                        rngsheet1scode.Cells(i, intsheet1scode).Interior.ColorIndex = 4
                    End If
                    If (rngsheet1fullname.EntireRow.Cells(i, intsheet1fullname) = rngsheet2fullname.EntireRow.Cells(j, intsheet2fullname)) Then
                        rngsheet1fullname.Cells(i, intsheet1fullname).Interior.ColorIndex = 4
                    End If
                    If (rngSheet1DOB.EntireRow.Cells(i, intSheet1DOB) = rngSheet2DOB.EntireRow.Cells(j, intSheet2DOB)) Then
                        rngSheet1DOB.Cells(i, intSheet1DOB).Interior.ColorIndex = 4
                    End If
           End If
            Next
            If (rng.EntireRow.Cells(i, intSheet1IDCol).Interior.ColorIndex = xlNone) Then
                            rng.Cells(i, intSheet1IDCol).Interior.ColorIndex = 3
            End If
            If (rngsheet1fname.EntireRow.Cells(i, intsheet1fname).Interior.ColorIndex = xlNone) Then
                            rngsheet1fname.Cells(i, intsheet1fname).Interior.ColorIndex = 3
            End If
            If (rngSheet1DOB.EntireRow.Cells(i, intSheet1DOB).Interior.ColorIndex = xlNone) Then
                            rngSheet1DOB.Cells(i, intSheet1DOB).Interior.ColorIndex = 3
            End If
            If (rngsheet1reltype.EntireRow.Cells(i, intsheet1reltype).Interior.ColorIndex = xlNone) Then
                            rngsheet1reltype.Cells(i, intsheet1reltype).Interior.ColorIndex = 3
            End If
            If (rngsheet1sex.EntireRow.Cells(i, intsheet1sex).Interior.ColorIndex = xlNone) Then
                            rngsheet1sex.Cells(i, intsheet1sex).Interior.ColorIndex = 3
            End If
            If (rngsheet1salcode.EntireRow.Cells(i, intsheet1salcode).Interior.ColorIndex = xlNone) Then
                            rngsheet1salcode.Cells(i, intsheet1salcode).Interior.ColorIndex = 3
            End If
            If (rngsheet1mname.EntireRow.Cells(i, intsheet1mname).Interior.ColorIndex = xlNone) Then
                            rngsheet1mname.Cells(i, intsheet1mname).Interior.ColorIndex = 3
            End If
            If (rngsheet1scode.EntireRow.Cells(i, intsheet1scode).Interior.ColorIndex = xlNone) Then
            
                            rngsheet1scode.Cells(i, intsheet1scode).Interior.ColorIndex = 3
            End If
            If (rngsheet1fullname.EntireRow.Cells(i, intsheet1fullname).Interior.ColorIndex = xlNone) Then
                            rngsheet1fullname.Cells(i, intsheet1fullname).Interior.ColorIndex = 3
            End If
            If (rngSheet1DOB.EntireRow.Cells(i, intSheet1DOB).Interior.ColorIndex = xlNone) Then
                            rngSheet1DOB.Cells(i, intSheet1DOB).Interior.ColorIndex = 3
            End If
            
        Next
    Next cell
Next Row
'For Each row In rngNew.Rows
'    For Each cell In row.Cells
'        For i = 2 To intCount
'            For j = 2 To intCount1
'            cell.Value = LCase(cell.Value)
'            Next
'        Next
'    Next cell
'Next row

Set wks = Nothing: Set wksb = Nothing: Set wks3 = Nothing
'
'With Excel.Application
'        .ScreenUpdating = True
'        .Calculation = Excel.xlAutomatic
'        .EnableEvents = True
'End With


'End Sub

'Public Sub UserForm_Activate()
'
'Dim ws As Worksheet
'Dim rang As Range
''Set ws = Worksheets("sheet1")
'Set rang = Worksheets("sheet1").Cells
'rang.EntireRow.Interior.ColorIndex = xlNone
'
''For Each Row In wks.Rows
''    For Each cell In Row.Cells
''        .EntireRow.Cells.Interior.ColorIndex = xlNone
''    Next cell
''Next Row
'End Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

End Sub

How to Map 2 different columns in from 2 different excel files?

1 response

Subscribe To Our Newsletter!