Replace array formula with VBA

[tiredofit]

I am trying to replace the evaluate method with "only VBA".

This first piece of code is a "normal" index match, in that there is only a single criteria and so is entered simply by pressing the Enter key.

a and b return the same value, as expected.

    Dim IndexRng As Range
    Set IndexRng = Range("A1:A2")
    
    Dim rng As Range
    Set rng = Range("B1:B2")
    
    Dim rng1 As Range
    Set rng1 = Range("E3")
    
    Dim a, b
        
    a = Evaluate("=INDEX(" & IndexRng.Address & ",Match(" & rng1.Address & "," & rng.Address & ", 0), 1)")

    b = Application.Index(IndexRng, Application.Match(rng1.Value, rng, 0), 1)

This second piece of code is an array formula because there are multiple criteria and is entered by pressing Ctrl+Shift+Enter.​

c returns the correct value but d errors.

    Dim IndexRng As Range
    Set IndexRng = Range("A1:A2")
    
    Dim rng As Range
    Set rng = Range("B1:B2")
    
    Dim rng2 As Range
    Set rng2 = Range("C1:C2")
    
    Dim rng3 As Range
    Set rng3 = Range("E3")
    
    Dim rng4 As Range
    Set rng4 = Range("F3")
    
    Dim c, d
    
    c = Evaluate("=INDEX(" & IndexRng.Address & ",Match(1, (" & rng3.Address & " = " & rng.Address & ") * (" & rng4.Address & "=" & rng2.Address & "),0))")
    
    d = Application.Index(IndexRng, Application.Match(1, (rng3 = rng) * (rng4 = rng2), 0, 1))

The error message is:

Run-time error '13':

Type mismatch

How can I change d to make it work?

Thanks

 

[Fluff]

In that case how about

Sub tiredofit()
   Dim Ary As Variant, Crit As Variant
   Dim i As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value2
   Crit = Range("E3:F3").Value2
   For i = 1 To UBound(Ary)
      If Ary(i, 2) = Crit(1, 1) And Ary(i, 3) = Crit(1, 2) Then
         Range("E4").Value = Ary(i, 1)
         Exit For
      End If
   Next i
End Sub

 

[jasonb75]

As far as I'm aware, vba can not process a formula style array without evaluate.

It might be possible to speed up the loop method by using the range.find method, but, depending on how many times the content of rng3 is likely to appear in rng, it could slow it down.

If rng3 is only ever likely to appear maybe 5 - 10 times in 1000 rows then it should be quicker, 100 times in 1000 rows and you're in for a long wait.

With either loop method, speed is dependent on the position of the match in the table, closer to the top will be quicker to find.

 

[tiredofit]

In that case how about

Sub tiredofit()
   Dim Ary As Variant, Crit As Variant
   Dim i As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value2
   Crit = Range("E3:F3").Value2
   For i = 1 To UBound(Ary)
      If Ary(i, 2) = Crit(1, 1) And Ary(i, 3) = Crit(1, 2) Then
         Range("E4").Value = Ary(i, 1)
         Exit For
      End If
   Next i
End Sub

Thanks it certainly works on the small data posted here.

Will try it on my actual data.

As far as I'm aware, vba can not process a formula style array without evaluate.

It might be possible to speed up the loop method by using the range.find method, but, depending on how many times the content of rng3 is likely to appear in rng, it could slow it down.

If rng3 is only ever likely to appear maybe 5 - 10 times in 1000 rows then it should be quicker, 100 times in 1000 rows and you're in for a long wait.

With either loop method, speed is dependent on the position of the match in the table, closer to the top will be quicker to find.

My other worry is the PC running out of memory when looping over many records.


​

 

[tiredofit]

In that case how about

Sub tiredofit()
   Dim Ary As Variant, Crit As Variant
   Dim i As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value2
   Crit = Range("E3:F3").Value2
   For i = 1 To UBound(Ary)
      If Ary(i, 2) = Crit(1, 1) And Ary(i, 3) = Crit(1, 2) Then
         Range("E4").Value = Ary(i, 1)
         Exit For
      End If
   Next i
End Sub

Second thoughts, my actual criteria consists of a long column of data, hence I originally used the multiple criteria array formula.

 

[Fluff]

Are you saying all the data is in col A rather than cols A, B & C?

 

[tiredofit]

Are you saying all the data is in col A rather than cols A, B & C?

