Filter data on the basis of some condition

[Vishaal]

HI, we have the following data, and want to filter the data on behalf of the following points. (we have excel 2007 and 2010)

(1) Order Not Received and target date is passed
(2) Order Received but balance Qty difference is 50 or more than 50

New Microsoft Excel Worksheet - Copy.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Order	P Name	TG	Qty		Recd. Date	Recd. Qty
2	27/4/2010	Given	TEST 1	4/29/2010	500		4/27/2010	250	250
3	9/5/2010	Given	TEST 2	5/16/2010	200		5/20/2010	160	40
4	9/5/2010	Given	TEST 3	5/16/2010	150		5/17/2010	100	50
5	10/5/2010	Given	TEST 4	5/17/2010	600				600
6	10/5/2010	Given	TEST 5	5/17/2010	254		5/16/2010	254
7	12/5/2010	Given	TEST 6	5/19/2010	654		5/18/2010	654
8	12/5/2010	Given	TEST 7	5/19/2010	951		6/10/2010	950	1
9	12/5/2010	Given	TEST 8	5/19/2010	757		6/15/2010	757
Sheet4

Cell Formulas
Range	Formula
I8,I2:I5	I2	=E2-H2

Help please

 

[kweaver]

Is TG the target date?

One option might be to have a J column with this:

=IF(AND(I2>0,D2>G2),"x","")

filled down to mark where I is still missing product and the TG is past.
Similarly, you could write another formula for the 2nd set of conditions.
Then, filter on either J or K.

??

 

[kevin9999]

Same as @kweaver 's approach, but assuming you wanted both conditions tested in the same column (not totally clear from your post)

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Order	P Name	TG	Qty		Recd. Date	Recd. Qty		filter
2	27/04/2010	Given	TEST 1	4/29/2010	500		4/27/2010	250	250	X
3	9/05/2010	Given	TEST 2	5/16/2010	200		5/20/2010	160	40
4	9/05/2010	Given	TEST 3	5/16/2010	150		5/17/2010	100	50	X
5	10/05/2010	Given	TEST 4	5/17/2010	600				600	X
6	10/05/2010	Given	TEST 5	5/17/2010	254		5/16/2010	254
7	12/05/2010	Given	TEST 6	5/19/2010	654		5/18/2010	654
8	12/05/2010	Given	TEST 7	5/19/2010	951		6/10/2010	950	1
9	12/05/2010	Given	TEST 8	5/19/2010	757		6/15/2010	757
Sheet1

Cell Formulas
Range	Formula
I2:I5,I8	I2	=E2-H2
J2:J9	J2	=IF(OR(AND(G2="",D2<TODAY()),AND(G2<>"",(E2-H2)>=50)),"X","")

 

[kvsrinivasamurthy]

J1=" Order Not Received and target date is passed"
K1=" Order Received but balance Qty difference is 50 or more than 50"
In J2
=AND(G2="",D2>TODAY())
In K2
=AND(G2<>"",G2<=TODAY(),I2>=50)
Copy down both
For condition 1 filter data for Column J=TRUE
For condition 2 filter data for Column K=TRUE

 

[Vishaal]

Thanks

kvsrinivasamurthy​

kweaver​

kevin9999​

got the desired result

 

[kevin9999]

Thanks

kvsrinivasamurthy​

kweaver​

kevin9999​

got the desired result

Glad we could help, and thanks for the feedback