Efficient way to double loop through 2 dict

[john316swan]

I have a list of unique IDs in one sheet, and a table that includes duplicated list of unique IDs and another column that contains scholarship names. I am trying to find the most efficient way to return all the unique scholarship names.

For example, in Sheets(1), column A, I have the list of id's

Column A (StudentID)
1
2
3
etc.

Then in Sheets(7), column A there is a list of duplicated IDs for every scholarship a student has, and in column D list each scholarship.

Column A (StudentID)	Column D (AwardType)
1	Presidential Scholar
1	Ministry Worker
1	Music
2	Presidential Scholar
2	Church Partner
2	Theatre
3	Provost Scholar
3	Men Basketball
3	Christian HS
3	Music

Ideally I want a unique/unduplicated list of Column D (AwardType) so that I can transpose and export them as a column header. There are so many awards to lookup, that I want to limit it to the students in sheet 1 column A. Hope this makes sense, and thank you in advance for helping this rookie out.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Thanks for that, how about

Sub Johnswan()
   Dim Dic As Object, Dic2 As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Set Dic2 = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("Sheet2")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Dic2(Cl.Offset(, 3).Value) = Empty
      Next Cl
   End With
  Sheets("Sheet1").Range("B1").Resize(, Dic2.Count).Value = Dic2.Keys
End Sub

This will put the headers into sheet1 starting in B1

 

[john316swan]

Thank you @Fluff...essentially what I was looking for. Helped me find manual data input errors and cut load time.

 

[Fluff]

You're welcome & thanks for the feedback.