0 Replies Latest reply on Jan 11, 2013 9:22 PM by Michael Lance

    Display and calculate with the total of a set in a single row

    Michael Lance

      Anyone want a fun project for the weekend?

       

      I have the attached spreadsheet.

      It has 2 tables and a desired output table.

       

      Table 1 has the following fields of interest:

      last, first

      check date

      division

      department

      amount

       

      Table 2 has the following fields of interest:

      last, first

      division

      department

      FTE

       

      I need the following table and I think I need to do a right join between the two, where table 2 is the right table (of course):

       

      last, first (from table 2)

      check date (from table 1, since I need all dates displayed in rows and each individual will have multiple check dates)

      Note: the next 3 fields seem problematic, in theory, because I want to display records from table 2 (assuming a right join) next to the check date column, which is not a field from table 2.

      division (table 2)

      department (table 2)

      FTE (table 2)

      Note: another challenge: for every row (division and department): I need to multiply the FTE (table 2) by sum of the amount (table 1) per last, first (table 1) and check date (also table 1).

       

      The final table should have columns with headers that look like this:

       

      Table A

      LAST, FIRST (from table 2)CHECK DATE (from table 1)DIVISION (table 2)DEPARTMENT (table 2)FTE (table 2)NEW AMOUNT (table 1 x table 2 calculation from above)

       

       

      Extra Credit:

      I then need another table with the following:

      Note: the first 4 fields are from table 1:

       

      Table B

      LAST, FIRSTCHECK DATEDIVISIONDEPARTMENTAMOUNTADJUSTED AMOUNT

      Note: "ADJUSTED AMOUNT" tries to find a matching value from the resulting Table A to this one (Table B) based on exact matches between the first 4 fields in each (LAST, FIRST through DEPARTMENT). If it does, it subtracts what it finds in Table B from "AMOUNT." If it does not find a match, it subtracts "AMOUNT" from 0 to get a negative value (unless there is a better way to show the negative value).