Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell total plus and minus amounts Terry Excel Discussion (Misc queries) 2 August 30th 07 03:18 PM
Idenifying and Listing Duplicate Values in a Table? PBJ Excel Discussion (Misc queries) 0 October 24th 06 05:03 PM
HOW DO I ADD AMOUNTS FOR DIFERENT VALUES Paulo Coelho Excel Worksheet Functions 2 January 16th 06 01:46 AM
various values and listing them donjuan66 Excel Worksheet Functions 2 November 13th 05 01:55 PM
... Count, <<< Positive Values minus Negative Values >>> ... Dr. Darrell Excel Worksheet Functions 4 September 8th 05 01:36 PM


All times are GMT +1. The time now is 06:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"