Lookup with multiple conditions

[dspblues]

I have a large list of servers and I'd like to find the serial #s that meet a couple conditions. I'm open to how I do this... formulas, pivots, filtering?

I'd like to know, by serial #, show me only the serial #s that ONLY have windows and are running.

Thoughts?

[sktneer]

May be this array formula. Since this is an array formula so remember to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

Please Login or Register  to view this content.

And then drag down until you get the blank cells.

** You have an extra space in the end where col. C contains word "windows" so I removed that extra space.

Please find the attached sheet to see if this is what you want.

[dspblues]

Not sure this is what I need. I need a report of all serial #s that meet this criteria. Does this help me get there?

[sktneer]

Please see the attached sheet.

[dspblues]

With what you provided, you could do with a simple filter. I need to know if only windows is up and running on each serial #. In the example I provided, the results should be 0, but I should change that for the purpose of this example.

[dspblues]

excel help.xlsx

I've added another serial # to this to demonstrate what I'm looking for. In the end, I need a list of unique serial #s where the only thing running is a windows server. In real life, I have a spreadsheet with 5000 rows of data.

Any ideas?

[dspblues]

Anyone? I'm sure there's a good way to do this, I just can't figure it out.

[sktneer]

Try this Array Formula which requires Ctrl+Shift+Enter.
In F2

Please Login or Register  to view this content.

and copy down.
Is this what you are trying to achieve?

[samba_ravi]

Post Your Excepted Result Manually so that it is easy to understand

[dspblues]

Attached is what I'm trying to achieve. See column E.

excel help.xlsx

[dspblues]

To expand, I want to know any unique serial # where the condition exists that only windows servers are "running".

[sktneer]

That is really confusing. Would you please explain the logic behind the Trues in the range E14:E18?

[dspblues]

I don't know how to write the logic. In lay terms, I need to know which unique serial #s have ONLY windows running. In my example, serial #4 is the only one that qualifies.

[sktneer]

Do you mean that you want the serial # where there is Running in the status column and Windows in the OS version column? Right?
If this is the criteria, why not E2, E6, E8 and E12 are Trues and Why E14, E15 and E16 are Trues? Because in E14, E15 and E16, the corresponding status is Not Activated and OS Version is linux. And in E2, E6, E8 and E12, the corresponding status is Running and OS Version is Windows.

[dspblues]

It's not true because:

Serial #1 (column D) has other systems running besides windows on the server. (row 3, 4)
Serial #2 - has other systems running besides windows on the server. (row 9)
Serial #3 - - has other systems running besides windows on the server. (row 11)
Serial #4 - is the only serial # in the group that only has "windows" "running", all other systems are not activated.

[sktneer]

In E2

Please Login or Register  to view this content.

Or in F2, try this array formula.

Please Login or Register  to view this content.

[dspblues]

Hmm. Neither of those worked for me.

[Jacc]

Here are both of sktneer's solutions implemented plus one of my own. do any of these work for you?

[dspblues]

Yes, thank you both! I may go with what's in column E and use a Pivot to filter.

I'm interested in what each formula actually does as I'm not an expert in these nested formulas, yet.

[Jacc]

Good that it works! I forgot to mention that these are array formulas, they need to be entered with Ctrl + Shift + Enter.
You can get some insight in how the formulas work by using the Evaluate Formula button under the Formulas tab.