Simple Asset Management - Formula

[MD1032]

Hi all,

Long time lurker here. I usually find answers here but unfortunately, I'm a little stumped on this one!

I have two tables below. The first one is the main data table. The second one is an example of what it is supposed to display.
There are multiple assets for each desk. All desks already have screens present. I need to discover which desks are missing PCs.

Any ideas?
I have a feeling I have to combine an IF formula with either a VLOOKUP or XLOOKUP - But completely lost right now.

Thanks very much.

Asset	Desk Number	Type
791​	1​	Screen
864​	1​	Screen
788​	1​	PC
796​	2​	Screen
705​	2​	Screen
693​	3​	Screen
728​	3​	Screen
721​	4​	Screen
787​	4​	Screen
740​	4​	PC
753​	5​	Screen
813​	5​	PC

Desk Number	PC Present?
1​	Yes
2​	No
3​	No
4​	Yes
5​	Yes

 

[kevin9999]

One possibility

Book1
	A	B	C
1	Asset	Desk Number	Type
2	791	1	Screen
3	864	1	Screen
4	788	1	PC
5	796	2	Screen
6	705	2	Screen
7	693	3	Screen
8	728	3	Screen
9	721	4	Screen
10	787	4	Screen
11	740	4	PC
12	753	5	Screen
13	813	5	PC
14
15	Desk Number
16	1	Yes
17	2	No
18	3	No
19	4	Yes
20	5	Yes
Sheet1

Cell Formulas
Range	Formula
B16:B20	B16	=IF(COUNTIFS($B$2:$B$13,A16,$C$2:$C$13,"PC")<1,"No","Yes")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B16:B20	Cell Value	contains "No"	text	NO

 

[MD1032]

Lovely stuff!
I just attempted this on my dataset and it seems to be working great!

Thank you so much.

Happy new year.

 

[kevin9999]

Happy to help & thanks for the feedback