Compare data in columns of two worksheets and return value if match.

Hello all..

any help with this problem will be much appreciated.

here is what I am working on...

I have a workbook with two sheets (sheet1 and sheet2)

Sheet1 has a list of information in columnB
Sheet2 has a list of information in columnI.

I have foundand modified some code to compare the information in sheet2-columnI, to sheet1-columnB.. if they match, then it will paste the value from sheet2-columnS into Sheet1-columnC for the proper match.
(comfusing?? i hope not)

the code works great and does exactly what I needed, [B]except[B] it only works if Sheet2 has unique values.

Sheet2-ColumnI will have repeated values.

what I am wanting for it to do is... if it has a repeated value, it will add them together and paste that into the cell next to the proper match.

below is the code I am currently using...
again, it works fine but it will not return any values if they are duplicated.

Sub test()

Dim rng2 As Range, c2 As Range, cfind As Range
Dim x, y

    With Worksheets("sheet1")
    Set rng2 = Range(.Range("B2"), .Range("B2").End(xlDown))
    For Each c2 In rng2
    x = c2.Value
        With Worksheets("sheet2").Columns("I:I")
        On Error Resume Next
        Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind Is Nothing Then GoTo line1
        y = cfind.Offset(0, 10).Value
        End With
    c2.Offset(0, 2) = y
line1:
    Next c2
    End With

End Sub

Thank you for any suggestions, and maybe this particular code will not work for me...

suggestions???

thank you!!

ok.. so I made a quick sample file with some data... don't mind all the X's in it, it is data that needed to be hidden..

attached is the file, you will notice that it will only pick up the first instance of any matches, and not add up any duplicates..

sample1.xlsm

Different approach.
The only down side with it is I have to reduce sheet 2 in order to match with sheet1. The other option would be to copy these values in to new row(sheet).

Originally Posted by AB33

Different approach.
The only down side with it is I have to reduce sheet 2 in order to match with sheet1. The other option would be to copy these values in to new row(sheet).

AB33.. thank you for your response.. I will investigate your approach tomorrow when I return to work.

as my preliminary tests here at home seemed to have work, i will test it with more data lines in the morning.

I looked at your code, could you explain how it is working, so that I can learn as well?? it is interresting and I have never used 'scripting dictionary' before..

thank you

AB33,

again thank you.. I have tested this with more data and it is working great! I am not too worried about the resizing of the one sheet, as in teh final code this will just be a placeholder for the temporary information and cleared when all the results have been aquired.. (a lot more data to manipulate still but this gives me a start).

I will mark this thread as "solved" as your suggestion has done the task.

thank you!!