Excel Guides

Synchronizing Lists in Excel

Assuming you have two lists in Excel that you want to keep synchronized, there are a few different ways you can go about doing this. One option is to use a simple VBA macro to compare the lists and make any necessary updates. Another option is to use Excel's built-in "Consolidate" feature. Let's take a look at both of these methods in more detail.

Method 1: Use a VBA Macro

Using a VBA macro to synchronize two lists is relatively simple. The first thing you need to do is add a reference to the "Microsoft Scripting Runtime" library. This can be done by going to the Tools menu, selecting References, and then checking the box next to "Microsoft Scripting Runtime".

Once you've done this, you can insert the following code into a new module:

Sub SyncLists()

  Dim dict As New Scripting.Dictionary
  Dim key As Variant
  Dim i As Long

  'Add items from first list to dictionary
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    key = Range("A" & i).Value
    If Not dict.Exists(key) Then dict.Add key, 1
  Next i

  'Delete items from second list that aren't in dictionary
  For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    key = Range("B" & i).Value
    If Not dict.Exists(key) Then Range("B" & i).EntireRow.Delete
  Next i

  'Add items from second list to dictionary
  For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
    key = Range("B" & i).Value
    If Not dict.Exists(key) Then dict.Add key, 1
  Next i

  'Delete items from first list that aren't in dictionary  
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    key = Range("A" & i).Value     If Not dict.Exists(key) Then Range("A" & i).EntireRow.Delete     Next i     End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.