Copy rows with Qty>0

0
votes

I would like to create an Excel formula (without using VBA) to fill a sheet named "Bill" based on another sheet named "Prescription".

I have for example 10 rows in the sheet "Prescription" with columns Title, Quantity, Price and Total. I want to copy the rows where Quantity>0 to the sheet "Bill" without having any blank rows.

I know it is easy to do with VBA, all I have to do is to copy every rows (including blank rows), set up an autofilter to hide 0s and then add some VBA to refresh the filter, but I'm pretty sure I could do it without VBA (maybe with an array-formula or something to get the Nth non-0 value).

So this is "Prescription":

Title|Qty|Price| Total
----------------------
Test1|  0|99.99|     0
Test2|  1|99.99| 99.99
Test3|  0|99.99|     0
Test4|  3|99.99|299.97

I want a formula to get in another sheet:

Title|Qty|Price| Total
----------------------
Test2|  1|99.99| 99.99
Test4|  3|99.99|299.97
2
excelexcel-formulaexcel-2010

2 Answers

1
votes

How about MS Query?:

SELECT * FROM [Prescription$] AS S WHERE S.Qty> 0

Use Data->Get External Data->MS Query or u can also you a simple Add-In I made: http://blog.tkacprow.pl/excel-tools/excel-sql-add-in-free/

Once you execute the query once you can refresh it at any time by right-clicking and hitting refresh on the Query Table.

0
votes

Finally found an array-formula that does the job from Microsoft KB database: http://support.microsoft.com/kb/101167

The final formula:

={IFERROR(INDEX(Prescription!$I$1:$I$100,SMALL(IF(ISEMPTY(Prescription!$K$1:$K$100),"",ROW(Prescription!$K$1:$K$100)),ROW(2:2))-ROW(Prescription!$K$1:$K$100)+1),"")}