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

x
?
Solved

Copy Data from one worksheet to another worksheet

Posted on 2014-04-17
4
Medium Priority
?
279 Views
Last Modified: 2014-04-18
Attached is a workbook that needs the SUN to SAT data from the ActiveDays Worksheet copied to the appropriate cells within the ID worksheet.

In the ActiveDays worksheet, there is one row of SUN to SAT data, for each major category, ID_Parent, within the ID worksheet.  In the ID worksheet, there is the ID_Parent column (Col A), with several other minor categories, or children (Col B), of the ID_Parent.

What is needed is a way, probably VBA, to copy the SUN to SAT data into each column, from the ActiveDays worksheet into the same fields of the ID worksheet, based on the Col A (ID_Parent) criteria.  Rows 2 and 3 of the ID worksheet provide an example.

Therefore, if a cell in column A, within the ID worksheet is ABCD, then the code would look for ABCD in the Activedays worksheet, and copy the values from columns B to H into columns D to J of the ID worksheet.
Combine-Tables.xlsx
0
Comment
Question by:Cook09
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40008124
I believe you can achive that result using SUMPRODUCT. In cell D2 the formula would be:

=SUMPRODUCT((ActiveDays!B:B),--(ActiveDays!$A:$A=$A2))

See attached.

Flyster
Combine-Tables.xlsx
0
 

Author Comment

by:Cook09
ID: 40008904
Flyster,

Yes it seems to work, but takes a very long time to update the calculations.  I had to put it to Manual.  Is there another method that is not so processor intensive?  But, I do like the formula aspect.

Cook09
0
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 40009022
Here it is using SUMIF. D2 formula is:

=SUMIF(ActiveDays!$A:$A,$A2,ActiveDays!B:B)
Combine-Tables-Sumif.xlsx
0
 

Author Closing Comment

by:Cook09
ID: 40009461
Exactly what I needed.  Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

876 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