Macro compare cells and paste to columns

[Nu2Java]

I am not really sure what to call this code, I found it here in this forum and it works quite well for what I need. The only problem is, I do not understand how the code works and would GREATLY appreciate someones time to write a few lines to explain how this code works (starting from "With CreateObject") so I can learn how to modify it to fit my needs. Many thanks ...

Sub checkParts()
 Const sh1 As String = "sheet1" '<-- Change for your needs
 Const sh2 As String = "sheet1" '<-- Change for your needs
 Dim a, i As Long, ii As Long
 
 With Worksheets(sh1)
    a = .Range("a1").CurrentRegion
 End With
 
 With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(a)
        If Not .exists(a(i, 2)) Then
            .Item(a(i, 2)) = Join(Array(a(i, 1), a(i, 2), a(i, 3)), "|")
        Else
            .Item(a(i, 2)) = Join(Array(.Item(a(i, 2)), a(i, 1), a(i, 2), a(i, 3)), "|")
        End If
    Next
    a = .items
 End With
 
 With Worksheets(sh2)
  '  .Cells.Clear
    For i = 0 To UBound(a)
        x = Split(a(i), "|")
        For ii = 0 To UBound(x)
            .Cells(i + 1, ii + 1) = x(ii)
        Next
    Next
 End With
End Sub

[Nu2Java]

What I want to do with this code is make a fourth a column. It seems to find the match from the second column, but if I add a fourth, it gets all messed up. Since I don't understand the code, I am not sure what needs to be changed. Any help would be greatly appreciated.

[AB33]

This code is written by Mike7952. I think he is around and should be able to help you out.

[Nu2Java]

Thanks AB33, I sent him a request asking for help.

[mike7952]

Can you upload the workbook that your working with?

[Nu2Java]

Thanks for the reply Mike.... Here is the workbook. I need to add a column for Name and Job number. The important column is the "Part Number" column. I have tried a few things to get what I need, but the results have been not so good. Thanks very much for your help.

[Nu2Java]

I am calling the macro on workbook open, which is fine for now. I just need to add more columns with the Part Number column being the one to compare and then paste along side like it does with what is there now.

[mike7952]

Im a little confussd of what you are wanting the results to be? Are you wanting to call the macro from the form?

[Nu2Java]

Ultimately, I would like to have the Part Number column first, which is the one I want to use for the compare method. So I would have it like this:

Column A Column B Column C Column D Column E
Part Number Date/Time Job Operator Part IN/OUT

[mike7952]

Im stiil confused some. With the data that is showing on sheet1 can you show on sheet2 your expected results?

[Nu2Java]

Ooops, sorry about that. I changed it to all show on sheet1 for testing. Right now I have the 3 columns... and that works great. I need to add more columns, but the way it works now, it will overwrite the data. On Sheet1, you will see part number "CA197-01" and then you see it again a few lines down which tells me the part came back in. When the macro runs, It will then paste that entire line next to the line it matches up with.

So the way I am wanting to use this is, a bunch of parts get scanned OUT.... I just need to find which ones got scanned back IN. I hope I am making sense.

[mike7952]

Can you upload another workbook that has the other columns that you want to add, and not call the macro from the thisworkbook.open event?

[Nu2Java]

Mike, Here is the workbook showing what I need. The userform will be filling in this data. So from the worksheet, all 3 part numbers went "OUT" and all 3 came back "IN", so there will be a part number match for all three and the data can copy to sheet2 when the macro runs. Your code works well, I just want to understand how it works so I can modify it with your help.

[mike7952]

This what you want?

Sub checkParts()
 Const sh1 As String = "sheet1" '<-- Change for your needs
 Const sh2 As String = "sheet2" '<-- Change for your needs
 Dim a, i As Long, ii As Long
 
 With Worksheets(sh1)
    a = .Range("a1").CurrentRegion
 End With
 
 With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(a)
        If Not .exists(a(i, 1)) Then
            .Item(a(i, 1)) = Join(Array(a(i, 1), a(i, 3), a(i, 2), a(i, 4), a(i, 5)), "|")
        Else
            .Item(a(i, 1)) = Join(Array(.Item(a(i, 1)), a(i, 3), a(i, 2)), "|")
        End If
    Next
    a = .items
 End With
 
 With Worksheets(sh2)
    .Cells.Clear
    For i = 0 To UBound(a)
        x = Split(a(i), "|")
        For ii = 0 To UBound(x)
            .Cells(i + 1, ii + 1) = x(ii)
        Next
    Next
 End With
End Sub

[Nu2Java]

This is very very close... can I have it copy all columns for each match of the part number? I need that since the operator taking them them IN will be different from the one taking them OUT. I see this is copying the date and IN only

[mike7952]

You mean like this. Just replace the for loop in the currunt code with the one below

    For i = 1 To UBound(a)
        If Not .exists(a(i, 1)) Then
            .Item(a(i, 1)) = Join(Array(a(i, 1), a(i, 3), a(i, 2), a(i, 4), a(i, 5)), "|")
        Else
            .Item(a(i, 1)) = Join(Array(.Item(a(i, 1)), a(i, 3), a(i, 2), a(i, 4), a(i, 5)), "|")
        End If
    Next

[Nu2Java]

Thanks Mike .... I think this will work perfectly. I appreciate your time and help.