Need to copy data cells from one sheet to another based on unique identifier

Hi there,

I am new here and I just registered in this forum to post this thread, therefore please be kind to me. I may become a regular poster, who knows?

I am struggling with something that I believe is quite simple to resolve, but my excel's skills are too naive for it. I tried to find a solution online but I did not have any success. Luckily I found this forum, so I decided to give it a try

I have an online web store that sells perfumes. I get stock information sent to me from the suppliers in the forms of excel sheets in CSV format. I have my own excel sheets of course which are in xlsx and I update the information regularily. My job is becoming more tiring as my stock in increasing, so is the time required for maintaining its information such as prices, quantity, etc... I decided that I should begin automating my work (so far so good, I managed to achieve most of the tasks I wanted to do and I am quite surprised with myself, I guess it is all part of the learning process).

So, I began learning how to link the data I am getting from my suppliers with my own data. The way I do it is by having two sheets (tabs) in my worksheet file, one is called "MAIN" which is obviously my main work area, and the other sheet is called "WEB" which is the supplier's data that I just copy from their source file and paste to this sheet.

I thought I figured out how to link data properly by using conditional formating. The format I use is something similar to this: =WEB!$F$4 , where "WEB" is the name of the sheet I am copying from, "F" is the column and "4" is the row of the required cell. Lately I discovered that my supplier is constantly changing the orders of the rows in their excel file, which made my work useless. I need to figure out a way to link the data to their relevant destination in my main file regardless of the changes the supplier is making. The only way I think could work is if I could take advantage of the unique identification number. Both me and my supplier are using exactly the same numbers, which are also known as SKU (Stock Keeping Unit). These numbers never change in our files despite the changes happening in the orders and locations of rows in supplier's data.

I have attached two photos for you to explain what I am doing. These images represent my "MAIN" sheet and "WEB" sheet which are both inside the same sample excel file. In "MAIN" sheet you will see an example of a perfume I am selling, called Rococo Rouge, and you will see the SKU number of this perfume in the yellow area underlined by two red lines as well as Fixed Cost Price ("Fx Cost" column) which is the data I am copying from the supplier's web sheet. As you can see in the photo (circled in red) above, I am using the formula I told you about, inside the "Fx Cost" cell. The second photo is the "WEB" sheet from the supplier, there you will find four perfumes in which one of them is the same Rococo perfume in my main sheet, you can tell which one it is by looking at the value in column A which is the same unique SKU number that I am using (451034), pointing at it with a red arrow. You will also see the value of the price that I am copying to my other sheet (circled in red) which is the one I need to exactly match regardless of any changes in file. For this example the location of the data for this particular perfume is F4, but unfortunately it is not always going to exactly be in the same spot. The column F never changes, but the rows are always changing, so today it is F4, tomorrow it might become F10 and after tomorrow it could become F55, I will never know!

1-MAIN.png

______________________________________________________________________

2-WEB.png

To summarize: Here is my vision of what I want to achieve:

* Copy data from "WEB" to "MAIN" where the column is F and the cell is in the row that has the value of "451034" in column A *

I hope that I made my points clear and I am very very thankful for all the help I could get from you. I am even thinking about subscribing to add some paid points in order to get a professional support, but I thought that my query might be too simple that somebody in the free forum could solve it for me easily, therefore I made my mind that I should try the free forum first before spending some money on a paid support that I may not really need.

I wish you all, my best regards.
Zed.

Welcome to the forum
Here one workbook often means more than 100 pictures (see some other advice and requirements in our http://www.excelforum.com/forum-rule...rum-rules.html ) .

As for the merit - try in your main sheet cell F2 the formula

Formula:

Please Login or Register  to view this content.

which looks in column A in Web for mainA2 content and returns value from sixht column in Web (false means exact match in column A)

this 1000 was last row number with data in WEB sheet
But as you have excel 2013 you do not have to worry much about last row and can address whole columns so:

Formula:

Please Login or Register  to view this content.

would work equally well.

As a matter of fact you probably want to add some more SKUs in column A so let's improve this a bit to:

Formula:

Please Login or Register  to view this content.

Now you can copy this cell it down, even if there is no data in A3 and below

See attached sample with updated formulas. (not only F2, look into G2, Q2 and S2 - and think whichever is propper approach - roundup I used or just ROUND

just one blank.

let me write it other way:

Formula:

Please Login or Register  to view this content.

can you see it better now?
---------------------------------------------
Or let's take the previous formula and "disassemble it":

Formula:

Please Login or Register  to view this content.

formula IF always follow the pattern

Formula:

Please Login or Register  to view this content.

so we check whether

Formula:

Please Login or Register  to view this content.

if it is true the result of the whole formula will be

Formula:

Please Login or Register  to view this content.

if the result of checking will be false (so A2 is not empty) then result of the whole formula will be result of our desired formula:

Formula:

Please Login or Register  to view this content.

Glad to see it worked, for you. And thanks for marking thread as solved, as well as for reputation point.

Have a good day!