VBA Compare column data from two wordbooks and show results

[dutchmhk]

Hi all

First of all - I've had excellent help from you guys in here, a few days ago with another VBA issue that I had. It was solved with a few lines of code within hours of posting the thread - AMAZING! Thank you so much!

So here goes for my second issue:
I've been trying to compare data from 2 columns (A4:500 & B4:500) in workbook1, sheet2 with data from column "F" (F4:500) in workbook2, sheet2. If there is a match in column "F" (workbook2, sheet2) from column "A" (workbook1, sheet2) on a specific row in column "F", then it should copy the text from column A row 3 (workbook 1, sheet2) to column "H", (workbook2, sheet2) to that same specific row. And if there is a match in column "F" (workbook2, sheet2) from column "B" (workbook1, sheet2) then copy the text from column B row 3 to the corresponding row in column "H"

I have "reg market" in A3 and "non-reg market" B3 for workbook1
and the idea is to compare 496 (could end up being more going forward) rows of stock from the two columns with imported data in column F in workbook2 and then indicate in column H if the stock is regulated or not

I hope it makes somewhat sense

Any help is much appreciated

Tnx
Dutch

 

[Crystalyzer]

Are both files open when you want this to occur? If not, what are the file locations? Do you want them to be closed when this is completed or remain open?

 

[dutchmhk]

Are both files open when you want this to occur? If not, what are the file locations? Do you want them to be closed when this is completed or remain open?

Sorry for not replying earlier, yes, I will be having both files open.
A thing I forgot to mention as well is; I expect a few new stocks in column F each time I import data to it. So If the code works correctly, then a few stocks in column F will have a corresponding blank field in column H since they were not found in either column A or B (wordbook1, sheet1) - this is intentional as it will force me to research those stocks and place them in the correct "market"

Hope it makes sense

br.

 

[Fluff]

How about

Sub dutchmhk()
   Dim wbkA As Workbook, wbkB As Workbook
   Dim Cl As Range
   Dim Dic As Object
   
   Set wbkA = Workbooks("A.xlsm")
   Set wbkB = Workbooks("B.xlsm")
   Set Dic = CreateObject("Scripting.dictionary")
   
   With wbkA.Sheets("sheet2")
      For Each Cl In .Range("A4", .Range("A" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = "reg market"
      Next Cl
      For Each Cl In .Range("B4", .Range("B" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = "non-reg market"
      Next Cl
   End With
   With wbkB.Sheets("Sheet2")
      For Each Cl In .Range("F4", .Range("F" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Cl.Offset(, 2).Value = Dic(Cl.Value)
      Next Cl
   End With
End Sub

 

[Peter_SSs]

You could test to see if this does what you want.

Sub Check_Reg()
  Dim wb1 As Workbook, wb2 As Workbook
  
  Set wb1 = Workbooks("dutchmhk_1.xlsm")  '<- Edit to your workbook name
  Set wb2 = Workbooks("dutchmhk_2.xlsm")  '<- Edit to your workbook name
  With wb2.Sheets("Sheet2")
    With .Range("H4:H" & .Range("F" & Rows.Count).End(xlUp).Row)
      .Formula = Replace("=IF(ISNUMBER(MATCH(F4,#A$4:A$1000,0)),#A$3,IF(ISNUMBER(MATCH(F4,#B$4:B$1000,0)),#B$3,""""))", "#", "'[" & wb1.Name & "]Sheet2'!")
      .Value = .Value
    End With
  End With
End Sub

 

[Peter_SSs]

If the code above does what you want, it could also be reduced to this with editing the workbook names in the two lines indicated.

Sub Check_Reg()
  With Workbooks("dutchmhk_2.xlsm").Sheets("Sheet2")  '<- Edits wb name
    With .Range("H4:H" & .Range("F" & Rows.Count).End(xlUp).Row)
      .Formula = Replace("=IF(ISNUMBER(MATCH(F4,#A$4:A$1000,0)),#A$3,IF(ISNUMBER(MATCH(F4,#B$4:B$1000,0)),#B$3,""""))", "#", "'[dutchmhk_1.xlsm]Sheet2'!")  '<- Edit wb name
      .Value = .Value
    End With
  End With
End Sub

 

[dutchmhk]

If the code above does what you want, it could also be reduced to this with editing the workbook names in the two lines indicated.

Sub Check_Reg()
  With Workbooks("dutchmhk_2.xlsm").Sheets("Sheet2")  '<- Edits wb name
    With .Range("H4:H" & .Range("F" & Rows.Count).End(xlUp).Row)
      .Formula = Replace("=IF(ISNUMBER(MATCH(F4,#A$4:A$1000,0)),#A$3,IF(ISNUMBER(MATCH(F4,#B$4:B$1000,0)),#B$3,""""))", "#", "'[dutchmhk_1.xlsm]Sheet2'!")  '<- Edit wb name
      .Value = .Value
    End With
  End With
End Sub

Hi peter

The code worked perfectly - and has a very fast execution speed. you really see the difference when the code has to compare 3 columns with 500 rows of data

Again - like last time - Thank you very much. This really makes a difference in time spent running through all these stocks and categorizing them against market types.

Best regards
Dutch

 

[dutchmhk]

How about

Sub dutchmhk()
   Dim wbkA As Workbook, wbkB As Workbook
   Dim Cl As Range
   Dim Dic As Object
  
   Set wbkA = Workbooks("A.xlsm")
   Set wbkB = Workbooks("B.xlsm")
   Set Dic = CreateObject("Scripting.dictionary")
  
   With wbkA.Sheets("sheet2")
      For Each Cl In .Range("A4", .Range("A" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = "reg market"
      Next Cl
      For Each Cl In .Range("B4", .Range("B" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = "non-reg market"
      Next Cl
   End With
   With wbkB.Sheets("Sheet2")
      For Each Cl In .Range("F4", .Range("F" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Cl.Offset(, 2).Value = Dic(Cl.Value)
      Next Cl
   End With
End Sub

Hi FLuff

Thank you very much - your code worked perfectly

I ended up using Peters solution due to execution speed since I have a lot of data to go through. But I really appreciate your help! you guys are amazing. This is the first Forum I have joined, where help is provided within a few hours of posting.

Thank again
Dutch

 

[Peter_SSs]

You're welcome. Glad we could help.