Required Macros to compare the value and need to take unique value and write in another column

[dineshkummar]

I have two Excel sheets with named :

Patching_MainSheet and ESL_Inventory

In Patching_MainSheet D column have 100 Servernames and ESL_Inventory Sheet A Column have 1845 Servernames and also I column have DataCentre name.
I need Macros to take one servername from Patching_MainSheet of D column and need to compare with 1845 Servernames in A Columns of ESL_Inventory Sheet if it matches, then it have to take one particular value(DataCentre Name) which is present I columns in ESL_Inventory and write down in B column of Patching_MainSheet.
Anyone guide me to prepare macros for this..

Thanks DK

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).

 

[dineshkummar]

Those are office sensitive data's i cant share it here in open forum

 

[dineshkummar]

DC Name	Host Name
	WSAP01
	WSAP02
	CDCP01
	CDCP02
	DFCP04
	DFCP06
	REGP09
	EDCP05
	GECP07

Host Name	DC Name
REGP09	London
CDCP02	Slovika
WSAP02	India
WSAP01	Germany
GECP07	France
WSAP02	UK
DFCP04	France
EDCP05	Spain
DFCP06	India

Pls find the sample sheet..

I have two Excel sheets with named :

Patching_MainSheet and ESL_Inventory

In Patching_MainSheet D column have 100 Servernames and ESL_Inventory Sheet A Column have 1845 Servernames and also I column have DataCentre name.
I need Macros to take one servername from Patching_MainSheet of D column and need to compare with 1845 Servernames in A Columns of ESL_Inventory Sheet if it matches, then it have to take one particular value(DataCentre Name) which is present I columns in ESL_Inventory and write down in B column of Patching_MainSheet.
Anyone guide me to prepare macros for this..

 

[dineshkummar]

any help guys ?

 

[dineshkummar]

anybody there to help here to prepare macros ?

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object, i As Long
    Set desWS = Sheets("Patching_MainSheet")
    Set srcWS = Sheets("ESL_Inventory Sheet")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value
    v2 = desWS.Range("D2", desWS.Range("D" & Rows.Count).End(xlUp)).Resize(, 9).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), v1(i, 9)
        End If
    Next i
    For i = LBound(v2) To UBound(v1)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("A" & i + 1) = dic(v2(i, 1))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub[CODE=vba]

[/CODE]

 

[dineshkummar]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object, i As Long
    Set desWS = Sheets("Patching_MainSheet")
    Set srcWS = Sheets("ESL_Inventory Sheet")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value
    v2 = desWS.Range("D2", desWS.Range("D" & Rows.Count).End(xlUp)).Resize(, 9).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), v1(i, 9)
        End If
    Next i
    For i = LBound(v2) To UBound(v1)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("A" & i + 1) = dic(v2(i, 1))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub[CODE=vba]

[/CODE]

really appreciated for quick response and thanks for the Macros,

when I ran the macros i am getting error Run-time error '9'
Subscript out of range
End Debug

 

[dineshkummar]

really appreciated for quick response and thanks for the Macros,

when I ran the macros i am getting error Run-time error '9'
Subscript out of range
End Debug

 

[Fluff]

Looks like there is a slight typo, try it like

    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then