Hello,
Can someone help me to figure out a formula that find the closest match based on two criterias?
I have two sets of data. The date has to match each other in both sets. The amount may match, but can differ within +/- 10. I want to find the match or the closest match. How can this be solved? Example sets:
Set 1:
[TABLE="width: 165"]
<tbody>[TR]
[TD]Date[/TD]
[TD] Amount [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 6 519 [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 15 741 [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 3 164 [/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 29 756 [/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 6 577 [/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 15 741 [/TD]
[/TR]
[TR]
[TD="align: right"]06.06.2014[/TD]
[TD] 4 148 [/TD]
[/TR]
[TR]
[TD="align: right"] 06.06.2014 [/TD]
[TD] 12 949
[/TD]
[/TR]
[TR]
[TD]Set 2:
[TABLE="width: 165"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 6 520 [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 15 741[/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 3 162[/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 29 751[/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 6 577[/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 15 739[/TD]
[/TR]
[TR]
[TD="align: right"]06.06.2014[/TD]
[TD] 4 149 [/TD]
[/TR]
[TR]
[TD="align: right"]06.06.2014[/TD]
[TD] 12 952
[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
Can someone help me to figure out a formula that find the closest match based on two criterias?
I have two sets of data. The date has to match each other in both sets. The amount may match, but can differ within +/- 10. I want to find the match or the closest match. How can this be solved? Example sets:
Set 1:
[TABLE="width: 165"]
<tbody>[TR]
[TD]Date[/TD]
[TD] Amount [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 6 519 [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 15 741 [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 3 164 [/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 29 756 [/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 6 577 [/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 15 741 [/TD]
[/TR]
[TR]
[TD="align: right"]06.06.2014[/TD]
[TD] 4 148 [/TD]
[/TR]
[TR]
[TD="align: right"] 06.06.2014 [/TD]
[TD] 12 949
[/TD]
[/TR]
[TR]
[TD]Set 2:
[TABLE="width: 165"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 6 520 [/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 15 741[/TD]
[/TR]
[TR]
[TD="align: right"]02.06.2014[/TD]
[TD] 3 162[/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 29 751[/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 6 577[/TD]
[/TR]
[TR]
[TD="align: right"]03.06.2014[/TD]
[TD] 15 739[/TD]
[/TR]
[TR]
[TD="align: right"]06.06.2014[/TD]
[TD] 4 149 [/TD]
[/TR]
[TR]
[TD="align: right"]06.06.2014[/TD]
[TD] 12 952
[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]