Efficient way to use index match or alternative method to insert values dynamically into worksheet

[ShaunH]

Hi everyone. I am stuck.
I need to fill values into columns using VBA in much the same way as INDEX and MATCH does, but using VBA. I have everything turned off already.

My data uses VBA to copy select columns from one sheet to a new sheet "Analyse" in Columns A to G. That works perfectly. The rows are dynamic as the source data changes occasionally.

Here is the problem: I need to lookup from a table on one sheet "GroupData" Column 1 and return column 2 and 3 in H and I,

using entries in column D as my MATCH.​

Then there is another lookup in a another sheet and table "SCPvalue", that looks at column 1 and returns column 2,

using entries in "Analyse" column E as my MATCH.​

All columns have headers as they are tables

It works fine if the formula is in the cell, but that is easily broken and has to remain even if the sheet has no values as that is something that does happen, which has it's own problems.
I have other code that also works by using VBA, but it is slow and still incomplete.

I clear the sheet from row 2 down before filling the new data. Data can be anything from 1 to 2000 rows of data.
The lookup is applied after the data is filled. "GroupData" is a Customer, Holding Company and Group list of 7000 rows, which can grow beyond 7000.
The other lookup table is only about 10 rows and two columns, dimSCP and SCPvalue, unlikely to change, but if it does, I need to make provision for it.

This is the slow incomplete VBA. It takes about 10 seconds to run through the customer database. The othe part is incomplete.

Sub FillValues()
Dim strThisFile As String
strThisFile = "TestFile.xlsm"

Dim strSheetA As String
strSheetA = "Analyse"
Dim strSheetG As String
strSheetG = "GroupData"
Dim strSheetS As String
strSheetS = "SCPvalues"

Dim intCounterA_Y As Integer
intCounterA_Y = 2
Dim intCounterG_Y As Integer
intCounterG_Y = 2
Dim intCounterS_Y As Integer
intCounterS_Y = 2

While Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 1).Value <> "" ' Loop through Anlayse
intCounterG_Y = 2
While Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 1).Value <> "" ' Loop through GroupData
If Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 4).Value = Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 1).Value Then
Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 8).Value = Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 3).Value
Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 9).Value = Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 2).Value
End If
intCounterG_Y = intCounterG_Y + 1
Wend




intCounterA_Y = intCounterA_Y + 1
    Wend

End Sub

The code only runs once after the data has been copied to "Analyse" sheet. It does not need to watch for changes to this sheet.
I have seen some rather shortened versions of VBA, that claim to be much faster, I cannot say. I'm still learning though.

Please help to either rewrite in a more efficient way, or point me in the right direction?
Also I would appreciate an explanation of the steps to help me learn if possible.

Thank you in advance.

 

[FatBoyClam]

oops, accidentally posted without finishing the post

*hold please caller*

 

[ShaunH]

The method you indicated above seems to insert the formulas into those cells, not the resultant values. Can the lookup not be done in memory and inserted? I have tried different approaches but all are slow.
Even the copying of the columns from my sample data from Temp to Analyse. I think the pasting to the Temp sheet is fast and very happy with that since there's some renaming going on too. At the end of the Analyse, since I don't need it after it's taken the required columns. When I have new data, I paste with the buttons as it's from Windows clipboard, then once thats done the Analyse button needs to be pressed. I just don't know what will happen if the data is incorrect in the clipboard, that's why I'm looking for specific column headers, then copy those to the Analyse sheet where all the magic happens. The pivot tables just sumurize what I need for a quick view.

 

[ShaunH]

Yes, cleared after Analyse is pressed, but would also work before pasting new data. I don't mind either way.

 

[FatBoyClam]

The method you indicated above seems to insert the formulas into those cells, not the resultant values. Can the lookup not be done in memory and inserted? I have tried different approaches but all are slow.
Even the copying of the columns from my sample data from Temp to Analyse. I think the pasting to the Temp sheet is fast and very happy with that since there's some renaming going on too. At the end of the Analyse, since I don't need it after it's taken the required columns. When I have new data, I paste with the buttons as it's from Windows clipboard, then once thats done the Analyse button needs to be pressed. I just don't know what will happen if the data is incorrect in the clipboard, that's why I'm looking for specific column headers, then copy those to the Analyse sheet where all the magic happens. The pivot tables just sumurize what I need for a quick view.

When you say "The method indicated above", do you mean allowing excel to auto fill the columns with the formulas?

I'm not clear what you have against using the formulas. Generally speaking, the fastest and most efficient method is to use the built in formulas and functionality.

What I'm certain is the massive issue is the way you're pasting from Temp to Analyse - you're copying the entire column, not just the rows with data. Because you're pasting it to a table,this means the table is extended all the way to the bottom of the tab (1 million and a few rows). So the formulas are being extended to a million rows, instead of a couple of thousand.

I think the best change we can make is to that element.

I'm not sure which, if any of the columns can be guaranteed to have a value in every row, so for safety, I'm going to check for the last value in each one, then fetch that many rows from each

First, lets clear the Analyse table in a cleaner way;

Replace this;

Sub ClearAnalyseTable()
    
    TurnOffStuff
    Sheets("Analyse").Rows("2:" & Sheets("Analyse").Rows.Count).ClearContents
    TurnOnStuff
End Sub

with this;

Sub ClearAnalyseTable()
    
    Dim tbl As ListObject
    
    Set tbl = Sheets("Analyse").ListObjects("AnalyseTable")
    
    TurnOffStuff
    
    If Not tbl.DataBodyRange Is Nothing Then
        tbl.DataBodyRange.Delete
    End If
    
    
    TurnOnStuff
End Sub

In your Analyse function, replace the following code block;

