Join 2 Data sources with nearby value

[Irfan]

Hi,

Currently working on a report where I have to find the cash short/over.

Basically we have sales data (Table 1) and bank deposits data (Table 2) and trying to find the amount variance.

Challenge is Bank deposits data we don't have the sales date. So basically we don't have any fields which we can use to join them.

The logic is we have to find the nearby date and nearby value. 

Below is sample data-

Table1	Table 2
Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-06-03	199.08	2019-06-07	199.62	0.54
2019-06-04	147.99	2019-06-07	147.97	-0.02
2019-06-05	129	2019-06-07	129.8	0.80
2019-06-06	180.83	2019-06-07	180.94	0.11
2019-06-07	177.94	2019-06-10	171.82	-6.12
2019-06-08	166.19	2019-06-10	165.71	-0.48
2019-06-09	219.75	2019-06-10	219.16	-0.59
	1220.78		1215.02	-5.76
Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-06-10	117.6	2019-06-14	114.82	-2.78
2019-06-11	125.73	2019-06-14	128.24	2.51
2019-06-12	137.6	2019-06-14	137.61	0.01
2019-06-13	183.18	2019-06-14	183.79	0.61
2019-06-14	161.26	2019-06-17	161.15	-0.11
2019-06-15	247.71	2019-06-17	248.92	1.21
2019-06-16	205.44	2019-06-17	199.93	-5.51
	1178.52		1174.46	-4.06

Any help would be appreciated.

Regards,

Irfan

[danilang]

Hi @Irfan 

Here's a workflow that does what you're looking for

It uses 2 iterative macros.  The first one finds the correct deposit date for each of the sales dates.  The second one compares each sale to all corresponding deposits on correct deposit date and assigns the one where the variance is the smallest

Here are the results

Dan

[Irfan]

Thank You so much Dan.

I appreciate your help.

It's working but for couple of date range getting issues, same bank deposits joining twice with sales. See attached screen shot.

Result should be like-

Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-05-20	156.76	2019-05-24	156.73	(0.03)
2019-05-21	157.2	2019-05-24	158.34	1.14
2019-05-22	175.49	2019-05-24	175.45	(0.04)
2019-05-23	136.58	2019-05-24	136.55	(0.03)
2019-05-24	149.2	2019-05-28	149.63	0.43
2019-05-25	191.47	2019-05-28	132.14	(59.33)
2019-05-26	118.93	2019-05-28	118.93	-

[danilang · Accepted solution]

Hi @Irfan 

I had to change the way the Closest Amount macro worked to remove a value from the deposit list once it was used. 

Dan 

[Geoowl]

Thanks for posting @Irfan - It's an interesting problem.

Just adding thoughts to @danilang's solution which looks great:

 - You may want to build in a threshold whereby some records will simply be left unassigned (if the variance is very high and/or days between deposit and sales date is large).

 - Currently solution is based on variance only, whereas a weighting to also use difference between dates might help (i.e. variance of 10 days and $1 in cash is perhaps a worse match than an exact match on date and a variance of $1.5). If you wanted to include, simply calculating datetimediff in days and creating a scored ranking combining this and variance and finally sorting on this gives you a combination of the two. You could also produce a %match column based on this, which would also allow you to filter for matches below a threshold in response to first point. Of course, weighting between these two is more of an art than science.

Best of Luck,

Matt

[Irfan]

Hi Dan,

Awesome!

It would be great if you can update the macro. In case exact match don't lookup the nearby value.

Please see the attached screen shot.

Result should be like below-

Sale Date	Cash Collected	Deposit Date	Bank Deposit	Variance
2019-05-13	219.25	2019-05-17	219.22	(0.03)
2019-05-14	133.86	2019-05-17	133.86	-
2019-05-15	192.76	2019-05-17	192.76	-
2019-05-16	137.73	2019-05-17	137.61	(0.12)
2019-05-17	204.5	2019-05-20	197.2	(7.30)
2019-05-18	153.05	2019-05-20	153.05	-
2019-05-19	197.81	2019-05-20	197.81	-

[danilang]

Hi @Irfan 

I'll have to think about this one.  All the exceptions that you're finding are slowly turning this into an optimization problem where you're trying to minimize the total variance in any deposit period.  

Dan