locked
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).

    Example: 
    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

Answers

  • 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