Unique Names based on multiple criteria

JCdo3

New Member
Joined
Mar 30, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
ABCDEFGHIJKLMNOPQR
3Key 1Key 2Key 3DateRDC Total Utilization
4Cesar98.97%Ivan Fabian 4/1/202251
5Lujan96.06%Ashleigh Marco 98.60%
6Jessica99.83%Ta Ana 
7Jose G99.29%Eugenia Alexis Total Closed
851
9Columns For Closed Trailers
10LeaderDock DoorCubeWeightUtilizationLoaderCommentsKey 1
11Jessica19427264058596.63%JIM
12Jessica198230042147100.35%JIMLoaderAvg UtilizationTrailers ClosedAvg Shift Utilization
13Jessica175262142002100.00%JIMJIM99.46%898.60%
14Jessica196241242083100.20%JIMTIM98.88%7
15Jessica193265542021100.05%JIMJOHN98.53%11
16Jessica19529083633296.93%JIMMIKE97.89%12
17Jessica191232842218100.52%JIMKAYLA98.63%13
18Jessica176302932287100.97%JIM   
19Jessica19728373593694.57%TIM   
20Jessica18824743869292.12%TIM   Trailers Closed
21Jessica180307540147102.50%TIM   51
22Jessica182291444548106.07%TIM   
23Jessica178305036637101.67%TIM   
24Cesar35026393974094.62%TIM   
25Cesar347277642258100.61%TIM   
26Cesar358260442196100.47%JOHN  
27Cesar338309140256103.03%JOHN   
28Cesar355301937773100.63%JOHN   
29Cesar343263242290100.69%JOHN   
28
Cell Formulas
RangeFormula
H4H4=TODAY()
Q4Q4=SUM(O13:O29)
K5K5=IFERROR(AVERAGEIF($E$11:$E$120,">0.00%"),"")
B4:B7,F4:F7,D4:D7B4=IFERROR(AVERAGEIF($A$11:$A$120,A4,$E$11:$E$120),"")
H8H8=COUNTA($A$11:$A$120)
N13:N29N13=IFERROR(AVERAGEIF($F$11:$F$120,M13,$E$11:$E$120),"")
O13:O29O13=COUNTIF($F$11:$F$120,M13)
Q13Q13=IFERROR(SUMPRODUCT(N13:N29,O13:O29)/SUM(O13:O29),"")
Q21Q21=IF(Q13>"","",SUM($O$13:$O$29))
M13:M25,M27:M29M13=IFERROR(INDEX($F$11:$F$120, MATCH(0,COUNTIF($M$12:M12, $F$11:$F$120),-1)),"")
E11:E29E11=MAX((C11/3000),(D11/42000))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E11:E120,K5,Q13Cell Value<=0.001textYES
U13:U29,X13,K5Cellcontains a blank value textYES
B4:B7,D4:D7,F4:F7,N13:N29,Q13Cellcontains a blank value textYES
B4:B7,D4:D7,F4:F7Cell Value>=0.97textYES
B4:B7,D4:D7,F4:F7Cell Valuebetween 0.95 and 0.969999999textYES
B4:B7,D4:D7,F4:F7Cell Value<=0.95textNO
E11:E120Cell Value<0.9textYES
E11:E120,K5,N13:N29,Q13Cell Value>=0.97textYES
E11:E120,K5,N13:N29,Q13Cell Valuebetween 0.96999999999999 and 0.95textYES
E11:E120,K5,N13:N29,Q13Cell Value<=0.94999999999textNO
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Are you looking for this:
Code:
=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
MNOPQR
10Key 1
11
12LoaderAvg UtilizationTrailers ClosedAvg Shift Utilization
13Daniel97.67%898.98%
14Marcelino102.24%5
15Melvin98.51%7
16Jeff98.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
RangeFormula
M13:M26M13=IF(B4:B7>"0.0%", "",UNIQUE(F11:F120))
N13:N29N13=IFERROR(AVERAGEIF($F$11:$F$120,M13,$E$11:$E$120),"")
O13:O29O13=COUNTIF($F$11:$F$120,M13)
Q13Q13=IFERROR(SUMPRODUCT(N13:N29,O13:O29)/SUM(O13:O29),"")
Q21Q21=IF(Q13>"","",SUM($O$13:$O$29))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E11:E120,K5,Q13Cell Value<=0.001textYES
B4:B7,D4:D7,F4:F7,N13:N29,Q13Cellcontains a blank value textYES
E11:E120,K5,N13:N29,Q13Cell Value>=0.97textYES
E11:E120,K5,N13:N29,Q13Cell Valuebetween 0.96999999999999 and 0.95textYES
E11:E120,K5,N13:N29,Q13Cell Value<=0.94999999999textNO
 
Upvote 0
See if this in M13 does what you want.

Excel Formula:
=LET(f,FILTER(A11:F120,A11:A120<>""),UNIQUE(FILTER(INDEX(f,0,6),VLOOKUP(INDEX(f,0,1),A4:B7,2,0)<>"")))
 
Upvote 0
Solution
See if this in M13 does what you want.

Excel Formula:
=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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top