Lookup Dept of Employee from Another Sheet and Add that Total to the department total

[ja189704]

I have a two worksheets:

1. COSTS. It has a list of cost by employee ID (EQUITYHOLDERCODE).
2. EMP DATE. A list that has employees with their department number, which has leading zeroes.

On the costs tab I want to lookup the employee department and than add the CURRENTEXPENSE (Column K tab COSTS) to that department total.

For EQUITYHOLDERCODE that is alpha I want it to just give me a total for all alpha.

Then I want it to put the details by department on third tab (but the department in the list it the # and name, I just want the 9 digit #), Desired Result. And the total from the CURRENTEXPENSE total to the be offset at the end.

I've attached a google sheet link. If someone could tell me what steps I need to take. I can use VBA to take the left 9 of the department into a new column and then vlookup on the new data. I know that's my 1st grade knowledge of VBA. I suspect I need to use some sort of array.......

Any help to point me in the right direction is appreciated.

Thanks.

SBC Marco.xlsm

 

[JW00]

Hi, I don't know if these are any good to you, on the Desired Results sheet in A2 type "=SORT(UNIQUE(FILTER(COSTS!N:N,COSTS!N:N<>"")))", that will give you a sorted dynamic list of your employee departments & in B2 type "=IF(A1="ALPHA NOT NUMERIC",SUM($B$2:OFFSET($B$2,COUNT(ROW($A$2#))-1,0)),SUMIF(COSTS!N:N,A2,COSTS!K:K))" & copy it down, this will give you all your department totals with a grand total below.