Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Data consolidation

Posted on 2013-06-25
2
Medium Priority
?
240 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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

971 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