Immediately Previous 5 Dates before specified Date and their corresponding names WITH THERE BEING DUPLICATE DATES.

x0nar

New Member
Joined
May 10, 2016
Messages
34
I'm trying to work on a formula to get the 5 nearest transactions before the specified date.
I've been able to draft the following formula


Where
Code:
Range for Dates in Source Table = $C$3:$C$50
Range for Names in Source Table = $D$3:$D:$50
Reference Date (Specified Date) = $F$1
1st Row for the output of closest dates = F3
1st Row for the output of names matching closest date= G3

In Cell F3 I input the following formula to get the closest date before the specified date
Code:
IFERROR(INDEX(C$3:C$50,MATCH(SMALL(IF($F$1>=$C$3:$C$50,$F$1-$C$3:$C$50,""),ROWS($F$3:F3)),$F$1-$C$3:$C$50,0)),"")


In Cell G3 I input the following formula to get the closest name corresponding to closest date before the specified date.
Code:
IFERROR(INDEX(D$3:D$50,MATCH(SMALL(IF($F$1>=$C$3:$C$50,$F$1-$C$3:$C$50,""),ROWS($G$3:G3)),$F$1-$C$3:$C$50,0)),"")


After inserting the formulas I drag them down to G7 so that the SMALL AND ROWS function act as an ascending order sort, and find the nth closest day. eg. For Row F4, it will find the 2nd closest date before the specified date.

Using the above formulas I have been able to get the list of dates and amounts. However the above function creates a problem if there are duplicate dates in the previous 5 dates interval. What happens in such a case, that it returns the duplicate date in the F row correctly, however while in G row, it returns the same name for dates. This I believe is due to match function recognizing only the first match and then returning the name.

Eg;
Consider that our specified date is 15/02/18. And the actual previous 5 transactions are as follows

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]14/02/18[/TD]
[TD]ZYX[/TD]
[/TR]
[TR]
[TD]13/02/18[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]13/02/18[/TD]
[TD]ASD[/TD]
[/TR]
[TR]
[TD]11/02/18[/TD]
[TD]NYT[/TD]
[/TR]
[TR]
[TD]10/02/18[/TD]
[TD]XSD[/TD]
[/TR]
</tbody>[/TABLE]

Now the formula inputted should output the exact same table as above. However it returns the table as

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]14/02/18[/TD]
[TD]ZYX[/TD]
[/TR]
[TR]
[TD]13/02/18[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]13/02/18[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]11/02/18[/TD]
[TD]NYT[/TD]
[/TR]
[TR]
[TD]10/02/18[/TD]
[TD]XSD[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, the value ABC is posted twice instead of ABC and ASD. This I believe is due to the Match function returning the value of first most row it matches.

The only way this can be overcome is if there was a possibility to get the row numbers of the 5 previous transactions directly from the SMALL FUNCTION.
Hence I wanted to ask if there was a way to do so.
Thanks.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top