Solved

Consolidate the data

Posted on 2013-05-25
9
296 Views
Last Modified: 2013-05-27
Hi,

I have attached a workbook named as ‘Sample’. It has two worksheets in it. They are

1.      Calculations
2.      Dump

In ‘Calculations’ worksheet, it has “Name’ column and there are two columns for each month starting from Aug ’13 to Mar ’14. In ‘Dump’ worksheet there are three columns ‘Date’, ‘Name’ and ‘Count’ and has some data is filled in each column.

With reference to the ‘Name’ columns in both the worksheet the sum of the count should be pulled up in ‘Aug ‘13’ column in ‘Calculations’ worksheet. Likewise it should repeat for all the clients and months. I have given few examples filled in for your reference.

I can use a vlookup to fetch the data from the other sheet. However, a macro  / formula (other than vlookup) doing this would really help me. Once I paste the data in ‘Dump’ worksheet and click on a button then values should reflect in respective cell. Please do the needful.

Thank you,
Prashanth
Sample.xls
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
  • 5
  • 2
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39197031
Would something like this work for you?
=SUMPRODUCT(--(Dump!$B$2:$B$64536=$A2),--(MONTH(Dump!$A$2:$A$64536)=MONTH(B$2)),Dump!$C$2:$C$64536)

Open in new window

You can paste the formula in cell B2 of your Calculations worksheet and copy it from there to other rows and columns.
0
 

Author Comment

by:pg1533
ID: 39197307
Hi,

I tried using this formula. However, I got a 'circular reference' warning and the result is showing as '0'. Please advice. Thank you for your time and support on this.

Regards,
Prashanth
0
 

Author Comment

by:pg1533
ID: 39197325
Also, it should consider the year also. If there are May 13 and May 14 then this formula will not help since it is pulling data based on month and not on year. The data should be pulled based on month and year. Please advise
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 39197485
Try this sample. Column's headers were changed to dates for Aug, Sep, Oct.
You should use array formula:
Press CTRL+SHIFT+ENTER (or COMMAND+RETURN on the Mac) to enter the formula as an array formula
Sample.xls
0
 

Author Comment

by:pg1533
ID: 39197742
Hi als315,

The formula is perfectly working fine. If there is a huge data then it is taking time to calculate. Is there a way to mitigate the time it is taking to calculate or can we use a macro to do this? Please do the needful. Thank you so much for your support and time.

Regards,
Prashanth
0
 
LVL 40

Expert Comment

by:als315
ID: 39197896
I prefer for such cases to use Pivot tables.
Sample.xls
0
 

Author Closing Comment

by:pg1533
ID: 39198480
thank you
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39199873
Hello pg1533,
Sorry I didn't get back to you quickly enough. Just didn't have the time to log in. I'm glad you got a solution to your problem.
0
 

Author Comment

by:pg1533
ID: 39199928
No Problem. Please do not be sorry. I understand. Thank you for your time and support.
Take care.

Regards,
Prashanth
0

Featured Post

Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

737 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