I have a spreadsheet linked to a database. Column E is "Quantity on hand", column F is "Quantity on Sales order". How can I filter the spreadsheet to show only the rows where the values in column F are greater than the values in column E?

Thanks.

I have a spreadsheet linked to a database. Column E is "Quantity on hand", column F is "Quantity on Sales order". How can I filter the spreadsheet to show only the rows where the values in column F are greater than the values in column E?

Thanks.

1. In cells E2 to E 10 I have:-

1

2

3

4

5

6

7

8

9

2. In cells F2 to F10 I have:-

20

6

5

4

3

15

2

1

11

3. In cell E11 I have:-

=MAX(E2:E10)

 - and this displays:-

9

4. Click in cell:-

E1

 - then:-

Data tab

Sort & Filter group

Click:-

Filter

5. Click on the drop down arrow in cell F1

 - then hover the mouse over:-

Number Filters

 - then click on:-

Greater Than . . .

The:-

Custom AutoFilter

 - window should open.

6. In the window called:-

Custom AutoFilter

 - in the drop down arrow field in its top right hand corner type in:-

9

 - then click on:-

OK

You now get 3 rows returned:-

1              20

6              15

9              11

Does that give you what you want?

• Edited by trip_to_tokyo Wednesday, January 28, 2015 8:17 PM

I have a spreadsheet linked to a database. Column E is "Quantity on hand", column F is "Quantity on Sales order". How can I filter the spreadsheet to show only the rows where the values in column F are greater than the values in column E?

Thanks.

1. In cells E2 to E 10 I have:-

1

2

3

4

5

6

7

8

9

2. In cells F2 to F10 I have:-

20

6

5

4

3

15

2

1

11

3. In cell E11 I have:-

=MAX(E2:E10)

 - and this displays:-

9

4. Click in cell:-

E1

 - then:-

Data tab

Sort & Filter group

Click:-

Filter

5. Click on the drop down arrow in cell F1

 - then hover the mouse over:-

Number Filters

 - then click on:-

Greater Than . . .

The:-

Custom AutoFilter

 - window should open.

6. In the window called:-

Custom AutoFilter

 - in the drop down arrow field in its top right hand corner type in:-

9

 - then click on:-

OK

You now get 3 rows returned:-

1              20

6              15

9              11

Does that give you what you want?

• Edited by trip_to_tokyo Wednesday, January 28, 2015 8:17 PM

No, that doesn't. It shows a mixture of > & < values.

No, that doesn't. It shows a mixture of > & < values.

On the data that I provided can you please tell us what your expected results are? 

 - or alternatively provide some of your own test data and expected results.

Thanks. 

As far as I can see the answer I have given matches the requirements set out in your original posting unless, of course, I have misunderstood something. 

I want to filter this so that it only shows the records where the QuantityOnSalesOrder (Column F) is higher than TotalQuantityOnHand (Column E).

ItemCode	ItemCodeDesc	PurchaseUnitOfMeasure	TotalQuantityOnHand	QuantityOnSalesOrder	QuantityOnPurchaseOrder
020NUTSWASHERS	Nuts &   Washer Kit, Fastenal #W	EA	0	0	0
034BLOWER1011	Blower Heated Pump 110v m3-300	EACH	0	0	0
034BLOWER1012	Variable Speed   Blower System f	EACH	0	0	0
034CAP1000	White Cap for cg air code: fg-	EACH	240	0	0
034CAP1001	Bone Cap for cg   air code: fg-m	EACH	300	0	0
034CAP1002	Chrome Cap for cg air code: fg	EACH	500	0	0

• Edited by TurkR Friday, January 30, 2015 2:32 PM

I want to filter this so that it only shows the records where the QuantityOnSalesOrder (Column F) is higher than TotalQuantityOnHand (Column E).

ItemCode	ItemCodeDesc	PurchaseUnitOfMeasure	TotalQuantityOnHand	QuantityOnSalesOrder	QuantityOnPurchaseOrder
020NUTSWASHERS	Nuts &   Washer Kit, Fastenal #W	EA	0	0	0
034BLOWER1011	Blower Heated Pump 110v m3-300	EACH	0	0	0
034BLOWER1012	Variable Speed   Blower System f	EACH	0	0	0
034CAP1000	White Cap for cg air code: fg-	EACH	240	0	0
034CAP1001	Bone Cap for cg   air code: fg-m	EACH	300	0	0
034CAP1002	Chrome Cap for cg air code: fg	EACH	500	0	0

• Edited by TurkR Friday, January 30, 2015 2:32 PM

The QuantityOnSalesOrder is never higher than the TotalQuantityOnHand so there's nothing to show. 

My spreadsheet has over 5000 records. In it there is. The sample I provided doesn't.

My spreadsheet has over 5000 records. In it there is. The sample I provided doesn't.

How about this then? 

With regard to the original example that I gave I have added in a helper column (column G in my Workbook). 

In cell G2 I have:-

=IF(F2>E2,"greater than","not greater than")

 - I have then copied that down the column. 

If you now do a:-

Data tab 

Sort & Filter group

Filter 

 - on column G and select only the:-

greater than

 - values you get all the rows that (I think!) you want. 

Does that work out for you? 

• Edited by trip_to_tokyo Friday, January 30, 2015 11:54 PM

My spreadsheet has over 5000 records. In it there is. The sample I provided doesn't.

How about this then? 

With regard to the original example that I gave I have added in a helper column (column G in my Workbook). 

In cell G2 I have:-

=IF(F2>E2,"greater than","not greater than")

 - I have then copied that down the column. 

If you now do a:-

Data tab 

Sort & Filter group

Filter 

 - on column G and select only the:-

greater than

 - values you get all the rows that (I think!) you want. 

Does that work out for you? 

• Edited by trip_to_tokyo Friday, January 30, 2015 11:54 PM
• Marked as answer by TurkR 21 hours 54 minutes ago

Yes. Thank you.

Yes. Thank you.

OK - great!