• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a Template out of Multiple Combinations of 4 fields distributed across 3 tables

hello Friends and Ninja Friends,

Writing after a long on Chandoo Forum with a lot of hope. Especially after Chandoo moved on and has also taken to a lot of HR Data issues.....!!

I have in one table about 80 Work locations named W1, W2, W3......so on.
Against each location I have a set of Makers M1, M2, M3.
Most makers are responsible for a single or two locations BUT, there are some who are responsible for multiple locations ranging from 3 to 8.

Two more tables consist of Department & Headcount Type.
There are about 12 Departments and about 6 Headcount types.

For each location there could be Any of the 12 Departments that could be ACTIVE ( i.e. containing some people )
and for each of these Active departments there are certain TYPE of People that are working in those departments.
If we are to look at total combinations then it would be 80 locations x 12 Departments x 6 Headcount types = 5760 Combination rows.

The challenge for me is to prepare a template based on last data of headcount the Makers may have entered.
I have given 2 examples of Templates that we need to automatically create for all Makers, assuming that such data was already entered by those Makers and that we have such data for ALL Makers.
Real challenge is to be able to create such templates automatically WITHOUT creating a MASTER of 5760 Rows.

Attaching Excel file for ease of understanding..... ANY Takers ??
Have tried to simplify this problem, its a problem of crunching the Exponential into an integrated Result / formula based.
Eventually, we want to adapt the solution into an application we are trying to create for Data collection of Headcount from different Sites / Work locations....hence I am here !

I am very hopeful ! :) :)