Excel grouping of data

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

John

Posted 2019-05-29T08:41:20.817

Reputation: 235

1Create 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 – 2019-05-29T09:06:35.913

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 – 2019-05-29T13:27:11.833

Vlookup working - trying the second part now :-) – John – 2019-05-29T13:39:18.943

Answers

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).

Akina

Posted 2019-05-29T08:41:20.817

Reputation: 2 991

Asked: 2019-05-29T08:41:20.817

Viewed: 32 times

Active: 2019-05-29T13:45:30.967