Formula to check data contingent on multiple constraints

[Dennisss11]

Hi all,

I am struggling to find a formula that returns exactly what I need to filter my data. In the mini-sheet below there is a simplification of the data. For the real data set, there are over 450k rows that need to be checked. However, a formula that is working in the mini-sheet should also work on the full set (I think).

For every specific date, the formula should check the following:
- Is there a "C" and a "P" entry available. If not, those dates should return "". As seen in row 23 and 26 (which I highlighted orange) on these dates there is only either a "P" available or only a "C" respectively.
- If there is a "C" and "P" entry, it should find the "C" closest to number stated in AP6 and the "P" entry closest to number stated in AO6.
- The result should be in column AQ as shown. So every date that matches the first requirement, gets only one "C" entry that we "Keep" and one "P" entry that we "Keep".

Hopefully it is clear what I am trying to accomplish. Thanks in advance for taking the time!

All Sell 1DTE.xlsx
	AM	AN	AO	AP	AQ	AR
4
5			Put	Call
6		Preferred Delta (closest to)	-0.2	0.2
7
8		Raw Data			Formula needed
9		Date	Put/Call	Delta	Check
10		7/23/15	C	0.44
11		7/23/15	P	-0.44
12		7/23/15	C	0.33
13		7/23/15	P	-0.33
14		7/23/15	C	0.21	Keep
15		7/23/15	P	-0.21	Keep
16		7/23/15	C	0.11
17		7/23/15	P	-0.11
18		7/24/15	C	0.23
19		7/24/15	P	-0.23
20		7/24/15	C	0.19	Keep
21		7/24/15	P	-0.19	Keep
22		7/24/15	C	0.99994
23		7/25/15	P	-0.21
24		7/26/15	C	0.4	Keep
25		7/26/15	P	-0.4	Keep
26		7/27/15	C	0.19
27		7/29/15	C	0.001
28		7/29/15	P	-0.001
29		7/29/15	C	0.8
30		7/29/15	P	-0.8
31		7/29/15	C	0.2
32
Sheet1

 

[Peter_SSs]

Duplicate to: Filter large amount of data by multiple constraints
In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.