Nested Dictionary?

anderb16

New Member
Joined
Nov 17, 2014
Messages
12
I have a set of data: Domains; Users; Machines

I have a task that requires me to check to see if a domain user has logged into a particular machine in the past.

There are unlimited domains, an undefined number of users, and the machine name could potentially change at any time. The tool has to be able to potentially check hundreds to new user/machine combinations at a time. The reason for separating by domain is because the user/machine reports have to be sent to each domain rep individually.

What I thought I was going to do was create a dictionary of domains, a dictionary of users, and a collection of machines, this way I would be able to use the .exists property instead of looping through collections or arrays which is the current problem with my previous code (time to run the tool).

<tab>
Domain
User
Machine
Machine
User
Machine
Machine
Domain
User
Machine

<tbody>
</tbody>



But I found that when it was time to add a new user to an existing domain, I could not - or I was simply doing it incorrectly.

Currently, the data is set up as such:
UserMachineDomain

<tbody>
</tbody>

I'm not including my code because it's completely non-functional and would only add to confusion, but I'd be happy to include more examples if needed. Any suggestions at making this work?</tab>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,056
.
I have a task that requires me to check to see if a domain user has logged into a particular machine in the past.

Is that data already stored somewhere or do you need to create the database now ?


If you have a means, when the user 'logs in' to a machine, to also have his/her information logged to a sheet, you can easily search that sheet/database ...
separate the data on that sheet by User or Domain or Machine, etc.

???
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
From what you have posted, it looks like you have multiple blanks cells.
Is this correct, or is your data more like

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Domain</td><td style=";">User</td><td style=";">Machine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Domain 1</td><td style=";">User 1</td><td style=";">Mc 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Domain 1</td><td style=";">User 1</td><td style=";">Mc 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Domain 1</td><td style=";">User 1</td><td style=";">Mc 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Domain 1</td><td style=";">User 2</td><td style=";">Mc 4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Domain 1</td><td style=";">User 2</td><td style=";">Mc 5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Domain 1</td><td style=";">User 2</td><td style=";">Mc 6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Domain 1</td><td style=";">User 3</td><td style=";">Mc 7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Domain 2</td><td style=";">User 4</td><td style=";">Mc 8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Domain 2</td><td style=";">User 4</td><td style=";">Mc 9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Domain 2</td><td style=";">User 5</td><td style=";">Mc 10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Domain 2</td><td style=";">User 5</td><td style=";">Mc 11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Domain 3</td><td style=";">User 6</td><td style=";">Mc 12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Domain 3</td><td style=";">User 6</td><td style=";">Mc 13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Domain 3</td><td style=";">User 6</td><td style=";">Mc 14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Domain 3</td><td style=";">User 7</td><td style=";">Mc 15</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

Where every row has the relevant information (the data does not need to be sorted)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
What exactly do you need to do with the data?

Is there any other data than domain/machine/user, e.g. date/time?
 

anderb16

New Member
Joined
Nov 17, 2014
Messages
12

ADVERTISEMENT

Thank you all who replied!


Is that data already stored somewhere or do you need to create the database now ?

The information already exists on a sheet.

From what you have posted, it looks like you have multiple blanks cells.
Is this correct, or is your data more like

Excel 2013/2016
A B C
1 Domain User Machine
2 Domain 1 User 1 Mc 1
3 Domain 1 User 1 Mc 2
4 Domain 1 User 1 Mc 3
5 Domain 1 User 2 Mc 4
6 Domain 1 User 2 Mc 5
7 Domain 1 User 2 Mc 6
8 Domain 1 User 3 Mc 7
9 Domain 2 User 4 Mc 8
10 Domain 2 User 4 Mc 9
11 Domain 2 User 5 Mc 10
12 Domain 2 User 5 Mc 11
13 Domain 3 User 6 Mc 12
14 Domain 3 User 6 Mc 13
15 Domain 3 User 6 Mc 14
16 Domain 3 User 7 Mc 15
Sheet2

Where every row has the relevant information (the data does not need to be sorted)

No blank cells, your example is spot on!

What exactly do you need to do with the data?

Is there any other data than domain/machine/user, e.g. date/time?

Information pertaining to new logins by users on different machines is being recorded to create a report and forwarded to domain managers. There is no other relevent information.
I currently have a script that loops through and compares new data to each data set (compare domains/compare users/compare machines) but when applying that routine to a large data set, it is too slow, which is why I was hoping to be able to use the .exists property of a dictionary or another faster routine.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
Code:
Sub anderb16()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   
   For i = 2 To UBound(Ary)
      If Not Dic.Exists(Ary(i, 1)) Then Dic.Add Ary(i, 1), CreateObject("scripting.dictionary"): Dic(Ary(i, 1)).CompareMode = 1
      If Not Dic(Ary(i, 1)).Exists(Ary(i, 2)) Then Dic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary"): Dic(Ary(i, 1))(Ary(i, 2)).CompareMode = 1
      Dic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)) = Empty
   Next i
   
   With Sheets("Sheet2").Range("G1").CurrentRegion
      Ary = .Resize(, .Columns.Count + 1).Value2
   End With
   For i = 2 To UBound(Ary)
      If Dic.Exists(Ary(i, 1)) Then
         If Dic(Ary(i, 1)).Exists(Ary(i, 2)) Then
            If Dic(Ary(i, 1))(Ary(i, 2)).Exists(Ary(i, 3)) Then
               Ary(i, 4) = "Exists"
            End If
         End If
      End If
   Next i
   Sheets("Sheet2").Range("G1").Resize(UBound(Ary), 4).Value = Ary