I have the following:

Sheet1

ColA ColB ColC Superfield
1      a      aa    1aaa
2      b      bb    2bbb
3      c      cc    3ccc

Sheet2

ColA ColB ColC Superfield
4      g     gg    4ggg
5      h     hh    5hhh
6      i     ii    6iii

In this example, both Sheet1 and Sheet2 have only 3 rows (but in reality it's a few houndred thousand) and 3 columns (ignore the Superfield column).

I want to look at row 1 of Sheet1 in Sheet2 and find if there's a match. Likewise for rows 2 and 3. I could reconcile using a Superfield, made up of the concatenation of the other fields.

As you can see, if I had a few hundred thousand rows, it'll take a long time to loop.

If Sheet1 had 10,000 rows and Sheet2 100,000 rows, it'll take 1bn loops.

This is why originally I chose to use array formulae and in particular, the index match with multiple criteria.

 

[jasonb75]

Just to make sure that I'm on the right lines, you want to match every row in sheet 1 to sheet 2 and every row in sheet 2 to sheet 1?

You wouldn't need to loop through both sheets entirely, part of one could be done while looping through the other and I have no idea where you got 1bn from, it would actualy be 110,000 loops with a variable number of passes depending on how efficient we can make it.

What do you actually have in columns A, B and C? Would either column B or C contain entries that only appear once on each sheet?

Please don't post private / confidential data from your real sheet, but if we had a better idea of what you have then we might be able to come up with a better suggestion.

 

[Fluff]

Further to Jason's comments, is it only the 1st 3 columns your interested in, as originally you were only matching 2 columns?
Also what do you want to happen if there is a match?

 

[tiredofit]

Just to make sure that I'm on the right lines, you want to match every row in sheet 1 to sheet 2 and every row in sheet 2 to sheet 1?

You wouldn't need to loop through both sheets entirely, part of one could be done while looping through the other and I have no idea where you got 1bn from, it would actualy be 110,000 loops with a variable number of passes depending on how efficient we can make it.

What do you actually have in columns A, B and C? Would either column B or C contain entries that only appear once on each sheet?

Please don't post private / confidential data from your real sheet, but if we had a better idea of what you have then we might be able to come up with a better suggestion.

Further to Jason's comments, is it only the 1st 3 columns your interested in, as originally you were only matching 2 columns?

Also what do you want to happen if there is a match?

​

Thanks for your replies.

Re the 1bn, I thought for every row in Sheet1 (from 1 to 10,000) you have to look (possibly but especially in the event you cannot find a match) through every row in Sheet2 (from 1 to 100,000), hence 10,000 x 100,1000 = 1bn.

I'll mock up some data over the weekend and reply.

 

[jasonb75]

I see where you're coming from with your 1bn figure now, different ways of looking at the same thing produce different answers. Looking at it from your way of thinking, it would be 1bn iterations if there were no matches anywhere. There are a number of factors that could reduce that significantly.

TBH, we don't reall need a mock up of the data, just an idea of what is contained within the relevant columns.

The content of columns B and C in each sheet is the significant part here, particularly the likelihood of records being found only once, multiple times, or not at all in one or both sheets.

In other words
Will the content of any single cell in sheet 1 Column B be found more than once in sheet 1 column B?
Will the content of any single cell in sheet 1 Column B be found more than once in sheet 2 column B?
Will the content of any single cell in sheet 2 Column B be found more than once in sheet 1 column B?
Will the content of any single cell in sheet 2 Column B be found more than once in sheet 2 column B?

Will the content of any single cell in sheet 1 Column C be found more than once in sheet 1 column C?
Will the content of any single cell in sheet 1 Column C be found more than once in sheet 2 column C?
Will the content of any single cell in sheet 2 Column C be found more than once in sheet 1 column C?
Will the content of any single cell in sheet 2 Column C be found more than once in sheet 2 column C?

Then, using column D as a superfield of columns B and C

Will the content of any single cell in sheet 1 Column D be found more than once in sheet 1 column D?
Will the content of any single cell in sheet 1 Column D be found more than once in sheet 2 column D?
Will the content of any single cell in sheet 2 Column D be found more than once in sheet 1 column D?
Will the content of any single cell in sheet 2 Column D be found more than once in sheet 2 column D?

With the 12 criteria sets above, some will be a given based on others, I've just thrown in every possible combination to be sure that an important one didn't get missed.