I have two sheets with 5 columns each; item number, description, quantity on-hand, price & total value.
I want to match the columns on sheet B (old report) with the columns on sheet A (new report) and whatever does not match leave on Sheet B; I need to see what items are no longer in inventory since the old report. Can someone help me?
There are many ways of doing this, the simplest i think is the look and see method, and this can be made easier by adding a third sheet which will include two pivot tables.
I am assuming a basic level of excel knowledge so I'll try not to boggle your mind.
In the "data" drop down menu you will find a "pivot-table and pivot-chart report..." option. Select that, click next and then select the entire range of information on sheet B. Then click finish. Once finished excel will present you with an empty table and a window containing all of the headings of the columns in the table on sheet b (known as "fields"). By clicking and dragging these fields onto the axes of the table you can fill the table with the information from sheet b.
I would recommend this; Item Number onto the left hand side (vertical axis) and "quantity on hand" into the large central area (creating a table that shows you the number of "quantity on hand" listed by "item number". The other fields can be left out of the pivot table for now.
You may notice that the top left cell of the table now say something like "count of Quantity on hand", you can change this by right clicking on it and selecting one of the last option called "field settings" and then choosing the "sum of" - although i should add that your post didn't mention needing to know the difference in quantity of each item, i just thought "sum" would be more useful than a "count".
Repeat this process for the info on sheet A into the same sheet as before, making sure to place each table side by side and maybe sort the "item number" axis for added effect.
You are now ready to compare reports side by side visually and what's more, the pivot tables can be "refreshed" with a right click option so you can change the information in each of the original sheets and compare that new information.
I'm sure there are at least 5 other ways of doing this same job but i have put forth mine. I chose this idea for it's practical future-proof approach and also because it seems like the one with the most educational value