NEED HELP WITH EXCEL!!!

[Closed]
Report
-
Posts
6
Registration date
Friday August 20, 2010
Status
Member
Last seen
September 8, 2010
-
Hello,
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?

1 reply

Posts
6
Registration date
Friday August 20, 2010
Status
Member
Last seen
September 8, 2010

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
Posts
6
Registration date
Friday August 20, 2010
Status
Member
Last seen
September 8, 2010

Or how about this much much simpler way, put this in a blank cell in sheet B next to your existing information table.

=vlookup(choose the list of items in sheet A,select the list of items in sheet B,1,0)

press enter and then drag the cell with this formula in it down until the end of the information. It will look up only information that appears on both sets of data, thus showing you which items have disappeared between reports

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!