show a filter of a range
 

I have a pricing grid which has labels in column A, quantities in column B,
rates in column C, and total price in column D. There are about 60 rows.

Typically, the finished result only uses ~20 of the items. What is the best
way to show a summary in column G that list only the labels which have been
assigned quantities?

thanks in advance,

Andy

show a filter of a range
 

Assume source data as described in row2 down
with key col B = quantities

In F2: =IF(AND(ISNUMBER(B2),B20),ROW(),"")
Leave F1 empty

In G2:
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(A:A,SMALL(F:F,R OWS($1:1))))
Copy F2:G2 down to cover the max expected extent of source data, say down to
G200? Minimize col F. Col G will dynamically return the required results, ie
the labels from col A with assigned quantities (where qty 0), with all
labels neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"AJB" wrote:
I have a pricing grid which has labels in column A, quantities in column B,
rates in column C, and total price in column D. There are about 60 rows.

Typically, the finished result only uses ~20 of the items. What is the best
way to show a summary in column G that list only the labels which have been
assigned quantities?

show a filter of a range
 

In F2: =IF(AND(ISNUMBER(B2),B20),ROW(),"")
Leave F1 empty

In G2:
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(A:A,SMALL(F:F,R OWS($1:1))))
Copy F2:G2 down to cover the max expected extent of source data. Minimize
col F. Col G will dynamically return what you seek, ie only the labels in col
A with assigned quantities in col B (0), all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"AJB" wrote:
I have a pricing grid which has labels in column A, quantities in column B,
rates in column C, and total price in column D. There are about 60 rows.

Typically, the finished result only uses ~20 of the items. What is the best
way to show a summary in column G that list only the labels which have been
assigned quantities?

show a filter of a range
 

Thanks Max! That works like a charm (and I wouldn't be able to come up with
that myslef!)

"Max" wrote:

In F2: =IF(AND(ISNUMBER(B2),B20),ROW(),"")
Leave F1 empty

In G2:
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(A:A,SMALL(F:F,R OWS($1:1))))
Copy F2:G2 down to cover the max expected extent of source data. Minimize
col F. Col G will dynamically return what you seek, ie only the labels in col
A with assigned quantities in col B (0), all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"AJB" wrote:
I have a pricing grid which has labels in column A, quantities in column B,
rates in column C, and total price in column D. There are about 60 rows.

Typically, the finished result only uses ~20 of the items. What is the best
way to show a summary in column G that list only the labels which have been
assigned quantities?

show a filter of a range
 

Great to hear. Thanks for the high five.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"AJB" wrote in message
...
Thanks Max! That works like a charm (and I wouldn't be able to come up
with
that myself!)