0

hard one to explain this but here goes; I have a spreadsheet from SCCM of all the installed software on the estate and I want to identify users with "base" layer of apps, so MS office basically. Here's an example

Username     Computername       Software
John          A1234              Microsoft Office 2013
John          A1234              Microsoft Project2013
John          A1234              SAP 
John          A1234              Cisco VPN
Jane          A5678              Microsoft Office 2013
Jane          A5678              Microsoft Visio 2013
Greg          A3456              Microsoft Office 2013
Greg          A3456              Microsoft Project2013
Greg          A3456              Visual Studio 2017 
Greg          A3456              Adobe Photoshop

From that data I want to identify all users like Jane who only use office apps, I have 130,000 rows so it'd take forever to do manually and I can't filter as everyone has these apps. I have a nice pivot but I can't filter it down to these users. I've messed about with conditional formatting to no avail...

Any help greatly appreciated!

John

3
  • 1
    Create a "table" which contains all MS Office software. Create additional column which checks if current software is office one (vlookup(,,,false)). Create one more column which compares if countif(username = current) = summifs(username = current, software is office).
    – Akina
    May 29, 2019 at 9:06
  • Thanks @Akina I'm struggling a bit with that - the vlookup of the software column against the table of MS software only returns one result. and I'm not even sure how to implement the last formula, dummies guide for me would be appreciated :-)
    – John
    May 29, 2019 at 13:27
  • Vlookup working - trying the second part now :-)
    – John
    May 29, 2019 at 13:39

1 Answer 1

0

Assume the sample source data occupies A1:C11.

Create Office software names table in H1:H4 (header and 3 products in sample data).

Insert formulas:

D2=ISNA(VLOOKUP(C2,H:H,1,FALSE))+0
E2=SUMIFS(D:D,A:A,A2)

Drag formulas over D2:E11.

The users which do not have non-Office software are detected by zero value in column E.

To obtain user names and non-office soft counts only build pivot table with names (column A) and min/max value (column E).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .