How to identify "missing entries" in a worksheet

I have a spreadsheet that is a patch inventory by server (server name in Col A, patch name in Col E).

It is sorted on server name but not necessarily also on patch name. There is one row for each server for each patch - if a server has ten patches applied there will be ten rows for that server.

With data filter I can select a server to see what patches are applied or I can select a patch to see what servers it is on.

I want exactly the opposite of that. I want to select a server to see what patches are not on - or select a patch to see what servers it is not on.


Say that I have 50 servers, each with (the same) 15 patches required.

I envision a master list of servers and a master list of patches.

If I click on a server that has only ten patches applied I would like to see a list containing the 5 patches that are missing
If I click on a patch that is only applied to 40 servers I would like to see a list containing the 10 servers that don't have that patch

One thought is having the two master lists side by side. When clicking a server in the server master list it could search the patches on that server and color code the patches in the patch master list that it finds.

The patch inventory list (the main part of the spreadsheet) will change at least weekly.

The server master list will change, but just occasionally

The patch master list could change daily.

Does anyone have any clues on where to start, or what forum this should go into?

Paul

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Sample sheet will be attached. It is 33 rows containing a list of patches applied to two different servers. The actual data is over 1400 rows containing information on several dozen servers. The only columns relevant to this question are Col A (server) and Col G (patch). This data will be regenerated every week with automation.

Somewhere else I am going to have a manually updated list of servers and a manually updated list of required patches, Like the 'grid" below. Using the data...filter I can select a server and see all patches on it OR I can select a patch and see all of the servers on it. What I cannot see is exceptions (A server missing patches OR a patch not on all servers). Note that the list of servers rarely changes but the list of patches changes frequently.

My ideal solution would:

1) When clicking on a server in the list below somehow visually differentiate between patches on that server (eg maybe a checkmark in a col next to each patch). If I click on cemapp-cms-d1 below it will find all of the rows of data with that server in Col A then check those rows Col G against each of the patches below (partial match). In this case all 16 would be found. If clicking the other server only 12 of the patches would be found.

2) When clicking on a patch in the list below somehow visually differentiate between patches on that server (eg maybe a checkmark in a col next to each server). If I click on IFPH36923 below it will find all of the rows of data with that patch in Col G (partial match) then check those rows Col A against each on of the servers below. In this case it would be found on only the first server while IFPH33994 would be found on both.

List of all patches List of all servers
IFPH35098 cemapp-cms-d1
IFPH34122 cemapp-cms-p1
IFPH34968
IFPH36463
IFPH33994
IFPH34944
IFPH34067
IFPH34048
IFPH24409
IFPH34906
IFPH34501
IFPH34711
IFPH33648
IFPH33037
IFPH33228
IFPH36923

What I see looks awesome and exactly what I need. Now to see if my noob Excel skills can incorporate this into the full set of data.

Thank you!

Is there any way to dynamically set ranges in formulas?

I am trying to incorporate my live data into this spreadsheet. The sample spreadsheet consisted of 34 rows encompassing two servers. The current live data consists of 1634 rows encompassing 73 servers. Next week there could be 2000 rows and next month there could be 200.

The lists sheet is mostly formulas that need to adjust down (or up) and right (or left) depending on the data. The output sheet looks to have some formulas that would need to be adjusted too.

Thank you.

I've been playing around with this by adding actual data (which is a new spreadsheet every week or two). I just copy and paste the data into the existing table. The new data is 1633 rows. Do I need to do this differently?

It looks like the table adjusts accordingly and the server list and fix list seem to populate correctly but the matrix part of the sheet looks to be only partially populated (spaces instead of 0 or 1).

On the output sheet it looks like I get a complete fix list dropdown and the identifying of servers works as far as I can tell. However, the server list dropdown only gives me three of the 75 servers (or it had more but reverted to three).

Even this way it has provided value in that it told be that a production server was missing a critical fix.

Paul

Thank you.

I'm pretty sure that this anomaly is due to the way that I resized the input table to 1633 rows. The lists "array" falls apart on row 38. And as I understand it, are the values essentially true or false? The 3s and 4s have me scratching my head.

Thank you. The threes and fours make sense now since the SDK patch gets applied to four different products on each server.

Am I correct in saying that every data refresh requires updates to formulas and data validation sources (the source file updated today has 1752 rows).

Paul

I really appreciate the help that you have provided on this. I know that I had barely scratched the surface on Excel skills but this is a whole different level.