CODEWITHSUNDEEP
excelvbavlookupsumifs
jonplaca
jonplaca

Reputation: 827

=VLOOKUP(SUMIFS) Statement requires long processing time

EDIT

Workable File: https://docs.google.com/spreadsheets/d/1IhxKqnj62vssttZA73hF-S_TvynQNPdtSyb7wWq3Kpo/edit?usp=sharing

Screenshot: https://i.sstatic.net/eVe16.png

OBJECTIVE

I am currently creating a pricing model for CUSTOMERS, who order specific PRODUCTS, and CONFIGURATIONS of said product. The end result is to SUM SALES and LOOKUP a table to place the customer into a category.

APPROACH

PROBLEM(S): Processing time for rows is 30-60 minutes. I've tried breaking out the SUMIFS statements from the VLOOKUP to calculate them independently, but had no measurable success. I've also tried making SQL queries to the server (using DSUM) to reduce the local workload, but this also had no positive effect in processing time.

QUESTIONS

  1. How can I efficiently process large amounts of information and properly categorize CUSTOMERS based off of their SALES, PRODUCT ID, and CONFIGURATION criteria?
  2. I've been considering doing this in VBA, but am not sure whether or not VBA is actually more efficient than a cell formula.
  3. Would add-in utilities offer any solution (e.g power pivot or power query)?

Upvotes: 1

Views: 489

Answers (1)

mattdeak
mattdeak

Reputation: 190

With 500 thousand rows I would consider using Access or another database application. It's just a lot of data for VLOOKUPs and SUMIFs and even Excel VBA is going to be slow if you do tons of comparisons.

SUMIFs and VLOOKUPs are very easy to replicate in queries, and will be much much faster.

If using ACCESS, try using this SQL Query:

SELECT [Customer ID],[Product ID],[Configuration],[Pricing Tier] 
FROM 

    (SELECT [Data Table].[Customer ID], [Data Table].[Product ID], 
    [Data Table].[Configuration], Sum([Data Table].Sales) AS [Sales Sum]
    FROM [Data Table]
    GROUP BY [Data Table].[Customer ID], [Data Table].[Product ID], 
    [Data Table].[Configuration])

AS T,
[Pricing Table] P
WHERE [Annual Sales] = (SELECT Max([Annual Sales]) 
                       FROM [Pricing Table] 
                       WHERE [Annual Sales] <= [Sales Sum]);

This should (hopefully) do the trick. Let me know!

Upvotes: 2

Related Questions