NEED HELP WITH EXCEL!!!
Closed
LIZ
-
Aug 20, 2010 at 11:31 AM
JayHerring Posts 6 Registration date Friday August 20, 2010 Status Member Last seen September 8, 2010 - Aug 20, 2010 at 03:49 PM
JayHerring Posts 6 Registration date Friday August 20, 2010 Status Member Last seen September 8, 2010 - Aug 20, 2010 at 03:49 PM
Related:
- NEED HELP WITH EXCEL!!!
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
1 response
JayHerring
Posts
6
Registration date
Friday August 20, 2010
Status
Member
Last seen
September 8, 2010
Aug 20, 2010 at 03:10 PM
Aug 20, 2010 at 03:10 PM
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
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
Aug 20, 2010 at 03:49 PM
=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