Solved

Data consolidation

Posted on 2013-06-25
2
227 Views
Last Modified: 2013-06-25
Hi,

I have workbook attached and is named as ‘DataDump’. It has the following worksheets

1.      Sheet 1
2.      Sheet 2

In ‘Sheet1’ workbook we have the name of the companies listed in column A. We are trying to get the total points received for each company for each month by comparing the data which is there in ‘Sheet2’.

I have used an array to get the data and it is perfectly working. I know that we can also use pivot to get this numbers from ‘Sheet2’. The only problem which I am facing is that it is literally making excel slow while calculating. I also know that while working on excel we can make the calculations to ‘Manual’ which would make excel to work faster. Is there is way where on click of a button all the calculations should be done (basically a macro to do this) and display the results as shown in the example attached. Please advise.

Regards,
Prashanth
DataDump.xlsx
0
Comment
Question by:pg1533
2 Comments
 
LVL 2

Accepted Solution

by:
Agneau earned 500 total points
ID: 39274375
Hi Prashanth,

Instead of working with matrix formulas (very powerful, however poor perfomance depending on their construction), Excel now implements a function SUMIFS that can be used to sum a range according to more than one criteria.

Check the example attached

Regards
DataDump.xlsx
0
 

Author Closing Comment

by:pg1533
ID: 39274411
Thank you
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question