I need to extract matching data from excel files?

Question

I have on excel file like this :

SKU PRICE QUANTITY

1........25...........4

2.........41..........9

etc......etc........etc

The second one looks like this :

SKU UPC

0...786876

1...44777

etc...etc


While the first file contains about 300 active products the second one has a list of about 2000 active and deactivated products. What I need is to extract the UPC from the second file matching the active SKU from the first file so I have a final file that looks like this:

SKU UPC PRICE QUANTITY

1..44777...25.........4

and so on. I am new to using excel , I did some research online but couldn't make it by my self so if someone could give me a step by step guide it will be much appreciated. Thank you.

Answer 1

Use the Vlookup function.



So ... in the first file of 300 products, based on your 3 columns above, assume column A has the SKU and column B the price, Column C the quantity and Column D is where you want to bring in the UPC.



Assume in the second worksheet (which I'll call Sheet 2) your SKU is in Column A and your UPC is in column B, then the formula you would enter in cell D1 of the first worksheet would be:



=VLOOKUP(A1,Sheet2!$A$1:$B$2000,2,FALSE)



NOTE there should be a closed bracket appearing immediately after the word FALSE above and nothing else ... Yahoo Answers is chopping off the end of the formula for some reason.



Look at the tab at the bottom of the 2000 product worksheet to see its name, and replace 'Sheet2' with that tab name. If it's in a completely separate file, you'll need to put the file name in front of the tab name, eg if the file name is called FullProductList.xlsx and the tab name is Sheet2, then your formula would be:



=VLOOKUP(A1,'[FullProductList.xlsx] Sheet2'! $A$1:$B$2000,2,FALSE) ... without any of the spaces.



You can then copy the formula down to all the 300 cells below it.



Here's a video which also explains the Vlookup function if it helps: http://www.youtube.com/watch?v=g2YlBT7fiuk

Answer 2

First a question: Are these truly separate files? Or are they just different sheets in the same file?



You can certainly do this with separate files (and I'll show you how down below), but then you get into all sort of problems when you start moving files around to different directories, or renaming them. It's far cleaner to manage these different sets of data as different sheets in a single file. But in case your situation doesn't allow this, here you go:



Start by getting all 3 files loaded up simultaneously in Excel. To see them all, under "View" click "Arrange All", and choose "Tiled".



A1 to A4 of your 3rd file should contain the headers that you showed above in your question:



SKU..... UPC....PRICE....QUANTITY



A2 should contain the following, which just references the first active SKU in your first file. (Change "skuprice.xlsx" to whatever the name of your first file is):



=[skuprice.xlsx]Sheet1!A2



B2 should contain the following (changing skuupc.xlsx to your 2nd file's filename):



=VLOOKUP( A2, [skuupc.xlsx]Sheet1!$A$2:$B$3, 2, FALSE )



C2 and D2 will contain the following (again, change the filename to your first file):



=[skuprice.xlsx]Sheet1!B2



=[skuprice.xlsx]Sheet1!C2



Row 2 in your new 3rd file should now look exactly like the example in your question. All that's left is to replicate these formulas downward for however many SKUs you have. Because the VLOOKUP() formula used absolute and not relative referencing, all the replicated rows will continue to point to the same table. Of course if you've named your tables ranges, you can use those names instead to make the formulas simpler and less error-prone.



Again, if you have the option to merge these 3 files as different sheets in a single file, then your life will be much easier down the road.



@Savoir-Faire: The reason your formula's getting chopped off by Y! is you have no spaces in it. Y! shortens anything that's too long without spaces. My formula got shown correctly because I spaced it out.



Cheers;



Wire

Answer 3

A simple resolution could be to add a denotation in a brand new column for the participants listed in record 2 then, use a VLOOKUP perform in a new column in record 1. Example: List2 A B 1Joe Member 2Sally Member 3Juan Member List1 A B 1Joe =VLOOKUP(A1,LIST1!A:B,2,False) 2Sally =VLOOKUP(A2,LIST1!A:B,2,False) 3Juan =VLOOKUP(A3,LIST1!A:B,2,False) The VLOOKUP formula is telling EXCEL to seem for a fit of the value in cell A1, A2 and A3 (in this illustration) within the LIST1 Columns A and B. If it finds a suit to return the value from LIST2 to LIST1 that's 2 cells to the right. **EXCEL counts the cell it looking to as the primary mobile** If a suit is not determined, the error #N/A shall be lower back. The implies that character will not be listed in LIST1, as a consequence not a member. Don't worry about having to rewrite the components sometimes. Do it once then click on and drag it down the column and the supply cells will exchange for you.