Hi
I cannot post any attachments so I cannot share the sample data I made. I copied it below. The formatting is off. Hopefully you can still understand the sample.
I have a job matrix that shows staff movement. The table is read as follows. There were 22 people from job B that moved into job A. There were 3 people in job C that moved into job A. The percentage is based on the to job. I need to know the top three "FROM JOBS" that moved into each "TO JOB". This is shown in the expected outcome. In addition to the job, I also need to know the corresponding %.
Any ideas on how to do this?
Thanks in advance!
<tbody>
</tbody>
I cannot post any attachments so I cannot share the sample data I made. I copied it below. The formatting is off. Hopefully you can still understand the sample.
I have a job matrix that shows staff movement. The table is read as follows. There were 22 people from job B that moved into job A. There were 3 people in job C that moved into job A. The percentage is based on the to job. I need to know the top three "FROM JOBS" that moved into each "TO JOB". This is shown in the expected outcome. In addition to the job, I also need to know the corresponding %.
Any ideas on how to do this?
Thanks in advance!
SOURCE DATA | ||||||||||||||||
STAFF MOVEMENT COUNTS | STAFF MOVEMENT PERCENTAGE OF "TO JOB" | |||||||||||||||
FROM JOB | FROM JOB | |||||||||||||||
A | B | C | D | E | TOTAL | A | B | C | D | E | TOTAL | |||||
TO JOB | A | 1 | 22 | 3 | 0 | 19 | 45 | TO JOB | A | 2% | 49% | 7% | 0% | 42% | 100% | |
B | 0 | 0 | 7 | 9 | 3 | 19 | B | 0% | 0% | 37% | 47% | 16% | 100% | |||
C | 3 | 3 | 2 | 0 | 7 | 15 | C | 20% | 20% | 13% | 0% | 47% | 100% | |||
D | 2 | 1 | 1 | 7 | 35 | 46 | D | 4% | 2% | 2% | 15% | 76% | 100% | |||
E | 1 | 2 | 0 | 0 | 0 | 3 | E | 33% | 67% | 0% | 0% | 0% | 100% | |||
EXPECTED OUTCOME | ||||||||||||||||
TOP THREE IN TERMS OF JOB | TOP THREE IN TERMS OF % | |||||||||||||||
TOP 3 FROM JOB | TOP 3 FROM JOB | |||||||||||||||
TO JOB | A | B | E | C | TO JOB | A | 49% | 42% | 7% | |||||||
B | D | C | E | B | 47% | 37% | 16% | |||||||||
C | E | A | B | C | 47% | 20% | 20% | |||||||||
D | E | D | A | D | 76% | 15% | 4% | |||||||||
E | B | A | E | 67% | 33% |
<tbody>
</tbody>
Last edited: