Most Efficient Lookup Macro for Big Data? RRS feed

  • Question

  • Hello All

    We have this table on a sheet:

    We´re trying to get other Symbols Numbers from the same FullDate as shown in the sample pic.
    The pic is showing a powerpivot table in columns A B and C with almost 800k rows 
    (the full data in the model has 2.5 million rows).

    To get the D1 value, it must look up the same FullDate (A1) in AA, find "AAPL" (B2) in BB, and get the Number (D2) in CC

    I understand that these lookups use a lot of processing and want to know which would be the 
    fastest/more efficient way to get those values.

    As a reference it´s running on Win10 64b, Excel 2013 64b, 6 cores and 8 MB DDR3.  
    I´ve tried doing it with formulas (sumifs, index match, concatenate and vlookup). 
    All of them takes the CPU to 100% and Excel freezes.

    Also, no progress is being saved using formulas. Each time the file opens it starts all over. 

    So maybe this is a task for a macro that can run by date/else?

    What would be the most efficient way to get those values?

    • Edited by NicoPer Tuesday, May 2, 2017 4:06 PM
    Tuesday, May 2, 2017 2:10 AM


  • Replying my own question.

    In this case, a Date key was needed.
    Then attach all the needed columns to the date and call the related data from calculated columns.

    • Marked as answer by NicoPer Tuesday, May 2, 2017 3:29 PM
    Tuesday, May 2, 2017 3:29 PM