Excel - Compare data based upon specific criteria split into columns and delimiters

Hi all,

I am a new member here and I hope you will be able to help me out on my requirement. I need to automate an excel report which will contain IT infrastructure data. There are 3 main columns or headers: "SERVERS", "PORTS" and "SWITCHES".

The idea is to test and compare this data based upon specific criteria and then returning the appropriate results. The data in "PORTS" column is interdependent to data in "SWITCHES" column. Data is split within cells by delimiters. If the ports are connected to different Switches, I expect to get the result "OK = Redundancy exist", otherwise "NOK = Single Point of Failure".

To give you a better understanding, I have given you below a few examples.

----------

1. Example1 in Row2

CellA2 = Server_FRANCE
CellB2 = NIC1 | NIC2 | ILO
CellC2 = SwitchA | SwitchA | SwitchB

- Note: The values in B2 are interdependent with values of C2, that's the reason why we have delimiters (NIC1 | NIC2 belong respectively to the same Switch A and ILO to SwitchB). The name of ports and switches have been chosen for example. They will vary depending on the servers and switches.

- Need: Only apply test on ports NIC1 and NIC2 and compare if they are connected or not to the same SwitchA. (ILO out of scope all the time but need to be displayed in "PORT" column).

- Result expected: Here, the result will be "NOK" as we don't want to have the ports NIC1 and NIC2 connected to the same Switch.

----------
2. Example2 in Row3

CellA3 = Server_UK
CellB3 = NIC1 | ILO | NIC2 | NIC3
CellC3 = SwitchA |SwitchA | SwitchB | SwitchC

- Note: Here, the position of ILO is after the 1st delimiter. Once again, The values in B3 are interdependent with values of C3 meaning that NIC1, NIC2 and N3 belong respectively to Switch A, SwitchB and SwitchC. ILO is out of scope all the time but need to be displayed in "PORT" column. The name of ports and switches have been chosen for example. They will vary depending on the servers and switches.

- Result expected: "OK" (as we want the ports connected to different switches).

----------
3. Example3 in Row4

CellA4 = Server_Australia
CellB4 = ETH0 | ETH1 | ILO | ETH2
CellC4 = SwitchA | SwitchA | SwitchB | SwitchA

- Need: Tests on all ports (except ILO but it needs to be displayed in port column). The name of ports and switches have been chosen for example. They will vary depending on the servers and switches.

- Result expected: "NOK" (as the ETHO and ETH1 and ETH2 ports are connected to the same SwitchA).

----------

4. Example4 in Row5

CellA5 = Server_India
CellB5 = ETH0 | ETH1 | ETH2
CellC5= SwitchA | SwitchA | SwitchB

- Note: Sometimes we have a few cases where the ILO port is not present. Test will apply on the available ports.
- Result expected: "OK".


Apologies in advance for being sometimes a bit repetitive as I couldn't attach any spreadsheet.
Thanks a lot for sharing your ideas.

Regards,
Mike

Hi CK76 - First, thanks for your help. I have ran your code in my spreadsheet, but it returns "NOK" instead of "OK" for a few servers. I have attached a screenshot of my spreadsheet:

Capture.JPG