• 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

    Hui...

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

Add list of employees to repeating list of projects

D

DashboardNovice

Member
In the attached file, I have 25 project codes on the 1st tab. On the Employee tab, I have a list of 5 employees.

I want to assign each of these employees to all 25 project codes. The green tab shows what the final result would look like which has a total of 125 rows.

I am trying to determine a formula (or maybe some feature like PowerQuery) that can help me to create these assignments. As of now, I have to copy the list of project codes n number of times, where n = number of employees I have. In reality I have more than 5. I'm trying to avoid all the manual copying.

Is there a formula that would work for this?

As far as PowerQuery goes, I am thinking that maybe creating table joins on the first two tabs might be a step in the right direction.
 

Attachments

  • Chandoo.org Project Assignments.xlsx
    11.3 KB · Views: 8
Simple power query attached.

1. Load both tables (projects and employees), employees as connection only
2. Add extra column in projects
3. formula refers to tEmployees (name of the query for employees)
4. expand this table -> cross join made
5. load as table
 

Attachments

  • Copy of Chandoo.org Project Assignments.xlsx
    23.7 KB · Views: 4
Using formula and hope this helps
 

Attachments

  • Chandoo.org Project Assignments.xlsx
    14.1 KB · Views: 5
Follow up request here, wondering if anyone knows how to create a list based on cell value (formula preferred over VBA). For example, in attached, looking to create a dynamic sign-in sheet for recurring events at a venue...so security staff can have a quick printout sheet of who's expecting to come to initial in. Attached file displays a sample wish with just the job headings and qty's, formula creates a list based on that, and then we fill in the names and other specifics manually.
 

Attachments

  • Request for Help - Dynamic Employee SignIn Sheet.xlsx
    10.8 KB · Views: 1
5665 said:
Follow up request here, wondering if anyone knows how to create a list based on cell value (formula preferred over VBA). For example, in attached, looking to create a dynamic sign-in sheet for recurring events at a venue...so security staff can have a quick printout sheet of who's expecting to come to initial in. Attached file displays a sample wish with just the job headings and qty's, formula creates a list based on that, and then we fill in the names and other specifics manually.
Click to expand...
Don't hijack the other person's post !

Please open a new post for your own question.

Regards
Bosco
 
You must log in or register to reply here.
Back
Top