Faster Alternative to Array Formula

[cbrown6305]

I have two tables, a table of charges and table of payments. I wrote the following array formula to bring a date of service from the charges table to the payments table using a common UniqueID filed that is common in the payments and charges table. The formula is in the payments table and brings in the date of service that is closest (but not later than) the related date of payment (related, as it, they have the same UniqueID). The formula is listed below

{=MAX(IF((ChargesbyID[Unique ID]=[@[Unique ID]])*(ChargesbyID[date_of_service]<=[@[date_of_payment]]),ChargesbyID[date_of_service]))}

ChargesbyID is the charges table. All "@[" items reference columns within the payments table.

The formula works, but the charges table table has about 300,000 rows and the payments table as 360,000 rows, so it takes a crazy amount of time to run (i.e., >30 min). Is there a different way of doing this that doesn't lock me out of excel for an hour?

 

[kvsrinivasamurthy]

Is it possible to upload a sample file. We can try UDF. It may help.

 

[Peter_SSs]

One thing that might help is to know what version of Excel you are using as that determines what is available to you. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[cbrown6305]

Uploads are below. I've updated my info - running office 2016.

Charges Table

Array Example 1.xlsx
	B	C	D
5	Unique ID	date_of_service	Sum of amount
6	Location 1 | 1000	1/23/2018	249
7	Location 1 | 1000	5/30/2018	132
8	Location 1 | 1000	10/4/2018	132
9	Location 1 | 1000	2/5/2019	249
10	Location 1 | 1000	2/8/2019	188
11	Location 1 | 1000	6/6/2019	180
12	Location 1 | 1000	10/8/2019	104
13	Location 1 | 1000	12/11/2019	1,836
14	Location 1 | 1000	2/12/2020	132
Sheet1

Payments Table

Array Example 1.xlsx
	F	G	H	I
5	Unique ID	date_of_payment	Sum of payment	Date of Payment
6	Location 1 | 1000	2/20/2018	(249)	1/23/2018
7	Location 1 | 1000	6/12/2018	(132)	5/30/2018
8	Location 1 | 1000	2/19/2019	(156)	2/8/2019
9	Location 1 | 1000	3/1/2019	(70)	2/8/2019
10	Location 1 | 1000	6/24/2019	(108)	6/6/2019
11	Location 1 | 1000	10/22/2019	(80)	10/8/2019
12	Location 1 | 1000	12/23/2019	(569)	12/11/2019
13	Location 1 | 1000	2/21/2020	(80)	2/12/2020
14	Location 1 | 1001	1/3/2018	(46)	1/0/1900
15	Location 1 | 1001	2/5/2018	(48)	1/26/2018
Sheet1

Cell Formulas
Range	Formula
I6:I15	I6	=MAX(IF((ChargesbyID[Unique ID]=[@[Unique ID]])*(ChargesbyID[date_of_service]<=[@[date_of_payment]]),ChargesbyID[date_of_service]))
Press CTRL+SHIFT+ENTER to enter array formulas.

Formula in Date of Payment column of payments table:

{=MAX(IF((ChargesbyID[Unique ID]=[@[Unique ID]])*(ChargesbyID[date_of_service]<=[@[date_of_payment]]),ChargesbyID[date_of_service]))}

 

[Zot]

I guess the best way to do this is using VBA. Each time any change to sheet, a calculation takes place. Furthermore, with that long formula in each cell, it will consume too much memory and makes you worksheet BIG.

I'm using 2016 but I'd say that 2013 can run the same code half the time the 2016 can ?

 

[kvsrinivasamurthy]

Some clarifications are required.
In charged table only one Unique ID is shown. Does same Id repeats in many rows. Same Id is distributed to many rows or they are together in a set of rows (From Row 10 to Row25.
Secondly the ID's are sorted or not.

 

[cbrown6305]

There are many different Unique ID's in the charges table. Those IDs are unique to a location-account and each location-account has many different charges (in the ChargesbyID table) and many different payments (in the PaymentsID table). The UniqueID's are sorted alphabetically so UniqueIDs that are the same will come right after one another. The sorting for the ChargesID table is currently set a level 1: ChargesID alphabetically, level 2: date of service ascending.

 

[kvsrinivasamurthy]

It is difficult to explain without File. I have tried.
In I6 of payments

=MAX(((INDEX(ChargesbyID[Unique ID],$J6):INDEX(ChargesbyID[Unique ID],$K6)=[@[Unique ID]])*(INDEX(ChargesbyID[date_of_service],$J6):INDEX(ChargesbyID[date_of_service],$K6)<=[@[date_of_payment]]))*(INDEX(ChargesbyID[date_of_service],$J6):INDEX(ChargesbyID[date_of_service],$K6)))

In J6 of payments

=IF($F6=$F5,J5,MATCH($F6,ChargesbyID[Unique ID],0))

In K6 of payments

=IF($F6=$F5,K5,MATCH($F6,ChargesbyID[Unique ID],1))

Pull down all

J & K are helper columns in Payments table (assumed not used), can be hidden also.
J6 gives start position of F6 in charges table
K6 gives end position of F6 in charges table
I6 formula selects the Range from charges table from J6 K6 instead of entire range of table.
There by Search time is reduced drastically.