Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell total plus and minus amounts | Excel Discussion (Misc queries) | |||
Idenifying and Listing Duplicate Values in a Table? | Excel Discussion (Misc queries) | |||
HOW DO I ADD AMOUNTS FOR DIFERENT VALUES | Excel Worksheet Functions | |||
various values and listing them | Excel Worksheet Functions | |||
... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions |