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

 

[Fluff]

mumps is helping you, so please be patient. You need to remember that everything here is done on a voluntary basis.

 

[dineshkummar]

Sure I will wait.. You guys are really awesome in supporting ....
Cheers

 

[dineshkummar]

I need another help apart ESL_Inventory from I column, need to get data's from B,D and O has check and copy in E,I and J of Patching_MainSheet,

Patching_MainSheet I have 292 data's in D column
ESL_Inventory I have 1844 datas in A column

Pls requesting to rearrange the script as like mentioned above loops and also with above numbers..

Thanks in Advance..

 

[dineshkummar]

Any support guys ??

 

[mumps]

Use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of both sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[dineshkummar]

I have share the file pls find link :

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Need Macros as below :

In Patching_MainSheet D column have Hostname and ESL_Inventory Sheet A Column have Hostname.

Need Macros to take one Hostname from Patching_MainSheet of D column and need to compare with Hostname of A Columns of ESL_Inventory Sheet if it matches, then it have to take one particular value from SYSTEM NAME in B column,IS VIRTUAL YES/NO in D Column,Data Country in I column, with respect to hostname and write down in DC COUNTRY in B column,SYSTEM NAME in E column, IS VIRTUAL(Yes/No) in I column, ENVIRONMENT in J column of Patching_MainSheet respectively.

 

[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")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 15).Value
    v2 = desWS.Range("D2", desWS.Range("D" & Rows.Count).End(xlUp)).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) & "|" & v1(i, 2) & "|" & v1(i, 4) & "|" & v1(i, 15)
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            With desWS
                .Range("B" & i + 1) = Split(dic(v2(i, 1)), "|")(0)
                .Range("E" & i + 1) = Split(dic(v2(i, 1)), "|")(1)
                .Range("I" & i + 1) = Split(dic(v2(i, 1)), "|")(2)
                .Range("J" & i + 1) = Split(dic(v2(i, 1)), "|")(3)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[dineshkummar]

It is working as expected, Thank you !!! You guys are really awesome and more helpful.. you saved my manual work...
Thanks once Again !!!

 

[mumps]

You are very welcome.