Need direction on automating a task where I manually used VLOOKUP, IF Statements, and BEDMAS

[targus3194]

Hi everyone,

At a high level, I have to summarize data into a summary. I am given an excel that has the hours an employee has worked as well as how much an employee can charge to clients per hour. This is then multiplied to get to a revenue amount (Est) that we would charge out.

The things is the time entered though can be categorized into different phases (I.e, Advice, Operations, Administration, etc). There could be more phases.

My job is to create a summary that shows how much revenue is sourced from each phase per employee.

Please see this link for demonstration purposes: https://docs.google.com/spreadsheets/d/1sR7Fvp3_8tUlMF1IqCr7K2InvWMbQYsFFwjzQzSgF5s/edit?usp=sharing

I am hoping to potentially automate the entire process because I have to do it every week. Usually there is data going back weeks and so there are more rows and employees. It could take anywhere from 45 mins to 1.5 hour.

Any guidance on what I want to do is greatly appreciated. I also want to improve my VBA too.

I have listed the questions in the excel too but here they are as well:

Question 1
Is there a way to have a dynamically updating array that will source from column D and list them in a row in H,I,J,K (5) because there could be a lot of phases (I.e, 5+). [SOURCE DATA TAB]Question 2
Assuming the above is possible, is there a code I can write in VBA that would automatically do the "User Made" section including the IF Statements based on the the Table B5:G23? [SOURCE DATA TAB]

Question 3
Is it possible to have only unique names in Column B dependent on what each table is for (I.e, Only John Doe should be in Advice Table because he is the only one providing advice and generates revenue from providing advice) and make it dynamic? [SUMMARY TAB]


Thank you!

 

[RobertSF]

Welcome to the forum! I think we need to see the data as it arrives to you. The Google Docs spreadsheet you linked seems to show the final result, and of course we need to see it, but we also need to see the starting point -- the data that you get and from which you create the summary.

 

[targus3194]

Hey! Yes, so the starting point would be in the second tab called source data. I usually perform work on the source data tab which I labelled as the “user-made” portion but majority of the work is in the first tab.

 

[RobertSF]

Well, what I meant was that the final result you're showing us in the Google Doc spreadsheet is not enough information. What does the raw data look like? What are the rows and columns that are given to you, and from which you must create the summary you showed us? It might be possible to do what you want just with a pivot table. Have you tried that?