Lookup Multiple Entries AND Expand Lookup to Include Dependencies

[nutti7312]

Hi All,

I have a list of hundreds of server with multiple applications on them. I want to lookup and list all servers with the same application on them but also want to identify server dependencies which occur through additional applications on the server. Hopefully this example will explain what I am trying to achieve.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Servername[/TD]
[TD]Application[/TD]
[TD]Application[/TD]
[TD]Application[/TD]
[TD]Application[/TD]
[/TR]
[TR]
[TD]SVFRA1[/TD]
[TD]ForeFront[/TD]
[TD]MS SQL[/TD]
[TD]NetBackup[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SVFRA2[/TD]
[TD]WinPcap[/TD]
[TD]Wireshark[/TD]
[TD]MS SQL[/TD]
[TD]LatencyMon[/TD]
[/TR]
[TR]
[TD]SVFRA3[/TD]
[TD]Emulex[/TD]
[TD]PageGate[/TD]
[TD]Wireshark[/TD]
[TD]Solarwinds[/TD]
[/TR]
[TR]
[TD]SVFRA4[/TD]
[TD]SharePoint[/TD]
[TD]CMTrace[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SVFRA5[/TD]
[TD]ForeFront[/TD]
[TD]BGInfo[/TD]
[TD]LogParser 2[/TD]
[TD]LatencyMon[/TD]
[/TR]
</tbody>[/TABLE]











SVFRA1 has ForeFront on it so it has a dependency to SVFRA5 BUT
SVFRA5 also has LatencyMon on it which means that SVFRA1 also has a dependency to SVFRA2 (has LatencyMon on it).
SVFRA2 has Wireshark on it which means SVFRA1 has a dependency with SVFRA3 (has Wireshark on it).

For each server I am trying to get a list of which servers it is dependent on.

I can create a lookup that lists all servers with ForeFront on it but I haven't been able to create this list of expanding dependencies.

Just as a bit of background: when a server is migrated to another datacenter you need to consider which other servers must be migrated with it due to applications on the server relying on databases or file shares from multiple servers.

Does someone know of a way to solve this problem using formulas or even VBA?

Cheers folks,

Ian

 

[Legacy 396030]

Use an index match with multiple criteria. The server name will be the unique ID. If you can post it with the actual rows and columns I might be able to help you with the array formula.

 

[nutti7312]

Hi Billy, thanks for the quick reply. Doesn't the approach you are suggesting require the definition of all the applications that I want to determine the dependency? To me this sounds static and doesn't mean that the multiple criteria I am looking up will expand itself for each server? That's at least how I understood it .

[TABLE="width: 536"]
<colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2161;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4522;" span="2"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2872;" span="2"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2872;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;"> <tbody>[TR]
[TD="width: 61, bgcolor: transparent"]Name[/TD]
[TD="width: 127, bgcolor: transparent"]App[/TD]
[TD="width: 127, bgcolor: transparent"]App[/TD]
[TD="width: 81, bgcolor: transparent"]App[/TD]
[TD="width: 81, bgcolor: transparent"]App[/TD]
[TD="width: 86, bgcolor: transparent"]App[/TD]
[TD="width: 81, bgcolor: transparent"]App[/TD]
[TD="width: 69, bgcolor: transparent"]App[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6033[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6022[/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]ej2010[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6025[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6023[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6042[/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]mse2010ts[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6041[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8005[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]adlwd setup wizard?[/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]msmn3.4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8009[/TD]
[TD="bgcolor: transparent"]adlwd setup wizard?[/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6013[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ws[/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6034[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS1014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]wscp[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS1013[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8055[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8051[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8053[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8057[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8141[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]mssqls2008[/TD]
[TD="bgcolor: transparent"]mssqls2008r2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8142[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]mssqls2008[/TD]
[TD="bgcolor: transparent"]mssqls2008r2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6027[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6028[/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"]ej2010[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS6026[/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"]mes2010[/TD]
[TD="bgcolor: transparent"]emc[/TD]
[TD="bgcolor: transparent"]ems[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8058[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8056[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS1226[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"]lp2.2[/TD]
[TD="bgcolor: transparent"]mssp2010[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8127[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS1119[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]wpc[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS1120[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8196[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8093[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ws[/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]dcmc[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"]wpc[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8001[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]fiddler4[/TD]
[TD="bgcolor: transparent"]ddt2[/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"]msnm3.4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS1179[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]adac[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8011[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ws[/TD]
[TD="bgcolor: transparent"]wpc[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8007[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ddt2[/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"]msnm3.4[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8003[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]fiddler4[/TD]
[TD="bgcolor: transparent"]ddt2[/TD]
[TD="bgcolor: transparent"]msftmgm[/TD]
[TD="bgcolor: transparent"]msfuagam[/TD]
[TD="bgcolor: transparent"]msfuagm[/TD]
[TD="bgcolor: transparent"]msnm3.4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8088[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]msdax2012[/TD]
[TD="bgcolor: transparent"]bottomline[/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]dom[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]mssql2012[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS0234[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS0192[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]r2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS0033[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]smc[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]cpsmr65[/TD]
[TD="bgcolor: transparent"]cpsmr75[/TD]
[TD="bgcolor: transparent"]dosa[/TD]
[TD="bgcolor: transparent"]pg[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8251[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ws[/TD]
[TD="bgcolor: transparent"]addc[/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]cdm[/TD]
[TD="bgcolor: transparent"]dcmc[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS8252[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]addc[/TD]
[TD="bgcolor: transparent"]bacs4[/TD]
[TD="bgcolor: transparent"]dcmc[/TD]
[TD="bgcolor: transparent"]eocm[/TD]
[TD="bgcolor: transparent"]iss[/TD]
[TD="bgcolor: transparent"]lm[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DUS0034[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]sw[/TD]
[TD="bgcolor: transparent"]sw2002[/TD]
[TD="bgcolor: transparent"]swts[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]wpc[/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 396030]

How many cells need to match with column A?

 

[Legacy 396030]

Hello,

This shows when 4 criteria have to match each other and fills in the other data. The yellow area is to be entered:

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"><col style="width: 69.33px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #00CCFF, align: center"]ID[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 1[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 2[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 3[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 4[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 5[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 6[/TD]

[TD="bgcolor: #00CCFF, align: center"]ID[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 1[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 2[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 3[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 4[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 5[/TD]
[TD="bgcolor: #00CCFF, align: center"]Data 6[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]101[/TD]

[TD="bgcolor: #FFFFCC, align: center"]23[/TD]
[TD="bgcolor: #FFFFCC, align: center"]55[/TD]
[TD="bgcolor: #FFFFCC, align: center"]66[/TD]
[TD="bgcolor: #FFFFCC, align: center"]77[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]101[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]99[/TD]

[TD="bgcolor: #FFFFCC, align: center"]22[/TD]
[TD="bgcolor: #FFFFCC, align: center"]44[/TD]
[TD="bgcolor: #FFFFCC, align: center"]50[/TD]
[TD="bgcolor: #FFFFCC, align: center"]60[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]99[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="bgcolor: #FFFFCC"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula M2 {=IFERROR(INDEX(E$2:E$20,MATCH(1,($J2=$B$2:$B$20)*($K2=$C$2:$C$20)*($L2=$D$2:$D$20),0)),"")} N2 {=IFERROR(INDEX(F$2:F$20,MATCH(1,($J2=$B$2:$B$20)*($K2=$C$2:$C$20)*($L2=$D$2:$D$20),0)),"")} O2 {=IFERROR(INDEX(G$2:G$20,MATCH(1,($J2=$B$2:$B$20)*($K2=$C$2:$C$20)*($L2=$D$2:$D$20),0)),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[MickG]

Try this, for results starting "J1".
The results show strings of "Servers" that are dependent on each other.

[COLOR=navy]Sub[/COLOR] MG02Mar08
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, kRay [COLOR=navy]As[/COLOR] Variant, Num [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] p [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("A1").CurrentRegion
[COLOR=navy]Set[/COLOR] Rng = Rng.Offset(1, 1).Resize(Rng.Rows.Count - 1, Rng.Columns.Count - 1)
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Not IsEmpty(Dn) [COLOR=navy]Then[/COLOR]
   [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        Dic.Add Dn.Value, "#" & Dn.Row
   [COLOR=navy]Else[/COLOR]
        Dic(Dn.Value) = Dic(Dn.Value) & "#" & Dn.Row
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]

kRay = Application.Transpose(Dic.items)
ReDim Ray(1 To Dic.Count + 1)
[COLOR=navy]Do[/COLOR] [COLOR=navy]While[/COLOR] Num < Dic.Count
[COLOR=navy]For[/COLOR] n = 1 To UBound(kRay)
   [COLOR=navy]If[/COLOR] kRay(n, 1) <> "" And txt = "" [COLOR=navy]Then[/COLOR]
        txt = kRay(n, 1)
        kRay(n, 1) = "": Num = Num + 1
        c = c + 1
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]If[/COLOR] txt <> "" And Not kRay(n, 1) = "" [COLOR=navy]Then[/COLOR]
            Sp = Split(kRay(n, 1), "#")
        [COLOR=navy]For[/COLOR] p = 1 To UBound(Sp)
            [COLOR=navy]If[/COLOR] InStr(txt, Sp(p)) > 0 [COLOR=navy]Then[/COLOR]
                txt = txt & kRay(n, 1)
                kRay(n, 1) = ""
                Num = Num + 1
                [COLOR=navy]Exit[/COLOR] For
           [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] p
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
Ray(c) = txt: txt = ""
[COLOR=navy]Loop[/COLOR]
Dic.RemoveAll

ReDim nRay(1 To UBound(Ray))
[COLOR=navy]For[/COLOR] n = 1 To c
    Sp = Split(Ray(n), "#")
    [COLOR=navy]For[/COLOR] p = 1 To UBound(Sp)
        [COLOR=navy]If[/COLOR] Not Dic.exists(Sp(p)) [COLOR=navy]Then[/COLOR]
           Dic(Sp(p)) = Empty
          nRay(n) = nRay(n) & IIf(nRay(n) = "", Cells(Sp(p), 1), "," & Cells(Sp(p), 1))
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] p
[COLOR=navy]Next[/COLOR] n
Range("J1").Resize(UBound(Ray)).Value = Application.Transpose(nRay)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick