Solved

# Consolidate the data

Posted on 2013-05-25
264 Views
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
Question by:pg1533
• 5
• 2
• 2

LVL 14

Expert Comment

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)
``````
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

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

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

LVL 39

Accepted Solution

als315 earned 500 total points
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

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 39

Expert Comment

I prefer for such cases to use Pivot tables.
Sample.xls
0

Author Closing Comment

thank you
0

LVL 14

Expert Comment

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

No Problem. Please do not be sorry. I understand. Thank you for your time and support.
Take care.

Regards,
Prashanth
0

## Join & Write a Comment Already a member? Login.

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!