Cell comparison and row inserts

Question

I'm trying to compare files between multiple server's on our ERP program. Each server seems to have different files on some and not the others. I've got all my information in an excel sheet as to what server has what on it (including date, file name/type, and size of file), but I'd like to have the rows organized by their file name.

The way I have this setup is I have a header field for each server and each server has 3 columns. I want each row to have that same file name for each server group. So say cell A3, D3, and J3 = 12345.txt and cell G3 is 123.txt. I want cell's A3, D3, G3, and J3 to all have the same name for that row, if one of them doesn't, insert a row and push that section down. The original space can stay blank to show me that that server is missing said file.

A3 is my master column, but some server's have files that it doesn't, so even A3 doesn't have the same file as D, G, and J, I want A1-3 pushed down a row then.

I want everything in alphanumeric order, which is why even though columns A-C are the "master", I want a good visual representation of what it has and doesn't have in comparison to the other server's.

Is this possible with Excel? (I'm running office 365)

Picture from comments:

Answer 1

Here is a quick summary of the Pivot Table idea. I think it works well for viewing which files are/are not present on given servers. It also makes it possible to filter/sort/count and do all variety of things which are not possible when you start adding a bunch of blank rows.

Steps to do this:

• Create a set of random file names (with half shared between each list). This step is not needed since you have the data.
• Join the two lists and add the server name to each list. There is now one large table. I am just doing filename, you can do this with all your fields.
• Create a Pivot Table out of the data. On the rows you add the filename, server name on the column, and a COUNT in the values.
• (Optional) If you really need the filename repeated under the servers, you can add some formulas to the side to display them.

Picture of data and results

Formulas for the last table

Header (copied to the right) in M4

=I4

Data (copied down for both columns) in M5

=IF(I5<>"", $H5,"")