• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Consolidate the data

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
pg1533
Asked:
pg1533
  • 5
  • 2
  • 2
1 Solution
 
FaustulusCommented:
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
 
pg1533Author Commented:
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
 
pg1533Author Commented:
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
Industry Leaders: 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!

 
als315Commented:
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
 
pg1533Author Commented:
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
 
als315Commented:
I prefer for such cases to use Pivot tables.
Sample.xls
0
 
pg1533Author Commented:
thank you
0
 
FaustulusCommented:
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
 
pg1533Author Commented:
No Problem. Please do not be sorry. I understand. Thank you for your time and support.
Take care.

Regards,
Prashanth
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now