Help - Increasing spreadsheet efficiency - intelligent formulas - Client follow up

Hi.

I appreciate help with the following issue.

I’ve constructed a spreadsheet that automatically brings the Last Follow Up Date and Last Comments of my customers according to my follow up.

#

Customer

Phone

Last contact date

Description

1-Oct

2-Oct

3-Oct

4-Oct

5-Oct

6-Oct

7-Oct

8-Oct

9-Oct

10-Oct

11-Oct

12-Oct

13-Oct

14-Oct

15-Oct

16-Oct

17-Oct

18-Oct

19-Oct

20-Oct

21-Oct

22-Oct

1

John Doe

1111111

19-Oct

The customer is interested in a long term agreement.

Called the customer

Had a meeting with the customer

The customer is interested in a long term agreement.

2

James Hetfield

2222222

15-Nov

We started working with the customer

The customer called

3

Lars Ulrich

12121212

20-Oct

The customer is not interested

The customer is not interested

4

Kirk Hammet

13131313

18-Dec

The customer said we gave him the best service

Sent follow up mail

5

Melissa Palmer

14141414

15-Oct

We made our fist approach

We got Melissas card on a convention

We made our fist approach

Lately I’m having speed problems with my spreadsheet, this considering that im working with arrays and I have around 40,000 customers.

I was wondering if there is a way to make my formulas more efficient in order to avoid the occasional stop of my spreadsheet.

Ive attached a sample of my spreadsheet.

I really hope you can help me.

Best regards.

Camilo
View attachment Xcel Guru - Clients Follow Up Exercise.xlsx

No idea if it will speed things up, but you could use this in F4 copied down:

=INDEX($G4:$CJ4,,MATCH($E4,$G$3:$CJ$3,0))

You are working with arrays, but not with array formulae, which would be confirmed using CTRL+SHIFT+ENTER and would appear with curly brackets {}.

Thanks for the advice, but the spreadsheet does not seem to be faster. Probably the formula to best optimize would be the one related with the "last contact date".

I doubt you can optimise that formula any further. In the real spreadsheet, are you using entire column references (e.g. A:A) instead of limiting them? If so, this is the most likely culprit for slowing things down. Neither of the formulae you are using is an array formula, and the overhead will not be high, so something else must be coming into play.

Since you've already done the matching in column E, use the value therein with a LOOKUP; in cell F5:
=LOOKUP(E5,$G$3:$CJ$3,$G5:$CJ5)
and copy down.
Don't know if it's any more efficient though.