End Sub
With the main data as shown in post#3 and the data to check like

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Domain</td><td style=";">User</td><td style=";">Machine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Domain 3</td><td style=";">user 6</td><td style=";">mc 14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Domain 1</td><td style=";">User 2</td><td style=";">Mc 1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

anderb16

New Member
Joined
Nov 17, 2014
Messages
12

ADVERTISEMENT

Maybe something like
Code:
Sub anderb16()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   
   For i = 2 To UBound(Ary)
      If Not Dic.Exists(Ary(i, 1)) Then Dic.Add Ary(i, 1), CreateObject("scripting.dictionary"): Dic(Ary(i, 1)).CompareMode = 1
      If Not Dic(Ary(i, 1)).Exists(Ary(i, 2)) Then Dic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary"): Dic(Ary(i, 1))(Ary(i, 2)).CompareMode = 1
      Dic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)) = Empty
   Next i
   
   With Sheets("Sheet2").Range("G1").CurrentRegion
      Ary = .Resize(, .Columns.Count + 1).Value2
   End With
   For i = 2 To UBound(Ary)
      If Dic.Exists(Ary(i, 1)) Then
         If Dic(Ary(i, 1)).Exists(Ary(i, 2)) Then
            If Dic(Ary(i, 1))(Ary(i, 2)).Exists(Ary(i, 3)) Then
               Ary(i, 4) = "Exists"
            End If
         End If
      End If
   Next i
   Sheets("Sheet2").Range("G1").Resize(UBound(Ary), 4).Value = Ary
End Sub
With the main data as shown in post#3 and the data to check like

Excel 2013/2016
GHI
1DomainUserMachine
2Domain 3user 6mc 14
3Domain 1User 2Mc 1

<tbody>
</tbody>
Sheet2

This is an excellent start and appears to be easy to adapt to my needs. Particularly the following:

Code:
Sub anderb16()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   
   For i = 2 To UBound(Ary)
      If Not Dic.Exists(Ary(i, 1)) Then Dic.Add Ary(i, 1), CreateObject("scripting.dictionary"): Dic(Ary(i, 1)).CompareMode = 1
      If Not Dic(Ary(i, 1)).Exists(Ary(i, 2)) Then Dic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary"): Dic(Ary(i, 1))(Ary(i, 2)).CompareMode = 1
      Dic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)) = Empty
   Next i

I'm currently working with it to see if I can take it to the next step.

The tl;dr:
In my application, I have a userform.textbox where new data is input. It is typically input in the form of:
Code:
[INDENT]user1@domain1.com:machine1[/INDENT]
[INDENT]user2@domain2.com:machine2[/INDENT]

There is another textbox on the same userform where the deliminator, in this case ":", is set and a commandbutton to import the data. My current routine assigns the input from the textbox to a string and then split into an array by vbcrlf to be compared to the existing dataset - looking for new values.

First, the domain is identified in the string "domain1.com" and then looks to see if it exists in the existing set. Then the user "user1" and checked to see if it exists in that domain. Then the "machine" to see if that user has previously logged into that machine.

If the information has not been seen before, it adds it to the first empty row at the end of the previous data set for the next use.


Goal:
So, my what I'm looking to do is compare a string "user@domain:machine" to the dataset.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
In that case there is no need for all the dictionaries, you can use something like this instead
Code:
Option Explicit
Dim UfDic As Object

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If UfDic.Exists(Me.TextBox1.Value) Then
      MsgBox "It already exists"
   Else
      'copy to the sheet
   End If
End Sub

Private Sub UserForm_Click()
   Dim Ary As Variant
   Dim i As Long
   Dim x As String
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   Set UfDic = CreateObject("scripting.dictionary")
   UfDic.CompareMode = 1
   
   For i = 2 To UBound(Ary)
      x = Ary(i, 1) & "@" & Ary(i, 2) & ":" & Ary(i, 3)
      UfDic.Item(x) = Empty
   Next i
End Sub
 

anderb16

New Member
Joined
Nov 17, 2014
Messages
12
In that case there is no need for all the dictionaries, you can use something like this instead
Code:
Option Explicit
Dim UfDic As Object

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If UfDic.Exists(Me.TextBox1.Value) Then
      MsgBox "It already exists"
   Else
      'copy to the sheet
   End If
End Sub

Private Sub UserForm_Click()
   Dim Ary As Variant
   Dim i As Long
   Dim x As String
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   Set UfDic = CreateObject("scripting.dictionary")
   UfDic.CompareMode = 1
   
   For i = 2 To UBound(Ary)
      x = Ary(i, 1) & "@" & Ary(i, 2) & ":" & Ary(i, 3)
      UfDic.Item(x) = Empty
   Next i
End Sub

I'm trying it out and I'll report back.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,535
Members
418,138
Latest member
agnesegras

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
Top