Macro to Find, Sum, and Place Values

Fairly simple one here that I can't seem to figure out, I've attached an example workbook.

I just need a Macro (a formula isn't appropriate in this case) that will find the respective assets hours values on the Data worksheet and then sum them into the table on the Table Worksheet.

The correct sums are on the Table Worksheet to cross-check/verify the solution. Thanks in advance.
FindPlaceMacro.xlsx
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
i dont think you need to overkill it with VBA

just put this formula in H3 of Table Sheet and then Drag down and you get your result.

=SUMIFS(Data!C:C,Data!B:B,Table!G3)
-PolakAuthor Commented:
Hi JimJam... I know this can be accomplished easily with formulas; however, in my real workbook I need separation between several summation methods. Basically and end-user needs to choose their summation method and then "execute" the transfer of values from one place to another.

In my head there are two ways to do this; one is to have the macro place a specific SUMIFS formula in the table columns, or to just copy and paste over the values from the data source.
ProfessorJimJamMicrosoft Excel ExpertCommented:
if you really want to do it with VBA then here you go

Sub test()

    Range("A2").Select
    ActiveCell.Offset(1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(Data!C2,RC[-1],Data!C3)"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

-PolakAuthor Commented:
Works pretty well, couple things:
Can you explain
ActiveCell.FormulaR1C1 = "=SUMIF(Data!C2,RC[-1],Data!C3)"

Open in new window

That's the part that was preventing me from doing this on my own

Is there anything we can do about the "hanging rows" in the second asset group?
If this is running on a large data set, will this be the most efficient code to reduce Calculation Time?
ProfessorJimJamMicrosoft Excel ExpertCommented:
so couple of things to remember and also to make this better and improve.

A) i am glad that you are using the Table feature of excel in the table sheet. that is why when the macro puts the formula in first cell, automatically it populates to the last cell.  therefore no matter how big your table is, the formula will just work fine.

B) SUMIF is much better in terms of Speed calculation. of course there are other ways to calculate this to for example SUMPRODUCT and other SUM with IF etc, but SUMIF is much faster than the others, besides I used the whole column reference from the Sheet "Data" if you want to make this faster then i would suggest you use create a Table from your Data sheet, lets assume it is called Table1 then instead of the  ActiveCell.FormulaR1C1 = "=SUMIF(Data!C2,RC[-1],Data!C3)"  you could use ActiveCell.FormulaR1C1 = =SUMIF(Table1[Asset],[@[Asset Group 1]],Table1[Hours])"  which would be much faster in calculation becuase it will not take the whole column but only the data in the table.
-PolakAuthor Commented:
Cool, all good to know for the production workbook, I'll post here if I have a follow up question. Thank You Again!
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are most welcome. i am glad i was able to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.