0

I need a help please,

I have 2 tables: one has days an employee worked on the project/proposal in a month(Jan, Feb etc.), and the other has employee rates. I need to multiple matching rates and Jan days where all the criteria are same in both table, and then show the sums if Type: "Project" and Year "2018". I need to do this sum for each month separately, but If I find how to do it for one then rest should be simple :)[n/v] means no value.

Table 1

Year | Type | Project | Employee | Jan(days) | Feb(days)

2018 | Project | Apple | John N | 6 | 7

2018 | Project | Apple | Alex T | [n/v] | 8

2017 | Proposal | Banana | Tim C | 8 | [n/v]

2017 | Proposal | Banana | Sena I | 9 | 6

2018 | Project | Kiwi | John N | [n/v] | 6

2018 | Project | Kiwi | Yen T | 4 | 5

Table 2

Year | Type | Project | Employee | Rate

2018 | Project | Apple | John N | 30

2018 | Project | Apple | Alex T | 40

2017 | Proposal | Banana | Tim C | 20

2017 | Proposal | Banana | Sena I | 30

2018 | Project | Kiwi | John N | 10

2018 | Project | Kiwi | Yen T | 40

I can do this with SUMPRODUCT() if I have both rates and days in the same table;

The formula I used if both columns are in the same table is:

=IFERROR(SUMPRODUCT(((Table[Year]= D2)(Table[Type]="Project")(Table[Jan]>0))*(Table[Rate]*Table[Jan]) ),"")

but with 2 separate tables, it doesn't return the correct value.

Thanks in advance

Kev
  • 3
  • 4

1 Answers1

0

My first instinct would be to suggest writing an SQL query against the worksheets:

SELECT Table1.Year, Table1.Type, Table1.Project, Table1.Employee,
    SUM(Table1.[Jan(days)] * Table2.Rate) AS SumJan,
    SUM(Table1.[Feb(days)] * Table2.Rate) AS SumFeb,
    ...
FROM Table1
INNER JOIN Table2
    ON Table1.Year = Table2.Year
    AND Table1.Project = Table2.Project
    AND Table1.Employee = Table2.Employee
WHERE Table1.Year = 2018 AND Table1.Type = 'Project'
GROUP BY Table1.Year, Table1.Type, Table1.Project, Table1.Employee

However, you are on a Mac, so using ADO and CopyFromRecordset is out. Perhaps this is possible via ODBC and Microsoft Query.

Alternatively, you might load all the data from the second sheet into a Scripting.Dictionary (a drop-in replacement for Mac is available here). Then, you could write a function something like this:

Option Explicit

Dim dict As Dictionary

Sub FillDictionary()
    Set dict = New Dictionary

    ' load dictionary from table 2
    Dim rows As Variant
    rows = Table2.Range("A2").CurrentRegion.value
    Dim row As Variant, key As String
    For Each row In rows
        'assuming the first row has the column headers, we don't want to include those in the dictionary
        If row(0) <> "Year" Then
            key = Join(Array(row(0), row(1), row(2), row(3)), "_")
            dict(key) = row(4)
        End If
    Next
End Sub

Function GetSumRate(Year As Integer, RowType As String, Project As String, Employee As String, Days As Integer)
    If dict Is Nothing Then FillDictionary

    Dim key As String
    key = Join(Array(Year, RowType, Project, Employee), "_")
    If dict.Exists(key) Then GetSumRate = dict(key) * Days
End Function

Then, you could call the function from a worksheet cell:

=GetSumRate(Table[Year], Table[Type], Table[Project], Table[Employee], Table[Jan])
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • Thanks, Zev, from my limited programming knowledge this is going to limit the new values being added to the tables, as then each time for a new row in the table I have to create a new key and value. So for example if I want to add the new year 2019 or new employee X, I have to define them in the dictionary- is this the case? – Kev Apr 30 '18 at 00:28
  • @Kev You don't actually fill the dictionary by hand. That's part of the `FillDictionary` sub -- each time it is run it's supposed to recreate the dictionary values from `Table2` (I didn't write the implementation; I'll do so now). So if you add a new row to `Table2` and then rerun `FillDictionary`, the dictionary would have the information from the new row. – Zev Spitz Apr 30 '18 at 01:25
  • @Kev Updated with implementation of `FillDictionary`. – Zev Spitz Apr 30 '18 at 01:39