none
Is there a simple way to find ALL matches withough using an array RRS feed

  • Question

  • I'm using this function which finds all matching cash flows based on a security identifier.

    =IF(ROWS('Cash Flows'!$C$2:C2)<=COUNTIF('Cash Flows'!$B$2:$B$416621,$K$1)/$Q$3,INDEX('Cash Flows'!$C$2:$C$416621,SMALL(IF('Cash Flows'!$B$2:$B$416621=$K$1,ROW('Cash Flows'!$B$2:$B$416621)-ROW($K$1)),ROWS('Cash Flows'!$C$2:C2))),"")

    This is committed with CES.

    So, I'm saying find a certain value:  cell K1

    Find all matches in ColumnB and give me the corresponding dates in ColumnC.

    The function above works perfect, but it is so, so, so slow because I need to evaluate over 400k rows.  Is there some kind of UDF that would improve the performance?  So, even a Sub?  When I started working on this project last week, I was looking at around 10k rows and the performance was blazing fast.  Now, when I expand the universe to 400k rows, of course, it grinds to a halt.

    Thanks everyone.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.



    • Edited by ryguy72 Tuesday, January 19, 2016 3:00 AM
    Tuesday, January 19, 2016 2:47 AM

All replies

  • I ended up using VBA to control a filter in the sheet.  It does the same thing as the function I posted.  It's not much faster though.  I guess the problem is the number of records.  400k is a lot!

    If anyone can think of a faster way to do this, please do let me know.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, January 19, 2016 3:24 PM
  •  It's not much faster though.  I guess the problem is the number of records.  400k is a lot!

    If anyone can think of a faster way to do this, please do let me know.

    Show us a sample file with your code.

    Andreas.

    Tuesday, January 19, 2016 5:42 PM
  • The data set looks like this image.

    It's basically 416,621 thousand rows of that.  Now I just have some code that moves back and forth between my cash flow sheet, where I do the filtering, and my analytics sheet, where I'm doing the calculations.  It's basically like this.

                    Sheets("Cash Flows").Select
                    ActiveSheet.Range("$A$1:$D$416621").AutoFilter Field:=2, Criteria1:=Sheets(MyCell.Value).Range("K1").Value
                    ActiveSheet.Range("$A$1:$D$416621").AutoFilter Field:=1, Criteria1:=Sheets(MyCell.Value).Range("K3").Value
                    
                    Columns(3).Cells.SpecialCells(xlCellTypeVisible).Cells(2).Select
    
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                    Sheets(MyCell.Value).Select

    I'm thinking a parameter query to an Access table would be faster.  Maybe an ADO connection.  I'm not sure what the performance would be like in that kind of setup.  Maybe that's getting too abstract.  I really wanted to keep everything in Excel, if possible.  The only problem now is that the performance is horrible!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, January 19, 2016 6:12 PM
  • Hi ryguy72,

    >>I'm thinking a parameter query to an Access table would be faster
    I suggest you try to use query in Access. Without practice, we are not sure whether it would be faster.

    >> I really wanted to keep everything in Excel, if possible
    Do you mind splitting the records into many worksheet or workbook? If not, I suggest you store records in many workbooks and then query from them with vba code for a try.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 20, 2016 5:46 AM
  • The data set looks like this image.


    That doesn't help much. Again:

    Can you please upload a sample file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    Around 100 rows of data  is enough for testing.

    So we can download the sample file, run your code and view the results.

    And IMHO a query into a Access table would not be faster.

    Andreas.

    Wednesday, January 20, 2016 8:08 AM