Using SQL to perform Excel Formulas Faster

  • Hello and thank you in advance,


    I'm currently working on an excel table that is connected to a sharepoint so that it is able to update. The table is about 18,000 rows (but updated and new rows added daily) by 62 columns. Currently I'm using the output in excel to run formulas and determine workloads and equipment orders, an example of code below is what I'm using to determine the number of Desktop Workstations are able to be replaced in each department.


    Code
    1. =COUNTIFS(Deployment!$A:$A,A2,Deployment!$E:$E,"<>Exception",Deployment!$F:$F,"",Deployment!$K:$K,"<>Current",Deployment!$M:$M,"*Desk*")


    "Count If (This item belongs to the department referenced in the left column, this item is not 'on hold' for any reason, this device has not already been deployed, this is not an existing device, this device is a desktop computer)"


    This type of formula is used in a separate table about 900 rows (next to a list of each department) and 16 columns (each deployment item) and it's killing the computer every time it refreshes or a filter is applied. I'm new to SQL but completed a tutorial on CodeAcademy and SQL seems very similar to Excel formulas in that I can understand what I'm seeing when I read it. From what I see online, SQL is much faster but I'm not exactly sure how I can apply a refreshable table inside excel. I'm unable to connect to the SharePoint directly in order to use PowerBI and we're not using an SQL server. If at all possible I would like to avoid using VBA unless I have to. I downloaded a plugin for excel called QueryStorm but I'm looking for any advice from experienced SQL/Excel forum members and hopefully save myself some frustration.


    I appreciate any assistance or advice you can provide.


    - JP

  • Re: Using SQL to perform Excel Formulas Faster


    I have found a method that may work for me, I'm looking to be able to create multiple results columns from a single source but I haven't yet learned how to do that. It appears that I should create a table with the static information (department name, location) and then insert columns with my CASE or WHERE conditions. Is this bad practice or is there an optimal way to process these?


    Example;

    SQL
    1. SELECT [Site #],[Physical Department Name] AS Department, [Facility Name] AS Campus, SUM([Add/Refresh Monitor? (0-3)]) AS Display
    2. From Table_Deployment
    3. WHERE [Monitor Deployed?] IS NULL
    4. Group by [Site #];


    Would need to be joined by Site # to;

    SQL
    1. SELECT [Site #],[Physical Department Name] AS Department, [Facility Name] AS Campus, COUNT([Add Scanner?)]) AS Scanner
    2. From Table_Deployment
    3. WHERE [Scanner Deployed?] IS NULL
    4. Group by [Site #];


    Which would be joined again by Site # to;

    SQL
    1. SELECT [Site #],[Physical Department Name] AS Department, [Facility Name] AS Campus, SUM([Add Video (0-1)?)]) AS Camera
    2. From Table_Deployment
    3. WHERE [Video Deployed?] IS NULL
    4. Group by [Site #];