Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Replace array formula with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Replace array formula with VBA

    In that case how about
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,638
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    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.

  3. #13
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    Quote Originally Posted by Fluff View Post
    In that case how about
    Code:
    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.

    Quote Originally Posted by jasonb75 View Post
    As far as I'm aware, vba can not process a formula style array without evaluate.
    Quote Originally Posted by jasonb75 View Post

    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.


    Last edited by tiredofit; Sep 20th, 2019 at 08:23 AM.

  4. #14
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    Quote Originally Posted by Fluff View Post
    In that case how about
    Code:
    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.

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Replace array formula with VBA

    Are you saying all the data is in col A rather than cols A, B & C?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    Quote Originally Posted by Fluff View Post
    Are you saying all the data is in col A rather than cols A, B & C?

    I have the following:

    Code:
    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.
    Last edited by tiredofit; Sep 20th, 2019 at 09:24 AM.

  7. #17
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,638
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    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.

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Replace array formula with VBA

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    Quote Originally Posted by jasonb75 View Post
    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.

    Quote Originally Posted by Fluff View Post
    Further to Jason's comments, is it only the 1st 3 columns your interested in, as originally you were only matching 2 columns?
    Quote Originally Posted by Fluff View Post
    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.
    Last edited by tiredofit; Sep 20th, 2019 at 12:45 PM.

  10. #20
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,638
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace array formula with VBA

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •