Formula that can isolate records with missing values from expected results

[ek_skotous]

This is kind of a tough problem to describe. Bascially I have some Excel device reports from our Mobile Device Management (MDM) solution that will list multiple entries for a given users mobile device with one entry per applied policy. There are five policies that are supposed to be applied per device so for a device that has received all 5 policies I will see the same username, device model, device serial number, etc. listed 5 times (once for each policy)

If that user has a second device registered then I might also see that same username listed 5 more times for the second device model and serial number.

Ther serial number would always be unique for each grouping. The problem is that its possible that a device has only received some of the 5 policies. Thus one scenario I am trying to plan for is when a particualr device only has 3 or 4 of the required 5 policies so I can single those devices out of the Excel list.

Bascially for a given serial number I want an Excel formula to show me any records (userID, device model, device serial number) for which a row does not exist for any of the 5 defined policies. In other words show me ONLY those devices that are missing one or more of the 5 policies. These identified problem devices might be for a user who has only one device or they may ONE misconfigured device belonging to a user who has multiple devices even though that users other device(s) is(are) fine.

I built a simple sample spreadsheet which I attached to illustrate the type of data I have. For the sample data I made up some user data with userID's device models, device serial numbers, and the five policies I am checking for (Policy_A, Policy_B, Policy_C, Policy_D, Policy_E)


I am trying to wrap my brain around how to best do this. I thought about trying to parse a master list of all unique serial numbers against individual filtered lists of each policy I am checking against (and somehow trying to generate a list of serial numbers that don't have matches in all of the seperate policy lists but I don't think I can run formulas against the results of filtered lists without having to manually copy each filtered list its own worksheet as the results of that filter. I am hoping to find a way to do this automatically with a formula and avoid lots of manual work as this will become something that will need to be checked on a regular ongoing basis with eventually as many as 10,000+ devices with 5 or more policy records per device so a 50,000+ record spreadsheet. The stare and compare method of scanning for each of the 5 policies (that I am using now) will soon fail as we bring more clients into our MDM solution.


Hopefully what I am trying to do makes sense. I am a good PC troubleshooter and pretty decent with Excel but I am not a programmer and thus far I haven't been able to figure out a workable approach.

Any assistance or advice would be greatly appreciated!

Thanks,

ek_skotous

[daffodil11]

I used SUMPRODUCT and INDEX MATCH.

Sumproduct checks for missed policies per unique Serials. (You can quickly generate a list of uniques with Data -> Remove Duplicates)

Index and Match pull back the user and product that is associated with it.

IllustrativeDataSet huzzah.xlsx

[ek_skotous]

WOW! That is great! Thank you very much!!!!

Is there any way to set the output table to ONLY report on serial numbers where there is a missing policy instead of showing all of the unique serial numbers that don't have any missing policies? Even with what you have shown I could ultimately filter on blank users since userID are only reported for missing policies to get there but was wondering if there was a way in the formula to do that?

Thanks again! What you have demonstrated is a HUGE help!

-ek_skotous

[daffodil11]

Sure, with some RANK, COUNTIF, and a little extra MATCH and INDEX.

I use these functions quite a bit for creating data dumps, where I just paste a bunch of raw data into one tab and have formulas on other tabs sort and present only the relevant information.

I left some directions in the file.

Let me know if you have any questions.

IllustrativeDataSet huzzah.xlsx

[ek_skotous]

Thank you VERY much! Based on your input and a little bit of experimentation arising from your input, I have been able to create a workable solution to my dilema while working with my productoin data (which is a bit more complicated than the example)!

Not sure how to mark this as solved but I would consider this solved at this point

Thank you again VERY much!!!!


-ek_skotous

[daffodil11]

You just change the Thread Options at the top of the post to Mark as Solved.

Glad I could help.