Multiple servers are labled incorrectly; need to run a "circular check" to ouput yes or no

[Fao]

Hello All,

This is a very hard problem to explain.

• I need to update a list of servers that have a particular application installed.
• The issue is that there are duplicate server names.
• This has to stay this way because those servers have multiple applications installed and those need to be accounted for as well.
• I've given an example of what I trying to do below. Hopefully that explains it better.

INDEX_SMALL_IF_COUNTIF_Capture.PNG

The Count or “Check” (for lack of a better term) that needs to occur before and after each server name doesn’t appear to be taking place.

***Here’s the formula I used: =INDEX($B$1:$B$36188, SMALL(IF(COUNTIF($A$1:$A$36188,$B169), ROW($A$1:$A$36188)-ROW($A$1)+1), ROW($1:1)))

Any help, from anyone would be greatly appreciated. Thanks!

[6StringJazzer]

Here is what I think you are saying:

The result in column C should be Yes if a Yes appears in column B for any of the occurrences of the same server name in column A.

The formula for column C should be

Please Login or Register  to view this content.

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

If this does not solve your problem please attach a sample file. An image has limited value. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[Fao]

Hello good sir! Thank you so much! This formula works perfectly. I appears that I was way off course. Thanks again!

[6StringJazzer]

You're welcome! I think this could also be done with SUMPRODUCT but if the first one works I'll leave it at that.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.