0

I need some help with an Excel Project that's giving me headaches. I succeeded to achieve everything I wanted but the result is too heavy for Excel and it crashes all the time. I'm over-using the INDEX and MATCH functions on large tables (50 000+ lines) and Excel doesn't like it. I'm looking for a way to do the same thing in a lighter way for Excel.

Here's what I achieved : I created a report that helps me analyzing my employees's performance VS their billing targets. To create such a report, I used a Pivot Table.

That Pivot Table needs this information as its source :

  • Each sales that every employee made (amount in $ and date)
  • The hourly rate of each employee (which changes for every period, see TABLE1 below)
  • The billing target for each employees (which changes for every period, see TABLE1 below)

Here's my setup. I have 3 tables :

  • TABLE1 (See attached image) - A table where I manually input data for each of my employees (hourly rate and billing target). Their billing target and hourly rate change every period. So, each period has a different line and I indicate the first day of the period and the last day of the period.

  • TABLE2 (See attached image) - Table that contains sales data exported from another software I use. Each line represents an amount sold by an employee to a customer on a specific date. This table is pretty heavy and contains more than 50 000 lines. Moreover, the last 2 columns of this table use Index and Match functions to get the right hourly rate and the right billing target from TABLE1. That means that each of those 50 000 lines uses the INDEX and MATCH functions twice… This part is too heavy for Excel and I need a workaround. Moreover, TABLE2 is getting refreshed every few days with new data coming from my other software (an ERP). So the solution I need to find must take that into account and must be permanent (I try to avoid steps that will have to be done everytime I refresh TABLE2 with new data).

  • TABLE3 - A Pivot Table that uses TABLE2 as its data source. I use the slicer to select the name of an employee and a timeline to specify which months I want to display. Then the Pivot Table shows my employee's statistics grouped by months. The main statistic is the amount of "billed hours" for each employee, which is in reality the amount of sales made by that employee, divided by their hourly rate on a specific date.

My thoughts :

  • It is absurd that TABLE2 uses that many INDEX and MATCH functions. For example, if Employee1 made 500 sales between 2020-07-01 and 2020-07-31 (the same month, thus the same period, thus the same hourly rate and billing target), there will be 500 different lines that will use INDEX and MATCH to get the same hourly rate and billing target from TABLE1. That leads to a lot of duplicated calculation and a lot of duplicated data.

  • Would it be possible for a Pivot Table Calculated Field to use INDEX and MATCH in its formula? And would it be lighter for Excel to do so?

  • Another way would be to add, at the bottom of TABLE2, 12 lines per year (1 for each month) for every employee where I would write their hourly rate and the billing target. That way, the Pivot Table would be able to display an hourly rate and a billing target for each month, for each employee. That solution would work and would be lighter for Excel, but it would create a high risk of making mistakes while manually inputting the data.

I'm open to all suggestions including VBA! Thank you very much for your precious time!

EDIT : FORMULA

As requested, here's my INDEX AND MATCH formula that is in TABLE2 and gets the hourly rate from TABLE1 :

=INDEX(TAB_Employee_Data[[#All];[Hourly_Rate]];MATCH([@[Date (Cell)]]; IF(TAB_Employee_Data[[#All];[Name]]=[@[Employee(Cell)]];TAB_Employee_Data[[#All];[First day of the period]]);1))

TAB_Employee_Data is the tab that contains "TABLE1". I translated the names of the fields since all my work is in French.

This formula does the following : it searches the name of an employee in TABLE1 and finds the period which fits the date of a line in TABLE2. Also, to work properly, I need to sort the lines of TABLE1 in chronological order.

TABLE 1 : TABLE1

TABLE 2: TABLE2

Alexandre
  • 1
  • 1
  • 1
    Basically can you show your formula? – Scott Craner Aug 24 '20 at 18:02
  • I edited my post. – Alexandre Aug 24 '20 at 18:34
  • You are using an array formula. You will most likely need to write vba that uses arrays to do the whole table at once. – Scott Craner Aug 24 '20 at 18:34
  • 1
    I'd wonder if merging the tables in Power Query would be any faster. – Ron Rosenfeld Aug 24 '20 at 19:34
  • @RonRosenfeld Thank you Mr. Craner for the answer. Would you have any piece of advice on how I could use VBA? I'm not super familiar with VBA itself, but willing to learn. – Alexandre Aug 26 '20 at 13:02
  • @RonRosenfeld Thank you Mr. Rosenfeld. I had to watch some videos about Power Query to understand what it was. One thing that wasn't mentioned in my original post : TABLE2 (which is about 50 000+ lines), is a table I refresh every few days. So I re-export data from my other software and I copy it to TABLE2. In those circumstances, my understanding is that I would have to do a Power Query everytime I refresh my data. That solution would be redundant and I'm trying to avoid as many steps possible in the import-export process (as they represent high risk of making mistakes). – Alexandre Aug 26 '20 at 13:06
  • @ScottCraner I'm sorry Mr. Craner. I tagged Mr. Rosenfiled instead of you on previous comment. – Alexandre Aug 26 '20 at 13:11
  • Redundant? Not sure that is the correct word. And, in any event, you could also use Power Query to do the import. Seems to me that, for you, it's a matter of figuring out which approach works faster. – Ron Rosenfeld Aug 26 '20 at 17:00
  • Indeed, redundant is not the correct word. Sorry, French speaker here. I mean that it would be repetitive to merge my tables with Power Query everytime I refresh my data (which is done every day). I've never heard of Power Query before I read your comment, so I'm really not familiar with it. So I really don't know what it can do. What would be the advantage to import data (refresh my data) with Power Query? – Alexandre Aug 28 '20 at 14:26

0 Answers0