Listing values minus the zero amounts
 

Sheet 1
A B D F G
N
Cust # Customer Product Roll Cut
193
193 Joe's Interiors wool 39.99 39.99
193 Joe's Interiors fuzzy 0 0
193 Joe's Interiors shear 28.99 28. 99
193 Joe's Interiors smooth 20.99 20.99

There are many other customers but the above is just an example. Off to the
side I listed out the Cust #s and have a check in place to look for
duplicates. The formula is as follows:
=IF($F2="","",IF($A2=N$1,$F2-ROW()/10^10,""))
To my knowledge the check is looking at the Roll price, and matching to the
Cust # and # in N1 and then giving me the roll price. The idea here is to
list out any DUPS so that the next formula can pull the adjoining
information.

Sheet2
What I am doing on sheet 2 is pluging in the customer number and having it
retrieve all the products for that particular customer. What I do not want it
to do is pull those products that do not have a roll price.

I have the formula below pulling the info from Sheet1
=IF(ROWS($1:1)COUNT(OFFSET('SA278DL Builder
TEST'!$M$1:$M$12966,,MATCH($B$1,'SA278DL Builder
TEST'!$N$1:$FO$1,0))),"",INDEX('SA278DL Builder
TEST'!B$2:B$12966,MATCH(LARGE(OFFSET('SA278DL Builder
TEST'!$M$1:$M$12966,,MATCH($B$1,'SA278DL Builder
TEST'!$N$1:$FO$1,0)),ROWS($1:1)),OFFSET('SA278DL Builder
TEST'!$M$1:$M$12966,,MATCH($B$1,'SA278DL Builder TEST'!$N$1:$FO$1,0)),0)))

So in Cell B1 I plug in the Cust#. The formula above starts in cell B3:K3
and down for about 25 rows. When I type in the Cust# in B1, the cells below
should populate with the customers info, minus the products with a ZERO roll
Price.

Any suggestions? All help is very much appreciated.

Thanks,
JOE

Listing values minus the zero amounts
 

Hope this is useful:
=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL( IF(A1:A20<0,ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"")

Enter with Ctrl+Shifty+Enter (not just enter)

Regards,
Ryan---


--
RyGuy


"Carpet Guy" wrote:

Sheet 1
A B D F G
N
Cust # Customer Product Roll Cut
193
193 Joe's Interiors wool 39.99 39.99
193 Joe's Interiors fuzzy 0 0
193 Joe's Interiors shear 28.99 28. 99
193 Joe's Interiors smooth 20.99 20.99

There are many other customers but the above is just an example. Off to the
side I listed out the Cust #s and have a check in place to look for
duplicates. The formula is as follows:
=IF($F2="","",IF($A2=N$1,$F2-ROW()/10^10,""))
To my knowledge the check is looking at the Roll price, and matching to the
Cust # and # in N1 and then giving me the roll price. The idea here is to
list out any DUPS so that the next formula can pull the adjoining
information.

Sheet2
What I am doing on sheet 2 is pluging in the customer number and having it
retrieve all the products for that particular customer. What I do not want it
to do is pull those products that do not have a roll price.

I have the formula below pulling the info from Sheet1
=IF(ROWS($1:1)COUNT(OFFSET('SA278DL Builder
TEST'!$M$1:$M$12966,,MATCH($B$1,'SA278DL Builder
TEST'!$N$1:$FO$1,0))),"",INDEX('SA278DL Builder
TEST'!B$2:B$12966,MATCH(LARGE(OFFSET('SA278DL Builder
TEST'!$M$1:$M$12966,,MATCH($B$1,'SA278DL Builder
TEST'!$N$1:$FO$1,0)),ROWS($1:1)),OFFSET('SA278DL Builder
TEST'!$M$1:$M$12966,,MATCH($B$1,'SA278DL Builder TEST'!$N$1:$FO$1,0)),0)))

So in Cell B1 I plug in the Cust#. The formula above starts in cell B3:K3
and down for about 25 rows. When I type in the Cust# in B1, the cells below
should populate with the customers info, minus the products with a ZERO roll
Price.

Any suggestions? All help is very much appreciated.

Thanks,
JOE