Compare two values in different WBs' or sheets' cells and return data

Try the attached.

Please Login or Register  to view this content.

Originally Posted by PCI

A formula could do the job
E3==IF(SUMPRODUCT((Sheet2!$C$3:$C$26=$C3)*(Sheet2!$E$3:$E$26=E$2))=1;E$2;"")

This works, but seems to be too heavy with around 1500 product/rows.
Excel stops working.

Originally Posted by jindon

Try the attached.

Please Login or Register  to view this content.

Thank you! This does the trick but since this code is way too advanced for my skills, I don't know how to change and implement this code to my actual masterdata and target file.

I also need that the code does not copy the search attribute from Row2 because if I change it to anything else, then it does not work as it should.
So it should check what the property is in Row2, then do the search based on that criteria.


I attached the actual masterdata file and the target file format, so you can see what I try to accomplish a bit better maybe.

The properties listed are in MasterDataSample-file in column M --> these properties might change or there can be more others someday, so I must be able to determine the text / search criteria in TargetFileExample Row1 / columns U:AM .

I need to have the data brought to TargetFileExample columns U:AM, starting from Row2.

I added the files above in a new post.

Originally Posted by PCI

Another one

Please Login or Register  to view this content.

Maybe I don't know how to use this but it gives an error "Object required" and the code stops in

'For Each Rg In WkRg'

If this is not what you wanted, edit or create the code for yourself.

Please Login or Register  to view this content.

Originally Posted by jindon

If this is not what you wanted, edit or create the code for yourself.

Please Login or Register  to view this content.

I appreciate the help, but the code does not work. It stops to line

'If a(i, 2) <> "" Then'


I don't know how to fix this so I will continue to find some other options.

Thank you anyway.

Error? no way.
TargetFileSample.xlsx must be open.

Originally Posted by jindon

Error? no way.
TargetFileSample.xlsx must be open.

With Workbooks("targetfilesample.xlsx") <-- I changed this to my actual target filename.

It works with this example, but for some reason when I change the workbook name in the code to match the my actual target file, it gives the error, even if the target file is open.