Solved

Data consolidation

Posted on 2013-06-25
2
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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