Compare two excel datasets (worksheets) & add value

Question

i have to compare two worksheets and add values on Worksheet 1 if a match is success.

Worksheet1 = Worksheet where i have to add values in Column B (the value is a string) Worksheet2 = List of Data which will be used for search in Worksheet1

Worksheet1:

Column A.........................Column B

2016_ABC

2016_XYZ

2015_ABC

2013_XYZ

Worksheet2:

Column A

ABC

XYZ

DJH

UIJ

So, what i want is a formula or simple way (please not VBA, i already have a script in vba - but i have to keep it easy for customer) that compares Values in Worksheet2/Column A with Worksheet1/Column A.

I cannot compare with an exact MATCH, i have to use something like "CONTAINS".

If values in Worksheet2 Column A contains in Worksheet1 ColumnA add in same Row Worksheet1 Column B a string like "Contain!"

Thanks in advice. Have a nice day.

Have you tried searching for a contains formula? stackoverflow.com/questions/18617175/… — Tim Wilkinson
If you do not want a VBA solution why have you included the vba and excel-vba tags? Unless there is non-obvious reason for their inclusion, you should remove these tags — Tony Dallimore

chrs04 chrs04 · Accepted Answer · 2016-11-24T07:34:31

Cant figure that out.

Here is my VBA code:

For i = 1 To lastrowsheet1

activeteststring = Worksheets("devices").Cells(i, 1).Value

    For x = 6 To lastrowsheet2
        checkstring = Worksheets("sessions").Cells(x, 2).Value

        If InStr(checkstring, activeteststring) <> 0 Then

            Worksheets("sessions").Cells(x, 12).Value = Worksheets("devices").Cells(i, 2).Value

        End If



    Next x

Next i

Compare two excel datasets (worksheets) & add value

1 Answers