Populate columns based on multiple criteria

[JonasTiger]

Hi
I'm trying to build a table as shown in the attached file (S10:AD29) based in a data table(B9:N109) with the following tasks:
Populate Store Columns (yellow) if ITEM has PRIO1
If PRIO1 ITEMS >20, then transfer each remain item to next PRIO
If PRIO1 ITEMS <20, then add to list ITEMS with PRIO2, and so on…

Is this possible with formulae?

testeCPforum.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
8				PRIORITIES				CHOICES
9	ORD	ITEM	LOC	Prio1	Prio2	Prio3	Prio4	STO1	STO2	STO3	STO4	STO5	PRIOcoef		PRIOrank	PRIOrank1		N	STO1	PRIOcoef	N	STO2	PRIOcoef	N	STO3	PRIOcoef	N	STO4	PRIOcoef	N	STO5	PRIOcoef
10	1	ITEM 01	2	0	1	0	1	5	1	3	2	4	30		30			1	ITEM 18	70	21	ITEM 10	55	41	ITEM 07	45	61	ITEM 58	40	81	ITEM 37	55
11	2	ITEM 02	1	0	0	1	1	3	4	1	2	5	20		65			2	ITEM 31	55	22	ITEM 15	100	42	ITEM 13	40	62			82	ITEM 65	40
12	3	ITEM 03	1	0	0	1	1	1	2	3	4	5	20		66			3	ITEM 45	60	23	ITEM 34	45	43	ITEM 30	60	63			83	ITEM 93	85
13	4	ITEM 04	2	0	1	0	0	1	5	4	2	3	25		46			4	ITEM 46	55	24	ITEM 43	40	44	ITEM 35	45	64			84
14	5	ITEM 05	3	0	0	0	1	5	1	4	2	3	5		79			5	ITEM 60	100	25	ITEM 62	40	45	ITEM 55	40	65			85
15	6	ITEM 06	2	0	0	0	1	5	1	3	4	2	5		80			6	ITEM 82	80	26	ITEM 66	70	46	ITEM 63	55	66			86
16	7	ITEM 07	1	0	1	1	1	2	5	1	3	4	45		20			7	ITEM 88	55	27	ITEM 70	70	47	ITEM 64	70	67			87
17	8	ITEM 08	2	0	1	0	1	2	3	1	5	4	30		31			8			28	ITEM 75	55	48	ITEM 86	45	68			88
18	9	ITEM 09	2	0	0	0	1	5	3	2	1	4	5		81			9			29	ITEM 90	100	49	ITEM 100	70	69			89
19	10	ITEM 10	2	1	0	0	0	2	1	3	4	5	55		13			10			30			50			70			90
20	11	ITEM 11	2	0	1	0	1	1	2	3	4	5	30		32			11			31			51			71			91
21	12	ITEM 12	2	0	0	0	1	1	5	2	3	4	5		82			12			32			52			72			92
22	13	ITEM 13	1	0	1	1	0	3	5	1	4	2	40		24			13			33			53			73			93
23	14	ITEM 14	3	0	1	0	0	5	1	4	2	3	25		47			14			34			54			74			94
24	15	ITEM 15	1	1	1	1	1	5	1	3	2	4	100		1			15			35			55			75			95
25	16	ITEM 16	3	0	0	0	1	3	4	1	2	5	5		83			16			36			56			76			96
26	17	ITEM 17	2	0	0	0	1	1	2	3	4	5	5		84			17			37			57			77			97
27	18	ITEM 18	1	1	0	1	0	1	5	4	2	3	70		6			18			38			58			78			98
28	19	ITEM 19	2	0	1	0	0	5	1	4	2	3	25		48			19			39			59			79			99
29	20	ITEM 20	2	0	1	0	0	5	1	3	4	2	25		49			20			40			60			80			100
Sheet1 (2)

Cell Formulas
Range	Formula
AC8	AC8	=IFERROR(INDEX(Table13[PRIOcoef],MATCH(AA8,Table13[ITEM],0)),"")
T10:T16	T10	=FILTER(Table13[ITEM],(Table13[STO1]=1)*(Table13[PRIOcoef]>=40))
AG10:AG29,AD10:AD29,AA10:AA29,X10:X29,U10:U29	U10	=IFERROR(INDEX(Table13[PRIOcoef],MATCH(T10,Table13[ITEM],0)),"")
V10,Y10,AB10,AE10	V10	=S10+20
W10:W18	W10	=FILTER(Table13[ITEM],(Table13[STO2]=1)*(Table13[PRIOcoef]>=40))
Z10:Z18	Z10	=FILTER(Table13[ITEM],(Table13[STO3]=1)*(Table13[PRIOcoef]>=40))
AC10	AC10	=FILTER(Table13[ITEM],(Table13[STO4]=1)*(Table13[PRIOcoef]>=40))
AF10:AF12	AF10	=FILTER(Table13[ITEM],(Table13[STO5]=1)*(Table13[PRIOcoef]>=40))
V11:V29,S11:S29,AE11:AE29,AB11:AB29,Y11:Y29	V11	=V10+1
G10:G29	G10	=IF([@LOC]=1,1,0)
N10:N29	N10	=IF([@Prio1]=1,$E$1,0)+IF([@Prio2]=1,$F$1,0)+IF([@Prio3]=1,$G$1,0)+IF([@Prio4]=1,$H$1,0)
P10:P29	P10	=RANK(Table13[@PRIOcoef],Table13[PRIOcoef],0)+COUNTIF($N$10:N10,N10)-1
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AE12:AE29	Cell Value	duplicates	text	NO
AE11	Cell Value	duplicates	text	NO
AB12:AB29	Cell Value	duplicates	text	NO
AB11	Cell Value	duplicates	text	NO
Y12:Y29	Cell Value	duplicates	text	NO
Y11	Cell Value	duplicates	text	NO
V12:V29	Cell Value	duplicates	text	NO
V11	Cell Value	duplicates	text	NO
AF10	Cell Value	duplicates	text	NO
AC10	Cell Value	duplicates	text	NO
Z10	Cell Value	duplicates	text	NO
W10	Cell Value	duplicates	text	NO
T10	Cell Value	duplicates	text	NO
O11,R11:T11,S12:S29	Cell Value	duplicates	text	NO