Lookup Multiple Entries AND Expand Lookup to Include Dependencies

N

nutti7312

New Member
Joined
Mar 1, 2017
Messages
2
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.

ServernameApplicationApplicationApplicationApplication
SVFRA1ForeFrontMS SQLNetBackup
SVFRA2WinPcapWiresharkMS SQLLatencyMon
SVFRA3EmulexPageGateWiresharkSolarwinds
SVFRA4SharePointCMTrace
SVFRA5ForeFrontBGInfoLogParser 2LatencyMon

<tbody>
</tbody>











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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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.
 
Upvote 0
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 :).

NameAppAppAppAppAppAppApp
DUS6033adacmes2010emcems
DUS6022adacej2010mes2010emcems
DUS6025
DUS6023
DUS6014
DUS6042adacmse2010tsmes2010emcems
DUS6041
DUS8005adlwd setup wizard?msftmgmmsfuagammsfuagmmsmn3.4
DUS8009adlwd setup wizard?msftmgmmsfuagammsfuagmbacs4
DUS6013wsadacmes2010emcems
DUS6034adacmes2010emcems
DUS1014wscp
DUS1013
DUS8055
DUS8051
DUS8053
DUS8057
DUS8141mssqls2008mssqls2008r2
DUS8142mssqls2008mssqls2008r2
DUS6027
DUS6028adaceocmej2010mes2010emcems
DUS6026adaceocmmes2010emcems
DUS8058bacs4eocm
DUS8056bacs4eocm
DUS1226bacs4eocmlp2.2mssp2010
DUS8127bacs4eocm
DUS1119msftmgmmsfuagammsfuagmwpc
DUS1120msftmgmmsfuagammsfuagm
DUS8196
DUS8093wsbacs4dcmceocmwpc
DUS8001fiddler4ddt2msftmgmmsfuagammsfuagmmsnm3.4
DUS1179adaceocm
DUS8011wswpc
DUS8007ddt2msftmgmmsfuagammsfuagmmsnm3.4
DUS8003fiddler4ddt2msftmgmmsfuagammsfuagmmsnm3.4
DUS8088msdax2012bottomlinebacs4dommssql2012
DUS0234
DUS0192r2
DUS0033smccpsmr65cpsmr75dosapg
DUS8251wsaddcbacs4cdmdcmceocm
DUS8252addcbacs4dcmceocmisslm
DUS0034swsw2002swtswpc
<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> </tbody>
 
Upvote 0
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

ABCDEFGHIJKLMNO
1IDData 1Data 2Data 3Data 4Data 5Data 6 IDData 1Data 2Data 3Data 4Data 5Data 6
2235566778899101 235566778899101
322445060708099 22445060708099
4 000
5 000
6 000
7 000
8 000
9 000
10 000

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
Try this, for results starting "J1".
The results show strings of "Servers" that are dependent on each other.

Code: 
[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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
488
MissDB101
M
B
  • Question
Sum results from multiple lookups in 1 formula
2
Replies
10
Views
709
Fluff
Fluff
U
  • Locked
  • Question
Power BI - multiple table lookup and count
Replies
1
Views
131
Fluff
Fluff
B
  • Question
Help on a project utilizing formulas, lookups, and sorting
Replies
1
Views
649
bh24524
B
R
  • Question
3D dynamic lookup with duplicates across multiple sheets
Replies
0
Views
1K
Rich N
R

Forum statistics

Threads
1,214,918
Messages
6,122,255
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top