How to merge data from two excel sheets into one?
October 9, 2009 3:38 PM Subscribe
How to merge data from two excel sheets into one?
Here's the scenario. I have two excel sheets. Column A on both sheets contain item numbers to the products that my business carry. Column B on sheet 1 contains items with product description that are incorrect but sheet 1 is also a complete data sheet with all other information on pricings, vendors, etc. Column B on sheet 2 contains the correct production descriptions but doesn't have the rest of the information (pricings, vendors, etc.).
My question is, is there anyway that I can merge the Column B (product description) of the two sheets together based on column A (item numbers)? I want sheet 1 to now have the correct product descriptions that are contained in sheet 2. Hope this makes sense.
Here's the scenario. I have two excel sheets. Column A on both sheets contain item numbers to the products that my business carry. Column B on sheet 1 contains items with product description that are incorrect but sheet 1 is also a complete data sheet with all other information on pricings, vendors, etc. Column B on sheet 2 contains the correct production descriptions but doesn't have the rest of the information (pricings, vendors, etc.).
My question is, is there anyway that I can merge the Column B (product description) of the two sheets together based on column A (item numbers)? I want sheet 1 to now have the correct product descriptions that are contained in sheet 2. Hope this makes sense.
You can use the VLOOKUP function to do this. Steps for Excel 97 or so:
1) Get sheet 1 and sheet 2 in the same workbook. You can just drag the tab at the bottom from one to the other. I'm not sure if this is needed but it causes less pain later because Excel will freak if one of the files goes missing later on.
2) Sort Sheet 2 by the item number.
3) On Sheet 1, where you want the description, type in the formula:
=VLOOKUP(item number cell on sheet 1, range containing everythign on sheet 2, 2, false).
The last false is very important. Otherwise you will get junky data for any item number that is not on Sheet 2.
posted by smackfu at 3:49 PM on October 9, 2009
1) Get sheet 1 and sheet 2 in the same workbook. You can just drag the tab at the bottom from one to the other. I'm not sure if this is needed but it causes less pain later because Excel will freak if one of the files goes missing later on.
2) Sort Sheet 2 by the item number.
3) On Sheet 1, where you want the description, type in the formula:
=VLOOKUP(item number cell on sheet 1, range containing everythign on sheet 2, 2, false).
The last false is very important. Otherwise you will get junky data for any item number that is not on Sheet 2.
posted by smackfu at 3:49 PM on October 9, 2009
Agreeing with randomstriker, VLOOKUP will do exactly what you want.
posted by kosmonaut at 3:52 PM on October 9, 2009
posted by kosmonaut at 3:52 PM on October 9, 2009
Response by poster: One more question. Is there a keyboard shortcut so when I edit a cell, I can start typing and it will start off at the end of whatever is in the cell instead of erasing the content inside?
posted by willy_dilly at 4:51 PM on October 9, 2009
posted by willy_dilly at 4:51 PM on October 9, 2009
Like jeb says, F2, which is a fairly universal shortcut in Windows for "edit" or "rename". E.g. if you have a file selected in Explorer, hit F2 to rename the file.
posted by randomstriker at 11:25 PM on October 9, 2009
posted by randomstriker at 11:25 PM on October 9, 2009
This thread is closed to new comments.
posted by randomstriker at 3:47 PM on October 9, 2009