return top 3 entries

[blue333]

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!

[TABLE="width: 1088"]
<tbody>[TR]
[TD="colspan: 17, align: center"]SOURCE DATA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]STAFF MOVEMENT COUNTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]STAFF MOVEMENT PERCENTAGE OF "TO JOB"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]FROM JOB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]FROM JOB
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]TO JOB
[/TD]
[TD]A
[/TD]
[TD]1[/TD]
[TD]22[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]19[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD]TO JOB
[/TD]
[TD]A
[/TD]
[TD]2%
[/TD]
[TD]49%[/TD]
[TD]7%[/TD]
[TD]0%[/TD]
[TD]42%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]B
[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]37%[/TD]
[TD]47%[/TD]
[TD]16%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD]13%[/TD]
[TD]0%[/TD]
[TD]47%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]35[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]4%[/TD]
[TD]2%[/TD]
[TD]2%[/TD]
[TD]15%[/TD]
[TD]76%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]33%[/TD]
[TD]67%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 17, align: center"]EXPECTED OUTCOME[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]TOP THREE IN TERMS OF JOB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]TOP THREE IN TERMS OF %[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]TOP 3 FROM JOB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]TOP 3 FROM JOB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TO JOB
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TO JOB
[/TD]
[TD]A[/TD]
[TD]49%[/TD]
[TD]42%[/TD]
[TD]7%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B
[/TD]
[TD]47%[/TD]
[TD]37%[/TD]
[TD]16%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]E[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]47%[/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]76%[/TD]
[TD]15%[/TD]
[TD]4%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]67%[/TD]
[TD]33%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

I think this is close to what you require. The intervening table RANK ORDER is necessary because I couldn't figure out how to create the necessary array.

Copy the formulas down and across as necessary.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	STAFF MOVEMENT COUNTS								STAFF MOVEMENT PERCENTAGE OF "TO JOB"
2		FROM JOB								FROM JOB
3	TO JOB	A	B	C	D	E	TOTAL		TO JOB	A	B	C	D	E	TOTAL
4	A	1	22	3	0	19	45		A	2%	49%	7%	0%	42%	100%
5	B	0	0	7	9	3	19		B	0%	0%	37%	47%	16%	100%
6	C	3	3	2	0	7	15		C	20%	20%	13%	0%	47%	100%
7	D	2	1	1	7	35	46		D	4%	2%	2%	15%	76%	100%
8	E	1	2	0	0	0	3		E	33%	67%	0%	0%	0%	100%
9
10	RANK ORDER IN TERMS OF JOB
11		1	2	3	4	5
12	To/From	A	B	C	D	E
13	A	22	19	3	1	0
14	B	9	7	3	0	0
15	C	7	3	3	2	0
16	D	35	7	2	1	1
17	E	2	1	0	0	0
18
19	TOP 3 THREE IN TERMS OF JOB
20	top	3							TOP 3 THREE IN TERMS OF %
21	To/From	A	B	C	D	E			To/From	A	B	C	D	E
22	a	B	E	C					A	49%	42%	7%
23	b	D	C	E					B	47%	37%	16%
24	c	E	A	B					C	47%	20%	20%
25	d	E	D	A					D	76%	15%	4%
26	e	B	A						E	67%	33%
Sheet74

Cell Formulas
Range	Formula
G4	=SUM(B4:F4)
O4	=SUM(J4:N4)
A19	="TOP "&B20&" THREE IN TERMS OF JOB"
I20	="TOP "&B20&" THREE IN TERMS OF %"
J22	=IF(B22="","",INDEX($J4:$N4,MATCH(B22,$J$3:$N$3,0)))
B22	{=IF(COLUMNS($B22:B22)>COUNTIFS($B4:$F4,">="&LARGE($B4:$F4,MIN($B$20,COUNTIFS($B4:$F4,">"&0)))),"",INDEX($B$3:$F$3,AGGREGATE(15,6,(COLUMN($B4:$F4)-COLUMN($B$3)+1)/($B4:$F4=B13),COUNTIF($B13:B13,B13))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Job	=Sheet42!$A$2:$A$6

 

[DRSteele]

Forget that WOORKBOOK DEFINED RANGE NAME...I don't know what it's doin' there. Also, remove the word THREE from those two formulas.

 

[blue333]

Forget that WOORKBOOK DEFINED RANGE NAME...I don't know what it's doin' there. Also, remove the word THREE from those two formulas.

thanks for the reply!

I am trying to automate as much of this calculation as possible. We have a data feed that provided the data. For the most part you calculation looks great. The part that confuses me is the rank order table. What are you trying to do with that? Can that be automated? I don't mind if its a separate table but it just needs to be done automatically.

thanks again!!

 

[blue333]

The approach I have been using to calculate the top 3 is this formula

=LARGE($L9:$P9,COLUMNS($G20:G20))

$L9:$P9 contains the data that is being ranked.
$G20:G20 gives the rank needed (1st, 2nd, 3rd etc). This value increases as the formula is dragged across.

the problem with this approach is that if I want to change from top 3 to top 5 then I have to adjust my formulas by dragging them.

The data set I am working with is much larger than the one I have posted as a sample. I have close to 400 job titles I care about. Perhaps I need to look at top 10 or top 75. I have related tables that would also have to be re-adjusted. painful!

I like your approach alot. I just enter the number of top X i want and it gives it to me. That way i can setup the structure once and don't have to re-adjust each time for different scenarios. I just want to have to calculate the rank matrix each time. if that can be automated then that would be awesome!

thanks again for your help!!

 

[DRSteele]

Well let's see if I can explain the mess I've made. The Order Table is needed as fodder for the K argument in function AGGREGATE, which by necessity is an expanding range ( itself achieved with function COUNTIFS). I can't seem to reckon a way to make an array out of it something inside function COUNTIFS.

Note that the headings in B12:F12 are essentially meaningless and they can be omitted.

The table just there because we need to order every single item of the source data to make the algorithm function.

So, I think it's what you call automatic. If you refer to this video, you'll see the challenge presented by your data. I had to do what he did, yet for every line item and as columns instead of rows. https://youtu.be/rKDI-kdBsjY

 

[blue333]

Well let's see if I can explain the mess I've made. The Order Table is needed as fodder for the K argument in function AGGREGATE, which by necessity is an expanding range ( itself achieved with function COUNTIFS). I can't seem to reckon a way to make an array out of it something inside function COUNTIFS.

Note that the headings in B12:F12 are essentially meaningless and they can be omitted.

The table just there because we need to order every single item of the source data to make the algorithm function.

So, I think it's what you call automatic. If you refer to this video, you'll see the challenge presented by your data. I had to do what he did, yet for every line item and as columns instead of rows. https://youtu.be/rKDI-kdBsjY

thank you so very much!! The youtube video put into context what you were trying to do. I combined both your input and the youtube video to make it work. here is how i did with without any external rank table:

AGGREGATE(15,6,(COLUMN('To From (%)'!$B$5:$F$5)-COLUMN('To From (%)'!$B$5)+1)/('To From (%)'!$B5:$F5=B9),COUNTIF($B9:B9,B9)))

where B9 refers to the % table that was calculated earlier. the above formula is part of the calculation for returning the job titles.

you saved my life at work

have a great day!!

 

[DRSteele]

Good show! We did it. I'm glad to help ensure you still have a job! Thanks for posting your solution for passersby who might have similar questions.