0

I'm not sure if this is very complicated or if there's a very simple solution to do this with Microsoft Excel.

I have a table 1 that lists Employees downward and Projects across

Employee | Project A | Project B |

It will have the employee name and a simple Yes if they worked on that project.

Then I have table 2 that lists Projects downward and Locations across, a simple yes is inputted if the project was based (or somewhat based) in that office

Project | Office A | Office B |

However I now need to make table 3 which will list Employees downward and Offices across

Employee | Office A | Office B

So I will now be able to pull a list of what offices each employee has been in as a combination of the two tables I already have.

I don't even know what this is called to try and google it most of the things I have found have just been how to merge to identical tables or how to vlookup information.

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
user3178348
  • 1

1 Answers1

0

Sounds like you may be better served by a database in this situation, but sticking with excel, the best thing to do would be to get all of the columns onto one table, and hide the columns you don't need.

Copy the emp/proj table into a new sheet. Add columns for office a & office b for each project column, and fill those with vlookups referencing the project columns, then add summary columns for each office, and hide the rest.

ex:

employee (From Table 1) | Project A (T1) | Office A (Vlookup from T2 using Project A as reference) | Office B (T2 vlookup) | Project B (T1) | Office A (T2 vlookup with Project B as ref) | Office B (T2 vlookup) | Total Office A (Sum of office A) | Total Office B (sum of office B)

Hope that helps. In the totals columns, if you just want a yes/no or yes/blank instead of a sum, you can use an IF statement with an ISBLANK or a ">0".

JMcD
  • 100
  • 7