We help IT Professionals succeed at work.

advanced filter formula.

I have got an extract of data, with 2 key columns, computer, and software. the data is basically a software inventory of devices and in a sequential order, e.g.

computer 1 - software 1
computer 1 - software 2
computer 1 - software 3
computer 2 - software 1
computer 2 - software 2
computer 2 - software 3
computer 3 - software 1
computer 3 - software 2
computer 3 - software 3

There is one particular piece of software that should be on ALL computers, but I have fears it is not. I need a way to supply a value e.g. software 2, and filter my data so it returns just the computers who do not appear to have software 2 installed.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
If your data is in A2:B9 then enter this formula in C2

=SUMPRODUCT(($A$2:$A$9=A2)*($B$2:$B$9=" software 3"))

and copy down

Then filter the zeros

Rob HensonFinance Analyst
CERTIFIED EXPERT
Commented:
Similar to above but using COUNTIFS

=COUNTIFS($A$:$A$9,$A2,$B$2:$B$9,"Software 3")

and again filter on zeros.

Could also use Pivot Table

Create the Pivot and use the Computer Name as a row header and Software as a column Header and as a Data Value.

See attached, have also applied Conditional Formatting to the Pivot Table to show where the count of blanks across the row is greater than zero.
Software-count.xlsx