XLOOKUP/VLOOKUP taking too long to fill blank cells

[SkywardPalm]

I am trying to fill the blank within a table with found data from a second table using VBA. It works, but after the first column is found and filled with data the next columns take much longer (about a minute each) to fill. If there is a better method please let me know, I have tried VLOOKUP/XLOOKUP/INDEX&MATCH and all seem to be slower than they could be. I'm working with 300k rows in each table (max), but it's meant to scale to any size table.
Here is the snippet of code I am working with..

For Each rColHead In rActiveColHeaders
    Set foundCol = rReferenceColHeaders.Find(rColHead.Value)
            
    If Not foundCol = "ID" Then
        If Not foundCol Is Nothing Then
            'Takes too long after first found Header..
            With wksTempData.ListObjects("tempTable1").ListColumns(rColHead.Value).DataBodyRange
                On Error Resume Next 'ignore error if no blanks
                Set blankRng = .SpecialCells(xlCellTypeBlanks)

                If Not blankRng Is Nothing Then blankRng.Value = _
                    "=XLOOKUP([@ID],tempTable2[ID],tempTable2[" & rColHead & "],0)"
                ' If Not blankRng Is Nothing Then blankRng.Value = _
                    "=INDEX(tempTable2,MATCH([@ID],tempTable2[ID],0),MATCH(""" & rColHead & """,tempTable2[#Headers],0))"
                ' If Not blankRng Is Nothing Then blankRng.Value = _
                    "=VLOOKUP([@ID],tempTable2,MATCH(""" & rColHead & """,tempTable2[#Headers],0),0)"
            End With
        Else
            Debug.Print rColHead.Value & " was not found. "
        End If
    End If
Next rColHead

 

[Oaktree]

The simplest answer might just be to turn calculations to manual before running your code and turn calculations back to automatic once your code has finished its loop.

That said, if you're already doing that or find that it's *still* too slow, the way to have this run a LOT faster will require a different approach.

In order of speed, referencing a dictionary object is faster than looping through a VBA array which, in turn, is a LOT faster than looping through cells.

As a general construct, it would look something like this:

1) Load tempTable2 to a VBA array. See See Arrays And Ranges In VBA if you're not sure how to do that.

2) Loop through that VBA array and set up a dictionary object for the input/output pairs (similar to a lookup table). Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery has an excellent guide to VBA dictionaries if you're not familiar with them. Note that I always code with late binding (covered in that link) if people other than just me would potentially use the code, as it saves you from inevitable questions about error messages from unchecked library references that are likely to happen with early binding.

3) Load the entire range of cells you're trying to populate (both those with values and those with blanks) to a separate VBA array (similar to step #1)

4) Loop through the VBA array from #3 and replace null values with the value retrieved from the dictionary you set up in #1

5) Paste the VBA array from #4 back to the range (also covered in Chip Pearson's link from #1)

There is a lot to review there; but, if you were to go that approach, you could very well see performance improvements from 20+ minutes of recalculation churn to just a few seconds.

 

[SkywardPalm]

The simplest answer might just be to turn calculations to manual before running your code and turn calculations back to automatic once your code has finished its loop.

That said, if you're already doing that or find that it's *still* too slow, the way to have this run a LOT faster will require a different approach.

In order of speed, referencing a dictionary object is faster than looping through a VBA array which, in turn, is a LOT faster than looping through cells.

As a general construct, it would look something like this:

1) Load tempTable2 to a VBA array. See See Arrays And Ranges In VBA if you're not sure how to do that.

2) Loop through that VBA array and set up a dictionary object for the input/output pairs (similar to a lookup table). Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery has an excellent guide to VBA dictionaries if you're not familiar with them. Note that I always code with late binding (covered in that link) if people other than just me would potentially use the code, as it saves you from inevitable questions about error messages from unchecked library references that are likely to happen with early binding.

3) Load the entire range of cells you're trying to populate (both those with values and those with blanks) to a separate VBA array (similar to step #1)

4) Loop through the VBA array from #3 and replace null values with the value retrieved from the dictionary you set up in #1

5) Paste the VBA array from #4 back to the range (also covered in Chip Pearson's link from #1)

There is a lot to review there; but, if you were to go that approach, you could very well see performance improvements from 20+ minutes of recalculation churn to just a few seconds.

I have tried turning calculations to manual, for some reason it takes longer to process when I do that. I'll look into the method you provided and get back to you, thanks for quick response!

 

[SkywardPalm]

The simplest answer might just be to turn calculations to manual before running your code and turn calculations back to automatic once your code has finished its loop.

That said, if you're already doing that or find that it's *still* too slow, the way to have this run a LOT faster will require a different approach.

In order of speed, referencing a dictionary object is faster than looping through a VBA array which, in turn, is a LOT faster than looping through cells.

As a general construct, it would look something like this:

1) Load tempTable2 to a VBA array. See See Arrays And Ranges In VBA if you're not sure how to do that.

2) Loop through that VBA array and set up a dictionary object for the input/output pairs (similar to a lookup table). Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery has an excellent guide to VBA dictionaries if you're not familiar with them. Note that I always code with late binding (covered in that link) if people other than just me would potentially use the code, as it saves you from inevitable questions about error messages from unchecked library references that are likely to happen with early binding.

3) Load the entire range of cells you're trying to populate (both those with values and those with blanks) to a separate VBA array (similar to step #1)

4) Loop through the VBA array from #3 and replace null values with the value retrieved from the dictionary you set up in #1

5) Paste the VBA array from #4 back to the range (also covered in Chip Pearson's link from #1)

There is a lot to review there; but, if you were to go that approach, you could very well see performance improvements from 20+ minutes of recalculation churn to just a few seconds.

I'm having trouble setting up the dictionary with multiple Items per Key using my tempTable2. It is a reference table that has dynamic column headers (that change in name and index/location), so creating a Class to fill the dictionary is puzzling me. Would I have to do it in key/item pairs, looping through the column headers individually as I was before? (I also had trouble finding information on [Step 4] how to pull dictionary Items into the nulls of the Array I wish to fill with missing data)

 

[Oaktree]

Two options:

1) Different dictionary for each of your source fields. If you have a handful of them, this could be fine. But, it would be cumbersome if you have 50+.
2) One dictionary where the dynamic header becomes part of the key. For example, dict_tempTable2(id & "|" & column_name) = column_value. Then, you would lookup the similar concatenation of the ID and the dynamic column name against your dictionary.