Copying data based on criteria

[Pujsek]

Hello everybody,

I have a big excel file (around 7600 rows) which contains information about online store products. I would like to update the stock numbers of the products. The new stock numbers come from another excel file which I got from the supplier. The SKUs of the products are the same and are found in column A in both files. However, the number of rows and the order is not the same so I can't simply copy and paste the desired data. The new stock numbers are found in column H of the second file and I would like to copy them into column S of my file.

I hope someone can help me with this.

Thank you for your answers.

Best regards,

Sašo

[PaulM100]

One way, untested:

Please Login or Register  to view this content.

Change the colored part as per your actual files

[Pujsek]

Hello Paul,

Thank you for your reply.

If I understand correctly, the blue part should be replaced with file path of the suppliers file, the green part should be the name of the sheet in my file and the red part should be the name of the sheet in supplier's file?

Would this be easier if I simply copy the data from supplier's file and add it to a second sheet in my own file?

[PaulM100]

You are correct. It could be simpler. and if are gonna do it manually, then you will need just a vlookup to make it work.

[Pujsek]

Would you be so kind and send me the formula that would do as described in my original post, keeping in mind that my data will be on Sheet 1 and suppliers data will be on Sheet 2 of the same file?

[AliGW]

You have posted in the VBA section, which is why you have been offered code - do you want a formula solution instead?

[Pujsek]

I posted here because I started with two different files but figured that this could be much simpler if I simply combine both files into a single one with two sheets. If I am correct and a formula solution would be simpler, then yes I would like that one. Sorry for the confusion but I am a total n00b when it comes to Excel. All I know is that there a lot of things one can do with it.

[PaulM100]

In Sheet1 S2 and down =iferror(index(sheet2!$h:$h,match($A2,sheet2!$A:$A,0)),"")

[Pujsek]

Thank you for the formula. However, Excel is returning error when trying to execute it.

[PaulM100]

Based on your sample:=IFERROR(INDEX('Suppliers data'!$H:$H,MATCH($A2,'Suppliers data'!$A:$A,0)),"")

[Pujsek]

I've changed the name of the sheet when trying out the formula and I have now tried the second one. Still nothing.

[PaulM100]

Check this tutorial: https://corporatefinanceinstitute.co...formula-excel/

[Pujsek]

Thanks for your help. I was following the tutorial to the letter but I can't get it to work no matter what I try. I even tried pasting the suppliers' data into my own sheet and still nothing.

[AliGW]

Are you getting an error message? Did you change commas to semi-colons for your locale?

=IFERROR(INDEX('Suppliers data'!$H:$H;MATCH($A2;'Suppliers data'!$A:$A;0));"")