Readers like you help support MUO. When you make a purchase using links on our site, we may earn an affiliate commission. Read More.

I have two Excel spreadsheets. One has 20 named items with a device code in two columns. The second has hundreds of items entered the same way with other info like prices and etc.

I want to move all the items from spreadsheet 2 to spreadsheet 1 and have all the items match up (for prices and so on) and have the items not included in spreadsheet one disappeared or be grouped for easy deletion.

Any way to do this?

Scott Kupferman
2013-06-07 00:37:41
MAKEUSEOF VIDEO OF THE DAY
SCROLL TO CONTINUE WITH CONTENT
You could pull both into Access, run a query to find unmatched items but as a maketable query. You would then have a result set in the form of a table that you could then export back to Excel.
Alan Wade
2013-05-12 06:24:48
There are some good VLOOKUP examples here that should give you an idea on how to adapt to what you want to achieve:http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
Oron Joffe
2013-05-10 20:57:18
One way would be to add a column (let's call it column Z) to the second spreadsheet, and put into it a lookup function (e.g. VLOOKUP() ) which would look for the code in the first spreadsheet. If it finds it, it should display the code, and if not, it should display nothing (or a special code, such as "X" or "not found"). Once that is set up, you can sort on column Z and the unmatched codes will be grouped together, ready for deleting.
Bud Ingersoll
2013-05-12 04:06:28
Damn! I just developed a lengthy response explaining the use of nested IFs and ANDs, before I read the full explanation of the problem. But more embarrassing was Joffe's solution.. Much cleaner than mine. Good job.