Need For Speed - Something Faster Than VLookup 1

[DrBowes]

Hi All

I have about 9000 rows of data that refresh from an external source.

After a refresh I need to add another column to the data. If one column in the data is equal to one of about 20 values then the new column has the value true, otherwise it is false.

What is the quickest way of checking if each row is true or false, as I want to automate recalcalculating the new column when the user refreshes? I want this to happen as quickly as possible becuase the refresh takes long enough already...

I have tried using vlookup (dragging it down the rows and then pasting values), and also checking to see if each item in the row is in a collection containing the 20 items, but I wonder if anybody can think of something faster. e.g. a smart use of the find method, hash tables, better code for the use of the collection (at the moment I am using "for each item in mycollection&quot.

Cheers

 

[Zathras]

You neglected to say what your values look like, but assuming you have three digit integers, then the following code runs in under a second:
[blue]

Const TRUE_VALUES = ".233.731.789.112.223.334.445.668.224.124.238.883.246.346.111.222.333.888.999.444."

Sub SetTrueOrFalse()
Dim r As Range
Dim c As Range
  Application.ScreenUpdating = False
  Set r = Range("A1:A9000")
  r.Offset(0, 1).Formula = False
  For Each c In r
    With c
      If InStr(TRUE_VALUES, "." & .Value & ".") Then
        .Offset(0, 1) = True
      End If
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

[/color]
If you can't adapt the concept to your actual data, post some examples of the type of data you are searching thru and the 20 values you are searching for.

 

[luceze]

Interesting code Zathras. Do you have to declare all of your true values explicitly in your constant statement?

Eric

 

[Zathras]

Not sure I understand your question. How else would one know which values correspond to "TRUE" and which ones don't? I suppose you could use a variable, but in this case what's the difference?

Of course, if the exact set of about 20 values varies from run to run then certainly you would need a variable. (Or alter the code every time which kind of slows things down and that would be contrary to the original request.)

 

[DrBowes]

Thanks and have a star - I look forward to testing this for speed when I am back in the office but I'm sure it will be much faster.

I was using six digit numbers held in cells that I want to make esily updatable, but can easily build the constant by concatenating the cells with dots between them at the start of the procedure.

I almost thought of this, but didn't think of using the dots so was concerned about other six digit numbers qualifying by accident.

 

[stitch]

Hi,

Is there anyway to modify Zathras's code to use cells in a seperate worksheet rather than a fixed array of values?

For example I have a worksheet called "Values" with a list of email address stored in A1:A100, and a worksheet called "Results" where my data is stored. I would like to look at each record in the "Results" worksheet and see if the email address appears in the "Values" worksheet. If it doesn't then highlight the row and enter "No" in a column.

Many thanks,
Richard.

 

[Gavona]

A different approach would be to make use of advanced filters. I'd love to know how it compares with Zathras's speedwise:

Create a range in a separate worksheet with the field name at the top of the column and the true values listed below it. Give the range the name "testvals".

Name your database "alldata"

Highlight the cells you want to be filled with True/False. Name this range "results"

Without code you would:
Start off by filling "results" with the value FALSE"
then apply the advanced filter (use the rangenames above for the database and criteria ranges)
then select results, visible cells only and fill with the value "true"
Finally show all data

Using recorder the code is as follows:
Application.Goto Reference:="results"
Selection.FormulaR1C1 = "FALSE"
Range("ALLDATA&quot.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:= Range("testvals&quot, _
Unique:=False
Selection.SpecialCells(xlCellTypeVisible).select
Selection.FormulaR1C1 = "TRUE"
ActiveSheet.ShowAllData

You could make the code more robust by letting it redefine the named ranges "testvals" and "results" at the start
Range("testvals&quot.CurrentRegion.Name = "testvals"
That approach won't quite work for the "results" range but you could use dynamic rangenames for example as an alternative.

Regards
Gavin


Good luck.
Gavona

 

[TheBitDoctor]

Here's another method:

=NOT(ISERROR(MATCH(A10000,Sheet2!$A$2:$A$351,0)))

Stitch: the criteria range is on another sheet in reference to your question.

Works for strings or numbers but be careful of floating point values and round-off errors. Be sure to round to an appropriate number of significant digits first.

Ron

 

[DrBowes]

Gavin, excellent idea!

Bitdoctor, the original problem is formulas were too slow when having to fill a large number of rows.

Stitch

To create the true values from those cells you could use (assuming VBA Sheet name is wsValues)

for i = 1 to 100
TRUE_VALUES = TRUE_VALUES & wsValues.Cells(i,1).value & "."
next i

Then use the code from Zathras as above. However if TRUE_VALUES becomes too long then Gavona's code would be the better option.

 

[Zathras]

Don't forget the leading "." and you would have to declare TRUE_VALUES as a variable and not a constant:

Dim sTrueValues as String
:
:
sTrueValues = "."
For i = 1 to 100
sTrueValues = sTrueValues & wsValues.Cells(i,1).value & "."
Next i
:
:

Note that I follow the "old school" convention of putting constants in all caps and using mixed caps for variables.

 

[TheBitDoctor]

DrBowes:

I couldn't believe that VBA code was faster than a formula so I tried a test. Copy the following code into a new workbook and add a button on Sheet1 that runs Test_Search.

Sub Test_Search()
Sheets.Add
Range("E1&quot.FormulaR1C1 = "Start"
Range("E2&quot.FormulaR1C1 = "Stop"
Range("F1&quot.Value = Timer
Range("A1&quot.FormulaR1C1 = "Search Range"
Range("A2&quot.Select
ActiveCell.FormulaR1C1 = "=INT(RAND()*10000)"
Selection.AutoFill Destination:=Range("A2:A10001&quot, Type:=xlFillDefault
Range("B1&quot.FormulaR1C1 = "Lookup Values"
Range("B2&quot.Select
ActiveCell.FormulaR1C1 = "=INT(RAND()*10000)"
Selection.AutoFill Destination:=Range("B2:B21&quot, Type:=xlFillDefault
Columns("A:B&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C1&quot.FormulaR1C1 = "Item Found"
Range("C2&quot.Select
ActiveCell.FormulaR1C1 = "=NOT(ISERROR(MATCH(RC[-2],R2C2:R21C2,0)))"
Selection.AutoFill Destination:=Range("C2:C10001&quot, Type:=xlFillDefault
Range("D1&quot.FormulaR1C1 = "Count"
Range("D2&quot.Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-1]:R[9999]C[-1],TRUE)"
Calculate
Range("F2&quot.Value = Timer
Range("F3&quot.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("G3&quot.FormulaR1C1 = "Seconds"
Columns("A:G&quot.EntireColumn.AutoFit
ActiveSheet.Buttons.Add(222, 125, 87, 26).Select
Selection.OnAction = "Test2"
Selection.Characters.Text = "Test 2"
ActiveSheet.Buttons.Add(222, 175, 87, 26).Select
Selection.OnAction = "Test3"
Selection.Characters.Text = "Test 3"
ActiveSheet.Buttons.Add(222, 225, 87, 26).Select
Selection.OnAction = "Try_Again"
Selection.Characters.Text = "Try Again"
Range("A2&quot.Select
End Sub
Sub Test2()
Range("E6&quot.FormulaR1C1 = "Start"
Range("F6&quot.Value = Timer
Range("B2&quot.Value = Range("A2&quot
Calculate
Range("E7&quot.FormulaR1C1 = "Stop"
Range("F7&quot.Value = Timer
Range("F8&quot.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("G8&quot.FormulaR1C1 = "Seconds"
End Sub
Sub Test3()
Range("D2&quot.Clear
Range("E6&quot.FormulaR1C1 = "Start"
Range("F6&quot.Value = Timer
Range("B2&quot.Value = Range("A3&quot
Calculate
Range("E7&quot.FormulaR1C1 = "Stop"
Range("F7&quot.Value = Timer
Range("F8&quot.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("G8&quot.FormulaR1C1 = "Seconds"
End Sub
Sub Try_Again()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

On my 650MHz machine the new sheet is created in just under 3 tenths of a second. That seemed reasonable. Then I ran test2 and it takes about 45 seconds!!!! This is why computers have driven me to drink! Test3 takes about 6 hundredths of a second. I'd pay money and give up the booze if someone could explain this one.

Ron

 

[TheBitDoctor]

Ok. I think I understand now. Excel evaluates each cell in a linear fashion. Each cell and all of its dependants. So, when one of the lookup values gets changed all 10000 comparisons depend on that new value and the countif statement has to check all 10000 comparisons 10000 times.

I tried replacing the 10000 comparisons with an array formula. It worked MOST of the time but every once in a while the 45 second monster popped up again. So, I changed the countif function to use an indirect reference. That works every time. I also changed test3 to comment out that cell and that works too.

Ron

Sub Test_Search()
Sheets.Add
Range("E1&quot.FormulaR1C1 = "Start"
Range("E2&quot.FormulaR1C1 = "Stop"
Range("F1&quot.Value = Timer
Range("A1&quot.FormulaR1C1 = "Search Range"
Range("A2&quot.Select
ActiveCell.FormulaR1C1 = "=INT(RAND()*10000)"
Selection.AutoFill Destination:=Range("A2:A10001&quot, Type:=xlFillDefault
Range("B1&quot.FormulaR1C1 = "Lookup Values"
Range("B2&quot.Select
ActiveCell.FormulaR1C1 = "=INT(RAND()*10000)"
Selection.AutoFill Destination:=Range("B2:B21&quot, Type:=xlFillDefault
Columns("A:B&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C1&quot.FormulaR1C1 = "Item Found"
Range("C2&quot.Select
ActiveCell.FormulaR1C1 = "=NOT(ISERROR(MATCH(RC[-2],R2C2:R21C2,0)))"
Selection.AutoFill Destination:=Range("C2:C10001&quot, Type:=xlFillDefault
Range("D1&quot.FormulaR1C1 = "Count"
Range("D2&quot.Select
Range("D3&quot.Value = 10001
ActiveCell.Formula = "=COUNTIF(INDIRECT(""C2:C"" & D3),TRUE)"
Range("F3&quot.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("G3&quot.FormulaR1C1 = "Seconds"
Columns("A:G&quot.EntireColumn.AutoFit
ActiveSheet.Buttons.Add(222, 125, 87, 26).Select
Selection.OnAction = "Test2"
Selection.Characters.Text = "Test 2"
ActiveSheet.Buttons.Add(222, 175, 87, 26).Select
Selection.OnAction = "Test3"
Selection.Characters.Text = "Test 3"
ActiveSheet.Buttons.Add(222, 225, 87, 26).Select
Selection.OnAction = "Try_Again"
Selection.Characters.Text = "Try Again"
Range("A2&quot.Select
End Sub
Sub Test2()
Range("E6&quot.FormulaR1C1 = "Start"
Range("F6&quot.Value = Timer
Range("D3&quot.Value = 4
Range("B2&quot.Value = Range("A2&quot
Range("D3&quot.Value = 10001
Calculate
Range("E7&quot.FormulaR1C1 = "Stop"
Range("F7&quot.Value = Timer
Range("F8&quot.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("G8&quot.FormulaR1C1 = "Seconds"
End Sub
Sub Test3()
Range("D2&quot = "'" & Range("D2&quot.FormulaR1C1
Range("E6&quot.FormulaR1C1 = "Start"
Range("F6&quot.Value = Timer
Range("B2&quot.Value = Range("A3&quot
Range("D2&quot.FormulaR1C1 = Right(Range("D2&quot, Len(Range("D2&quot))
Calculate
Range("E7&quot.FormulaR1C1 = "Stop"
Range("F7&quot.Value = Timer
Range("F8&quot.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("G8&quot.FormulaR1C1 = "Seconds"
End Sub
Sub Try_Again()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

 

[xlbo]

Y'all know what'd be even better.....if you didn't have to enter a formula at all....

There is an option of the ExternalDataRange called "FilladjacentFormulas"

Put your formulae in to start with and then right click somewhere in the data range

Tick the fill adjacent formulae tickbox
Any formulae to the right of the data range will now be automatically filled down to the bottom of the new dataset - whenever it is refreshed - it's as simple as that

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DrBowes]

Geoff

This doesn't help with the speed issue though.

 

[xlbo]

I would counter that it probably is quicker as you are not re-entering all the formulae again - It is entirely up to you but I would almost always use a built in feature like that if it is available. The only other thing I would suggest if you are not doing so already is to enter the formula in bulk ie range("D210000&quot.formula = "yadda yadda" rather than line by line

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244