Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
6 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Consolidate the data
Posted on 2013-05-25
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
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
[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
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)
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
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
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
Active today
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
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
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
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
Active today
I prefer for such cases to use Pivot tables.
Sample.xls
Sample.xls
Featured Post
Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month6 days, 6 hours left to enroll
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.