Sub Analyse() 'Excel VBA to move Columns based on criteria

TurnOffStuff 'Speed up Excel
ThisWorkbook.Sheets("Temp").Visible = True

Dim ar As Variant, i As Integer, rng As Range

On Error GoTo ErrorHandler2

'Set the Array Values
ar = Array("CRN", "Customer Name", "Circuit/Equip ID", "Extension", "SLA", "Service Type", "Status")
For i = 0 To UBound(ar) 'Loop through the Array
    Set rng = Sheets("Temp").Rows(1).Find(ar(i), lookat:=xlWhole, MatchCase:=False)
    If Not rng Is Nothing Then rng.EntireColumn.Copy Sheets("Analyse").Cells(1, i + 1) 'Copy and Paste to new sheet.
Next i

with this;

Sub Analyse() 'Excel VBA to move Columns based on criteria
   
ClearAnalyseTable 'Lets make sure the analyse table is clear before filling it

TurnOffStuff 'Speed up Excel
ThisWorkbook.Sheets("Temp").Visible = True

Dim ar As Variant, i As Integer, rng As Range, r As Long

On Error GoTo ErrorHandler2

'Set the Array Values
ar = Array("CRN", "Customer Name", "Circuit/Equip ID", "Extension", "SLA", "Service Type", "Status")
For i = 0 To UBound(ar) 'Loop through the Array
    Set rng = Sheets("Temp").Rows(1).Find(ar(i), lookat:=xlWhole, MatchCase:=False)
    If Not rng Is Nothing Then
        If rng.Offset(Rows.Count - 1).End(xlUp).Row > r Then r = rng.Offset(Rows.Count - 1).End(xlUp).Row
    End If
Next i

For i = 0 To UBound(ar) 'Loop through the Array
    Set rng = Sheets("Temp").Rows(1).Find(ar(i), lookat:=xlWhole, MatchCase:=False)
    If Not rng Is Nothing Then rng.Resize(r, 1).Copy Sheets("Analyse").Cells(1, i + 1) 'Copy and Paste to new sheet.
Next i

then comment out the FillValues line (as the table will autofill the formulas)

I've just created a 7000 row set of Company data and 1995 row data set on the Temp tab with Company names randomly selected from those 7000, then used the above adaption to your code to copy it into the Analyse table. It took 1 second.

N.B I had to fiddle with the table on Analyse, as the columns are not in the same order as they are after the code has run - personally, if you're going to fetch a column at a time, I'd read the column headers from the target table, look that up in the source table and fetch that way.
Your Analyse function has the TurnOnStuff call after both Exit Sub lines, so it never hits, and left the workbook on Manual Calculation. I copied it back up above both of the Exit Sub statements.
I also had to comment out the "RefreshPivot" routine calls, as that function/sub does not exist in the workbook you sent, but that shouldn't really change the overall time taken, as Pivot tables are very efficient

 

[ShaunH]

Thank you.
The original workbook, was a problem, due to it being used by many people, and columns can be arranged as per the users choice in the App that the source data come's from. I cannot pick out the columns in an array in memory as I don't know how or if possible. I would then just paste the columns I needed in the order that I need them to appear, in the Analyse sheet, since it's the only sheet that holds the data connected to everything I'll be looking at. On last refresh I had 451 rows of data in one of my sources. Another had 45, so the data does range.
I have read many posts and tried various itterations to get this workbook to work; It needed to be fast, easy to amend if an additional column is required, include notifications to the user and not have to go to each user to rearrange their columns in the app, although this would be used mostly by management.
The form on the Search Tab was pretty easy to get to work, but I think it needs work as for some reason it broke when I recreated it in the sample and I don't know why. I'll look at that shortly.
Sorry about the RefreshPivot, forgot to add it to the workbook.

I'm about to insert your code and give it a go. Will feedback shortly.

 

[ShaunH]

OK, still a problem. Do I include an IFNA to the formula? Also, SCPvalue may be null in which case nothing happens.
I have entered the formula as you specified, replaced the blocks of code, but when I paste and then Analyse I just get #N/A all the way to last row of data.
Can I protect the formula in H, I and J from accidental deletion by user?
My sample code the Customer Name is supposed to be D.

 

[ShaunH]

Got the popup sorted. But have another problem with the formulas.

The formulas entered as: From left to right in the columns above. The SLA column is most likely to be empty.
=INDEX(CustGroup[dimGroup], MATCH([@[Customer Name]], CustGroup[dimCust],0))
=INDEX(CustGroup[dimHold], MATCH([@[Customer Name]], CustGroup[dimCust],0))
=INDEX(SCPvalue[dimSCPvalue], MATCH([@SLA], SCPvalue[dimSCP], 0))
But for the one's that are not it does also shows #N/A

 

[ShaunH]

Other than the problem showing #N/A, it works perfectly and is super fast compared to my attempt. Much appreciated.

 

[ShaunH]

OK my bad, I also didn't move my TurnStuffOn to the right place. Also sorted the #N/A with an IFNA inclusion. Most excellent work sir. Is there a way to protect those formulas though?

 

[FatBoyClam]

OK my bad, I also didn't move my TurnStuffOn to the right place. Also sorted the #N/A with an IFNA inclusion. Most excellent work sir. Is there a way to protect those formulas though?

The sheet the formulas is on is only supposed to be amended by the Analyse code, is it not?

If so, then password protect it. In your Analyse routine, you can unprotect the sheet at the start, and then protect it again at the end.

Note: excel password protection is only really a safeguard against accidental or inadvertent user breakage. Malicious user breakage is largely indefensible in Excel (particularly when the answer to 95% of questions is a single Google away, and the other 5% can usually be gained by asking a question on a forum ?)