Search code examples
excellookupvlookupexcel-match

How to map the sheet 1 data with sheet 2 with help of Unique ID?

I have created an excel sheet with the attributes ID, NAME in one sheet.

SHEET 1:

enter image description here

In another sheet, I have names in a random manner and they have to be mapped to their correct ID with the reference from SHEET-1 data.

SHEET 2:

enter image description here

The highlighted values are manually entered. Is there any formula that can populate the data automatically.

NOTE: The above data is sample set of data and original data differs in sheet 2. There will be more than 2 columns in the second sheet.

Solution

  • I got my solution after working with VBScript.

    Private Sub GetID_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDefaultID = ThisWorkbook.Sheets.Item("DefaultIDs")
Set objGetID = ThisWorkbook.Sheets.Item("GetIDs")
iDefaultRC = objDefaultID.UsedRange.Rows.Count
iDefaultCC = objDefaultID.UsedRange.Columns.Count
iGetRC = objGetID.UsedRange.Rows.Count
iGetCC = objGetID.UsedRange.Columns.Count
For i = 1 To iGetCC
    If objGetID.Cells(1, i) = "Name" Then
        iGetNameCol = i
    ElseIf objGetID.Cells(1, i) = "ID" Then
        iGetIDCol = i
    End If
Next
For i = 1 To iDefaultCC
    If objDefaultID.Cells(1, i) = "Name" Then
        iDefNameCol = i
    ElseIf objDefaultID.Cells(1, i) = "ID" Then
        iDefIDCol = i
    End If
Next
For i = 2 To iGetRC
    For j = 2 To iDefaultRC
        If objGetID.Cells(i, iGetNameCol) = objDefaultID.Cells(j, iDefNameCol) Then
            objGetID.Cells(i, iGetIDCol) = objDefaultID.Cells(j, iDefIDCol)
        End If
    Next
Next

End Sub