UNIQUE list based on crieria

[thewiseguy]

How can I create a formula that would give me the unique values in column W, based on column U being equal to anything EXCEPT "Materials," or "Rental"??

Many thanks in advance!

v2023.8 - Copy - Copy.xlsm
	U	V	W	X	Y	Z
2	ECM		ECM SubCategory	QTY (fixture)	Add. Labor	Install Note
3	Lamp	BR Lamp	HALCO 8W LED BR30 Lamp 27K	1
4	Accessory		HALCO Joiner Bracket for LWA Wrap	1
5	Accessory		KEYSTONE Constant Wattage LED Emergency Backup	1
6	Materials		TBD 2-Screw EMT Set Screw Coupling 3/4-Inch	1
7	Materials		TBD Tan Wirenuts	1
8	Rental		UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day	1
9	Control		MAXLITE Network Node -Round, White	1
10	Fixture	Area	MAXLITE 53W LED PostTop Area Light [3K]	1
11	Accessory		HALCO Joiner Bracket for LWA Wrap	1
12	Accessory		KEYSTONE Constant Wattage LED Emergency Backup	1
13	Materials		TBD 2-Screw EMT Set Screw Coupling 3/4-Inch	1
14	Materials		TBD Tan Wirenuts	1
15	Rental		UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day	1
16	Control		MAXLITE Network Node -Round, White	1
Input

 

[Rick Rothstein]

I'm a little confused by your request that you want "unique" values from Column W when, especially based on the column's title, they all seem to be unique from each other already. With that said, this might do what you want...

=FILTER(W3:W16,(U3:U16<>"Materials")*(U3:U16<>"Rental"))

 

[thewiseguy]

Rick - thank you again; this worked perfectly. Column W has many repeated values so I am trying to pull the unique values out, which will in turn give me the products being utilized on each project. I am trying to exclude certain values and your formula did just that. Many thanks!

*Edit - would you mind showing me how to do the same thing but INCLUDING ONLY the 2 criteria above ("Materials" and "Rental"?

 

[Rick Rothstein]

Okay, to make sure your list is unique, just wrap a UNIQUE function call around my formula...

=UNIQUE(FILTER(W3:W16,(U3:U16<>"Materials")*(U3:U16<>"Rental")))

As for returning only Column W values for Materials or Rental, use this formula...

=UNIQUE(FILTER(W3:W16,(U3:U16="Materials")+(U3:U16="Rental")))