Unique Names based on multiple criteria

[JCdo3]

Hello,
I am trying to populate unique names from range F11:F120 to M13:M29 based on whether range B4:B7 >"".
I cannot seem to wrap my head around which formulas to use. I would prefer to have this completed with a formula rather than VBA. This sheet will be used by people that do not have a lot of experience using Excel. My experience is about 6 months of trying to teach myself and understand formulas, so any help would be appreciated.

Util Tracker WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	Key 1		Key 2		Key 3			Date			RDC Total Utilization
4	Cesar	98.97%	Ivan		Fabian			4/1/2022									51
5	Lujan	96.06%	Ashleigh		Marco						98.60%
6	Jessica	99.83%	Ta		Ana
7	Jose G	99.29%	Eugenia		Alexis			Total Closed
8								51
9	Columns For Closed Trailers
10	Leader	Dock Door	Cube	Weight	Utilization	Loader	Comments						Key 1
11	Jessica	194	2726	40585	96.63%	JIM
12	Jessica	198	2300	42147	100.35%	JIM							Loader	Avg Utilization	Trailers Closed		Avg Shift Utilization
13	Jessica	175	2621	42002	100.00%	JIM							JIM	99.46%	8		98.60%
14	Jessica	196	2412	42083	100.20%	JIM							TIM	98.88%	7
15	Jessica	193	2655	42021	100.05%	JIM							JOHN	98.53%	11
16	Jessica	195	2908	36332	96.93%	JIM							MIKE	97.89%	12
17	Jessica	191	2328	42218	100.52%	JIM							KAYLA	98.63%	13
18	Jessica	176	3029	32287	100.97%	JIM
19	Jessica	197	2837	35936	94.57%	TIM
20	Jessica	188	2474	38692	92.12%	TIM											Trailers Closed
21	Jessica	180	3075	40147	102.50%	TIM											51
22	Jessica	182	2914	44548	106.07%	TIM
23	Jessica	178	3050	36637	101.67%	TIM
24	Cesar	350	2639	39740	94.62%	TIM
25	Cesar	347	2776	42258	100.61%	TIM
26	Cesar	358	2604	42196	100.47%	JOHN
27	Cesar	338	3091	40256	103.03%	JOHN
28	Cesar	355	3019	37773	100.63%	JOHN
29	Cesar	343	2632	42290	100.69%	JOHN
28

Cell Formulas
Range	Formula
H4	H4	=TODAY()
Q4	Q4	=SUM(O13:O29)
K5	K5	=IFERROR(AVERAGEIF($E$11:$E$120,">0.00%"),"")
B4:B7,F4:F7,D4:D7	B4	=IFERROR(AVERAGEIF($A$11:$A$120,A4,$E$11:$E$120),"")
H8	H8	=COUNTA($A$11:$A$120)
N13:N29	N13	=IFERROR(AVERAGEIF($F$11:$F$120,M13,$E$11:$E$120),"")
O13:O29	O13	=COUNTIF($F$11:$F$120,M13)
Q13	Q13	=IFERROR(SUMPRODUCT(N13:N29,O13:O29)/SUM(O13:O29),"")
Q21	Q21	=IF(Q13>"","",SUM($O$13:$O$29))
M13:M25,M27:M29	M13	=IFERROR(INDEX($F$11:$F$120, MATCH(0,COUNTIF($M$12:M12, $F$11:$F$120),-1)),"")
E11:E29	E11	=MAX((C11/3000),(D11/42000))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E11:E120,K5,Q13	Cell Value	<=0.001	text	YES
U13:U29,X13,K5	Cell	contains a blank value	text	YES
B4:B7,D4:D7,F4:F7,N13:N29,Q13	Cell	contains a blank value	text	YES
B4:B7,D4:D7,F4:F7	Cell Value	>=0.97	text	YES
B4:B7,D4:D7,F4:F7	Cell Value	between 0.95 and 0.969999999	text	YES
B4:B7,D4:D7,F4:F7	Cell Value	<=0.95	text	NO
E11:E120	Cell Value	<0.9	text	YES
E11:E120,K5,N13:N29,Q13	Cell Value	>=0.97	text	YES
E11:E120,K5,N13:N29,Q13	Cell Value	between 0.96999999999999 and 0.95	text	YES
E11:E120,K5,N13:N29,Q13	Cell Value	<=0.94999999999	text	NO

 

[kweaver]

Are you looking for this:

=UNIQUE(F11:F29)

 

[JCdo3]

Are you looking for this:

=UNIQUE(F11:F29)

Unfortunately, no it's not, I've tried to insert =IF(B4:B7>"",UNIQUE(M13:M29))
but this has not worked.

 

[JCdo3]

Are you looking for this:

=UNIQUE(F11:F29)

This does not do what I need it to. If cells B4:B7 have a percentage in them I want cells M13:M29 to filter through the F column and fill unique names in cells M13:M29. I have tried the formula
=IF(B4:B7>"0.0%","",UNIQUE(F11:F120))
I just get #N/A error and it stops looking for unique names after 4 cells.

Util Tracker WIP.xlsx
	M	N	O	P	Q	R
10	Key 1
11
12	Loader	Avg Utilization	Trailers Closed		Avg Shift Utilization
13	Daniel	97.67%	8		98.98%
14	Marcelino	102.24%	5
15	Melvin	98.51%	7
16	Jeff	98.32%	4
17	#N/A
18	#N/A
19	#N/A
20	#N/A				Trailers Closed
21	#N/A				24
22	#N/A
23	#N/A
24	#N/A
25	#N/A
26	#N/A
27
28
29
28

Cell Formulas
Range	Formula
M13:M26	M13	=IF(B4:B7>"0.0%", "",UNIQUE(F11:F120))
N13:N29	N13	=IFERROR(AVERAGEIF($F$11:$F$120,M13,$E$11:$E$120),"")
O13:O29	O13	=COUNTIF($F$11:$F$120,M13)
Q13	Q13	=IFERROR(SUMPRODUCT(N13:N29,O13:O29)/SUM(O13:O29),"")
Q21	Q21	=IF(Q13>"","",SUM($O$13:$O$29))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E11:E120,K5,Q13	Cell Value	<=0.001	text	YES
B4:B7,D4:D7,F4:F7,N13:N29,Q13	Cell	contains a blank value	text	YES
E11:E120,K5,N13:N29,Q13	Cell Value	>=0.97	text	YES
E11:E120,K5,N13:N29,Q13	Cell Value	between 0.96999999999999 and 0.95	text	YES
E11:E120,K5,N13:N29,Q13	Cell Value	<=0.94999999999	text	NO

 

[Peter_SSs]

See if this in M13 does what you want.

=LET(f,FILTER(A11:F120,A11:A120<>""),UNIQUE(FILTER(INDEX(f,0,6),VLOOKUP(INDEX(f,0,1),A4:B7,2,0)<>"")))

 

[JCdo3]

See if this in M13 does what you want.

=LET(f,FILTER(A11:F120,A11:A120<>""),UNIQUE(FILTER(INDEX(f,0,6),VLOOKUP(INDEX(f,0,1),A4:B7,2,0)<>"")))

Yes this worked! Thank you. If you don't mind, could you explain why "=IF(B4:B7>"0.0%","",UNIQUE(F11:F120))" does not work?

 

[Peter_SSs]

Yes this worked! Thank you.

You're welcome.

could you explain why "=IF(B4:B7>"0.0%","",UNIQUE(F11:F120))" does not work?

The most obvious thing is that the formula provides no link between the names in A4:A7 and the names in A11:A120
There is also an issue about using "0.0%" which is a text value whereas the values in B4:B7 are numbers.