VBA Needed to populate employee data from data dump

[aach2034]

Hello,
I have a large amount of data that I receive with timesheets each
week. I am currently using many, many vlookups and pivots to achieve
the results that I need. My goal is to get the resource data by hours and spend, by month and project. Please take a look and let me knowif you can help.

[CharlesH]

aach2034,


Can you provided a more detailed example as too what you want too see?
A better example would help.

[Aussiebear]

Where did the variance figure get calculated from?

[aach2034]

I haven't figured out the percent of change based upon the prior period. The most important thing for me is to populate the fields in my sample file from the raw data. My job depends om it.

[CharlesH]

aach2034,

In the "Desired" sheet can you provide an example as to what you expect too see? If you fill in the blanks on the sheet we may be able too help you better.

[Aussiebear]

My first impression was that we might have been able to sort the data to fit within the 3 buckets IT CORE PLAN - Service Desk Requests, KTLO-C 2011 KTLO Application Service Desk Requests, & KTLO-C Dotcom Linux Migration but it seems there are at least 22 unique descriptions with the Project Investment column of data.

Is the raw data as shown in the sample workbook how you receive this data? And, more importantly, is there a way to define more clearly which expense bucket these descriptions can be applied to?

[shrivallabha]

Step 1: Use 'Advanced Filter' to find Uniques in "Resource Name" and copy them to Column A of desired results.

Step 2: Then in Cell D4 insert following formula:

=SUMPRODUCT(--(rawdata!$A$2:$A$1089='desired result'!$A4),--(rawdata!$K$2:$K$1089='desired result'!B$2),rawdata!$H$2:$H$1089)
And then copy it where you need monthly summary, the formula will adjust accordingly.
The same can be done for 'Total $'

Take a look at Sub-Forum SUMPRODUCT and other ARRAY functions. I bet, you will not need any explanations once you read XLD's explanation on SUMPRODUCT.

If you still require VBA then it can be worked out.

[Aussiebear]

@shrivallabha How does this predetermine the actual bucket list the expense is applied to?

[shrivallabha]

I missed that one (and thanks for pointing that out) but then the formula can be extended as the buckets are in Column D.

=SUMPRODUCT(--(rawdata!$A$2:$A$1089='desired result'!$A4),--(rawdata!$K$2:$K$1089='desired result'!B$2),--(rawdata!$D$2:$D$1089=TRIM('desired result'!$G$18)),rawdata!$H$2:$H$1089)

[Aussiebear]

Which gets back to my initial point. There are 22 unique entries within Column D. How do we associate these 22 entries with the three expense buckets?

[shrivallabha]

Which gets back to my initial point. There are 22 unique entries within Column D. How do we associate these 22 entries with the three expense buckets?
You are right. Its @ time OP "explained" these things?