Excel VBA Introduction Part 39 - Dictionaries

What people said

Recommends user6432984 · 2016-10 · Stack Overflow · on “VBA Excel Single out Duplicates in a single row of multiple rows”

Use Dictionary or Collection when checking for duplicates. This video will show you how. Excel VBA Introduction Part 39 - Dictionaries [this video] . If you run into trouble come back and post you code.

Recommends user6432984 · 2016-11 · Stack Overflow · on “Assistance needed in VBA to extract unique values based on one condition”

You should watch Excel VBA Introduction Part 39 - Dictionaries [this video] . You don't have to set your ranges to nothing. You aren't going to get any noticeable performance increase by doing so.

Recommends user6432984 · 2016-12 · Stack Overflow · on “MS Excel VBA - Match/Index trying to pull in dates to add to a total date duration”

Using Employee ID's as keys to a Dictionary makes it easy to add up the Durations. Most of this code generates a report but this is all you need to get the Durations by Employee ID's For x = 14 To lastRow dicIDs(.Cells(x, "C").Value) = dicIDs(.Cells(x, "C").Value) + .Cells(x, "G").Value - .Cells(x, "F").Value Next Sample Data [LINK] Result [LINK] Sub GetDurations() Application.ScreenUpdating = False Const SOURCE_SHEETNAME As String = "Sheet1" Dim lastRow As Long, x As Long Dim dicIDs As Object Set dicIDs = CreateObject("System.Collections.SortedList") With Worksheets(SOURCE_SHEETNAME) lastRow = .Range("C" & Rows.Count).End(xlUp).Row For x = 14 To lastRow dicIDs(.Cells(x, "C").Value) = dicIDs

Recommends user6432984 · 2017-07 · Stack Overflow · on “Assigning Ranges to Array”

You should watch Excel VBA Introduction [LINK] . Part 25 - Arrays [LINK] and Part 39 - Dictionaries [this video] are the most relevant.

Recommends user6432984 · 2017-09 · Stack Overflow · on “Pasting Data on same column as Date found Looping”

Dictionaries and collections are ideal for comparing lists. You should watch: Excel VBA Introduction Part 39 - Dictionaries [this video] Sub CopyIt() Dim cell As Range, dateRow As Range Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") With Sheet2 For Each cell In .Range("A2", .Range("A" & .Rows.Count).End(xlUp)) If Not dict.Exists(cell.Value2) Then dict.Add cell.Value2, cell Next End With With Sheet3 For Each cell In .Range("A2", .Range("A" & .Rows.Count).End(xlUp)) If dict.Exists(cell.Value2) Then Set dateRow = dict(cell.Value2).EntireRow With cell.EntireRow '*** do the USD bit ***** .Cells(1, 6) = dateRow.Cells(1, 3) '*** copy across usd income *** .Cells(1, 7) = dateRow.Cells

Recommends user6432984 · 2017-09 · Stack Overflow · on “Iterating through row range group data and take an action”

Consider using a Dictionary or Collection, whenever, checking for duplicates. Here I use a Dictionary of Dictionaries to compile lists of Persons by Manager. [LINK] Sub ListManagerList1() Dim cell As Range Dim manager As String, person As String Dim key As Variant Dim dictManagers As Object Set dictManagers = CreateObject("Scripting.Dictionary") For Each cell In Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row) manager = cell.Value person = cell.Offset(0, -1).Value If Not dictManagers.Exists(manager) Then dictManagers.Add manager, CreateObject("Scripting.Dictionary") End If If Not dictManagers(manager).Exists(person) Then dictManagers(manager).Add person, vbNullString End If Next For Each

Recommends user6432984 · 2017-09 · Stack Overflow · on “VBA to import/update range between workbooks (incl. different columns for results)”

Your code seems to have the two sheets backwards. In any case, I based my code on your image. I'm not sure of some of the formatting that you wanted, but you should easily be able to modify the code to suite your needs. I recommend using a Dictionary or collections when comparing unique items in two list (or searching for duplicates). You should watch: Excel VBA Introduction Part 39 - Dictionaries [this video] In many cases you could use constant to reduce the lines of code. I also recommend short variable names and the use of With blocks to reduce code clutter. Ideal you should never have to scroll horizontally to see what a line of code is doing. Sub Import2() Const IMPORTFILENAME = "XXX.xlsx"

Recommends user6432984 · 2017-11 · Stack Overflow · on “VBA Matching 2 sets of data”

You should use a Collection. A Scripting Dictionary is the collection of choice for this tasks. Watch: Excel VBA Introduction Part 39 - Dictionaries [this video]

Recommends TinMan · 2018-11 · Stack Overflow · on “Check one list against another list (VBA) using LBound and UBound . Or any way that works”

It is considered a best practice to add Option Explicit to the top of the code modules and declare a variables with the correct datatypes. Dim LastRow As Long, LastRowPO As Long Use & not And when concatenating strings. OpenPOList = OpenPO.Range("A2:A" And LastRowPO).Value LastRowPO is being used before its value is set. LastRowPO = OpenPO.Range("A" & Rows.Count).End(xlUp).row OpenPOList = OpenPO.Range("A2:A" & LastRowPO).Value Use Range.Find to search a group of cells not a single cell. If Not cell.Find(OpenPOList(i)) Is Nothing Then Using a Scripting.Dictionary to match unique values is vastly faster then using nested loops. Watch: Excel VBA Introduction Part 39 - Dictionaries [this video] . Yo

Recommends TinMan · 2019-08 · Stack Overflow · on “Declaring Arrays Dynamically in VBA”

You should watch: Excel VBA Introduction Part 39 - Dictionaries [this video] and read Excel VBA Dictionary – A Complete Guide [LINK]