Match 2 columns text and paste 3rd column value to corresponding cells of another sheet????

[PJohnson_Construction]

Hello,
I am perplexed with this code and read many threads and can't seam to get it right.

what I am trying accomplish is this:
take 3 columns of data (A,C,&D) from sheet2("job1"), then
match by exact text of columns C&D to columns C&D in sheet1("overview"). If there is a match, then paste corresponding value of column A from sheet2("job1") into column E of sheet1("overview") but on the row of the matching data.

the data set could reach upto 400 rows.

I also will have other sheets that I will want to run this macro on that will then paste matching values to columns F,G, etc. on sheet 1("overview"). The idea is to be able to extract the data, in this case... quantity of a given item... against a total quantity of that same item on sheet1. (I could figure this part of easily once it was working for one sheet)

Hopefully I explained it clear enough. Sorry I don't have code to post, it is so off at this point that it is just better to explain the overall goal.

Any help is greatly appreciated.
-PJ

 

[JackDanIce]

Welcome to the board. Try:

Sub Macro1()

    Dim dic     As Object
    Dim arr()   As Variant
    Dim x       As Long
    Dim str     As String

    Const Delim As String = "|"
        
    Set dic = CreateObject("Scripting.Dictionary")
        
    Application.ScreenUpdating = False
    
    With Sheets("job1")
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(x, 4).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 3) & Delim & arr(x, 4)) = arr(x, 1)
    Next x
    Erase arr
    
    With Sheets("overview")
        x = .Cells(.Rows.Count, 3).End(xlUp).row
        arr = .Cells(1, 3).Resize(x, 3).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            str = arr(x, 1) & Delim & arr(x, 2)
            If dic.exists(str) Then
            arr(x, 3) = dic(arr(x, 1) & Delim & arr(x, 2))
        Next x
        
        .Cells(1, 3).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Set dic = Nothing
    
End Sub

 

[PJohnson_Construction]

@JackDanIce
Thank you so much for your effort!! This is very helpful, however I am receiving a (Compile error: Next without For) at the "Next x" line in the with/end with section of the sheets("overview").

I will note that I am inserting this code into a command button on the sheets("overview") worksheet.

Thanks again!!

 

[JackDanIce]

Typo, try:

Sub Macro1()


    Dim dic     As Object
    Dim arr()   As Variant
    Dim x       As Long
    Dim str     As String


    Const Delim As String = "|"
        
    Set dic = CreateObject("Scripting.Dictionary")
        
    Application.ScreenUpdating = False
    
    With Sheets("job1")
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(x, 4).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 3) & Delim & arr(x, 4)) = arr(x, 1)
    Next x
    Erase arr
    
    With Sheets("overview")
        x = .Cells(.Rows.Count, 3).End(xlUp).row
        arr = .Cells(1, 3).Resize(x, 3).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            str = arr(x, 1) & Delim & arr(x, 2)
            If dic.exists(str) Then arr(x, 3) = dic(arr(x, 1) & Delim & arr(x, 2))
        Next x
        
        .Cells(1, 3).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Set dic = Nothing
    
End Sub

 

[PJohnson_Construction]

@JackDanIce
Thank you once again. It seems to be working however I can't tell completely because I had to add a column to sheets("overview"), so now the paste column needs to be column F, instead of before it was column E. I tried messing with the numbers, but couldn't get it to move over a column without overwriting column E?

I will need to know how to adjust this for my consecutive sheets anyhow, so I guess its good to address now. SO MANY THANKS AGAIN.

 

[JackDanIce]

Code adjusted, change number in blue, it represents the column index (i.e. "A" is 1, "B" is 2, ... "F" is 6, etc), try:

Sub Macro1()
        
    Dim x       As Long
    Dim LR      As Long
    Dim dic     As Object
    Dim str     As String


    Const Delim As String = "|"
        
    Set dic = CreateObject("Scripting.Dictionary")
        
    Application.ScreenUpdating = False
    
    With Sheets("job1")
        LR = .Cells(.Rows.Count, 1).End(xlUp).row
        For x = 1 To LR
            dic(.Cells(x, 3).Value & Delim & .Cells(x, 4).Value) = .Cells(x, 1).Value
        Next x
    End With
        
    With Sheets("overview")
        LR = .Cells(.Rows.Count, 3).End(xlUp).row
        For x = 1 To LR
            str = .Cells(x, 3).Value & Delim & .Cells(x, 4).Value
            If dic.exists(str) Then .Cells(x, 6).Value = dic(str)
        Next x
    End With
    
    Application.ScreenUpdating = True
        
    Set dic = Nothing
    
End Sub

 

[PJohnson_Construction]

@JackDanIce

I meant to reply last night. I got it working as needed after messing around and studying the code. I also had to increase the string size in the line above the column index number or else it pushed back an error saying it was out of range.

ALL IN ALL, I CAN'T THANK YOU ENOUGH!! It is working great now. I do have another code I'm working on that is fairly simple in comparison, but I can't seem to get it 100% accurate. It is just supposed to copy a dynamic range from another workbook and paste into this one that you wrote the other code for. When I get around to posting it, I will tag you. Thanks so much. YOU ARE THE MAN

 

[JackDanIce]

You're welcome, glad you figured out your own fix too.

 

[PJohnson_Construction]

@JackDanIce

I just now got back into this wkb and using this code. I've discovered that the line items (#quantities from column "A" from sheet2("job1")) that gets populated into column E of sheet1("overview") are sometimes being doubled up in two different rows. Some are accurate, but some aren't. When running the code on this specific case, I would say about 20 values from column A from sheet2("job1") were pasted into column E of sheet1("overview") based on matching text from columns C&D of both sheets. In those 20, about 5 or so were doubled up, making the report inaccurate.

Any insight as to why this is happening??

Thanks!