Index() Match() V/HLookup??? CHUGE Challenge! Up for it!? Need HELP!

I will do my best to explain – for me, it helps to know the what behind the why – so I apologize of this is too much, I have created a monthly KPI scorecard, that our Agents are scored on based on how many cases they resolve/close within a certain amount of time.

Example

Grand Total = Total Cases opened (count all cases that have an Age of 0 to 38 days)
Resolved Same Day (RSD) Age = 0
Resolved First Call(RFC) Age = 0
Resolved within 4 days or less age = < 4
Resolved within 8 days or less age = < 8

I also need to lookup how many cases were statues closed as "Resolved First Call" IF age = 0.. I know is pretty complicated, and I don't have backend access to the Database so I can’t do this in the SQL Code; so this is how I get it.. is it even possible??? - i am thinking this will require a complex VBA code..



I need to for each Tech Support agent, to count the amount of cases resolved/closed in <1, <4, <8; this is not ‘equal or less than X’ this is simply less than, so <8 = cases closed 0 to 7 days.
Seeing how the data is pulled, how I can get this? I’ve tried Pivot tables; Vlookup, Hlookup, Index, and match.
I need to be able to dump this data in, and do some type of lookup that pulls the data into their scorecard.

Looking at the attached file SFI data tab is the raw table. The SocreCard Tab has the format. I need to pull in the above criteria into their respective columns. The data pull is stupid, "Garbage in, Garbage out"

If anyone has any ideas, that would make this a bit easier than manually typing this in, it would be awesome..



I need to for each Tech Support agent, to count the amount of cases resolved/closed in <1, <4, <8; this is not ‘equal or less than X’ this is simply less than, so <8 = cases closed 0 to 7 days.
Seeing how the data is pulled, how I can get this? I’ve tried Pivot tables; Vlookup, Hlookup, Index, and match.
I need to be able to dump this data in, and do some type of lookup that pulls the data into their scorecard.

Looking at the attached file SFI data tab is the raw table. The SocreCard Tab has the format. I need to pull in the above critera into their respective colums. The data pull is stupid, "Garbage in, Garbage out"

If anyone has any ideas, that would make this a bit easier than manullay typing this in, it would be awesome.


Note*... On the SFI Data Tab, the ONLY columns I care about are the "Created by","Status","Age (Days)"

Here, i modified the file a bit. I added a column that added a new status field. Assuming all the other fields come from some system, you can just take the system extract and paste it into the table in the blue header columns. the green header column is the formula. Then just click refresh on your pivot table. I just used abreviations for the different categories and i left Duplicate as a category because it is neither resolved nor open still.

SEICaseTemp(1).xlsx

DanRiver,

Thanks for looking at this, and taking time out of your day, I did something similar, but didn't quite get me where I need to be, I appreciate the help!

The VBA works nicley, and gets me where I need!!

Oh yeah, I can see using this with many things I have to deal with! Thanks again, sir.