+ Reply to Thread
Results 1 to 24 of 24

get multiple matches from excel sumproduct as vba array()

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    get multiple matches from excel sumproduct as vba array()

    Trying to get the results of the sumproduct formula as an array for further use
    In my VBA procedure.

    In my case I have a list of dates (01/11/2020-10/11/2020) I would like to find a match
    Which dates in the range of + - two days from a certain date (04/11/2020)

    In addition there is a list of values that I want to find in the range of + -1 a certain value

    The common matches of dates and values should be saved into an array with the row number of matches somthing like array()=(3,4,6,7)
    Trying to use the formula SUMPRODUCT((A3:A12>=A1-2)*(A3:A12<=A1+2)*(B3:B12>=B1-1)*(B3:B12<=B1+1)*ROW(A3:A12))
    to get the line numbers but only getting their sum

    Anyone have an idea how this can be done using a macro (UDF) function

    sumproduct.JPG
    Attached Files Attached Files
    Last edited by sryair; 11-20-2020 at 11:36 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,778

    Re: get multiple matches from excel sumproduct as vba array()

    You should create your array as a global, and process the cells in a loop to populate the array for use later or in other procedures:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,634

    Re: get multiple matches from excel sumproduct as vba array()

    Another Option...
    Please Login or Register  to view this content.
    UDF "CTRL+SHIFT+ENTER"

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 11-21-2020 at 06:53 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    Thanks

    The question is can this also be done directly by using the results obtained from the sumproduct function without the conditions and loops?

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,778

    Re: get multiple matches from excel sumproduct as vba array()

    SUMPRODUCT does not return an array. You may be able to use a function that does return an array, but you have two working solutions.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,634

    Re: get multiple matches from excel sumproduct as vba array()

    can this also be done directly by using the results obtained from the sumproduct function
    Your formula sums the row numbers?

    Anyone have an idea how this can be done using a macro (UDF) function
    As Bernie says...you have been provided solutions...

    Now no loop but not with your sumproduct function...

    Please Login or Register  to view this content.
    Still don't know what you want to do with the stored row numbers!
    Last edited by sintek; 11-22-2020 at 12:26 PM.

  7. #7
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    thank you!

    Trying to write a VBA function to compare bank books with an accounting report.
    I know it takes about two days from the time the invoice is issued until the transaction is received in the bank account.
    Therefore first I need to find the rows of dates and amounts.

    The next step is to compare the descriptions that usually different between the invoices and the bank books and find the best match.

    After finding the best match it is required to mark the lines of the bank and the invoices with a common code

    For me this is a very complex task but it can save me a lot of time ...

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    Quote Originally Posted by sryair View Post
    Trying to use the formula SUMPRODUCT((A3:A12>=A1-2)*(A3:A12<=A1+2)*(B3:B12>=B1-1)*(B3:B12<=B1+1)*ROW(A3:A12))
    to get the line numbers but only getting their sum

    Anyone have an idea how this can be done using a macro (UDF) function
    No loop, no use of extra formula.
    If you want to use in cell
    1) Excel version with spill functionality
    =SumProducts(A3:B12,A1,B1)
    or
    =TRANSPOSE(SumProducts(A3:B12,A1,B1))
    2) other version
    =IFERROR(SumProducts($A$3:$B$12,$A$1,$B$1,COLUMN(A1)),"")
    then copy to the right
    or
    =IFERROR(SumProducts($A$3:$B$12,$A$1,$B$1,ROW(A1)),"")
    then copy down

    Sample of use in VBA.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 11-22-2020 at 10:08 PM. Reason: Sample file attached.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,267

    Re: get multiple matches from excel sumproduct as vba array()

    Here s the code for two macros Which give ARRAY of row numbers with condition
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    thanks

    Excellent solution

    How to use parameters instead of addresses

    I mean the parameters Rng, Mydate, Myval instead of A3: A12, A1, B1

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    What do you mean?

  12. #12
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    My workbook has 2 sheets

    the first sheet with details from accounting and the other a report from transactions at the bank

    I need to compare and associate (using a common code) every transaction from accounting to a bank transaction

    What I need is a function that for each transaction in the accounting will return the line numbers of the matches in the bank books

    For example for the first date in accounting 02/01/2020 and a value of 2544 I want to find in the bank books all transactions two days before and two days after this date with a value of 2543-2545 and get the line numbers of the matches in the bank books (lines 11-12)

    Thanks
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    Upload a workbook with such 2 worksheets and the result that you want.

  14. #14
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    I did it
    attached file

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    And your result is?

  16. #16
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    to get the line numbers in the bank sheet in this case lines 11-12 (are highlighted in light blue)

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    OK the line numbers...

    Please Login or Register  to view this content.
    Last edited by jindon; 11-27-2020 at 06:34 AM.

  18. #18
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    I want to use this function for further comparison of the descriptions as well and then choose at the end the match that will meet the criteria of the date (+ - 2 days) value (+ -1) and best partial description

    The macro is then required to mark in a common code in accounting (say 1000) and the same code in the bank books if no match was found between accounting and the bank to mark "no match" in accounting

    When I have a code like this, it will save me a lot of time ...

    Thank you

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    See my post above.

  20. #20
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    Thank you!

    Trying to understand and see how to use it ...

    Do you have an idea how to make a fuzzy matching to the text as well?

    I mean "Microsoft Comp" and "Microsoft" are 64% identical (len 9/len 14)

    Then the function will return the most appropriate line number or even (the dream ...) the common code?

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    Google about

    VBA Fuzzy Match

    You will find some codes.

  22. #22
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    actually that's why I need the function to return the line numbers of the matches of the date and the amount
    and then make Fuzzy Match of the text ...

    And only after finding the best match text (or mismatch) select the best match line in both sheets and mark in common code...

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,376

    Re: get multiple matches from excel sumproduct as vba array()

    You got the function to get the line number(s) already and that was your question in this thread.
    If you want to do a Fuzzy match, you need to open a new thread.

  24. #24
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    52

    Re: get multiple matches from excel sumproduct as vba array()

    thank you!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple matches in one row (no array formula)
    By TheN in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-12-2016, 04:58 PM
  2. Replies: 2
    Last Post: 09-02-2015, 01:06 AM
  3. SUMPRODUCT with multiple matches
    By mellowmarshall in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2014, 02:43 PM
  4. [SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches
    By bkwins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 04:57 AM
  5. Replies: 7
    Last Post: 04-11-2013, 12:46 AM
  6. [SOLVED] two-array matching formula with multiple matches
    By ilikeexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2013, 02:19 PM
  7. SUMPRODUCT count how many in a range that matches multiple keywords
    By tomlancaster in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2012, 08:13 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1