Looking for the correct excel formula

[rockyboxboa]

I have a spreadsheet with the following values. Column A is the item number
and column B is the qty.

TAB1
A B
1 Item Number Qty
2 500cc 0
3 1500cc 100
4 1500sp 0
5 3000cc 100
6 16000cc 100

These figures are pulled out of the database when you enter the purchase
order. On another tab of the spreadsheet is the packing list. I need a
formula that will reference the previous worksheet and ignore the 0 values.
So I need the packing list to look like this:

TAB2
A B
1 Item Number Qty
2 1500cc 100
3 3000cc 100
4 16000cc 100
5
6

Now I have been using the following formula in cell A2 that reads:

=if(tab1!B2>0,tab1!A2,if(tab1!B3>0,tab1!A3,if(tab1B4>0,tab1!A4,if(tab1!B5>0,tab1!A5,if(tab1!B6>0,tab1!A6,"")

Now this formula works great for cell A2 on tab 2. The problem arises for
cell A3. If I do the same formula as above, I come up with the same answer as
cell A2. I need to have the same formula AND somehow exclude the result from
cell A2.

In cell A4, I need to have the same formula and exclude the result from cell
A2 and A3.

And so on and so forth.

Anybody have any ideas how I could do this?

 

[T. Valko]

Try this...

On Sheet2...

Sheet2 A1:B1 = headers = Item Number, Qty

Enter this formula in C1. This will return the count of QTY from Sheet1 that
is >0.

=COUNTIF(Sheet1!B:B,">0")

Enter this array formula** in A2:

=IF(ROWS(A$2:A2)>C$1,"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!B$2:B$6>0,ROW(Sheet1!B$2:B$6)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Assuming that the items are unique...

Enter this formula in B2:

=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!B:B))

Select both A2 and B2 and drag copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"(e-mail address removed)"

 

[Jacob Skaria]

In Sheet 2 cell A1 enter the below formul and copy/drag to cell B1. Then copy
down as required...Please note that this is an array formula.You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=IF(COUNTIF(Sheet1!$B$1:$B$1000,">0")+1<ROW(Sheet1!A1),"",
INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000<>0,
ROW($B$1:$B$1000)),ROW(Sheet1!A1))))

If this post helps click Yes