Creating a name list from if quantity value is in adjacent cell on
 

Hi

I have a complete product list with all the product numbers in Colum A, and
the quantities in Colum B.

On a separate sheet I have a smaller table which I would like to populate
with the products that have a quantity value next to them only.

i.e.

Product A 1
Product B 0
Product C 0
Product D 5
Product E 25
Product F 0
Product G 2

Becomes

Product A
Product D
Product E
Product G

I know of many ways to make a macro do the task for me but I really dislike
them and would prefer to use a function string.

Thanks for your help in advance

Jive

Creating a name list from if quantity value is in adjacent cell on
 

Assuming you are starting your data in D1. Use the following *array*
formula.

=IF(ISERROR(SMALL(IF($B$1:$B$1000,ROW($B$1:$B$100 )-ROW($B$1)+1),ROW()-
ROW($D$1)+1))),"",INDEX($A$1:$A$100,SMALL(IF($B$1: $B$1000,ROW($B$1:$B
$100)-ROW($B$1)+1),ROW()-ROW($D$1)+1))))

This must be committed with Shift+Ctrl+Enter. You can copy down until
you get blanks.

HTH
Kostis Vezerides



On Jun 7, 7:07 pm, Jive wrote:
Hi

I have a complete product list with all the product numbers in Colum A, and
the quantities in Colum B.

On a separate sheet I have a smaller table which I would like to populate
with the products that have a quantity value next to them only.

i.e.

Product A 1
Product B 0
Product C 0
Product D 5
Product E 25
Product F 0
Product G 2

Becomes

Product A
Product D
Product E
Product G

I know of many ways to make a macro do the task for me but I really dislike
them and would prefer to use a function string.

Thanks for your help in advance

Jive

Creating a name list from if quantity value is in adjacent cel
 

Thank you

Was the first time i have used an Array formula and it works like a charm.

Easy to implement and exactly what i needed.

"vezerid" wrote:

Assuming you are starting your data in D1. Use the following *array*
formula.

=IF(ISERROR(SMALL(IF($B$1:$B$1000,ROW($B$1:$B$100 )-ROW($B$1)+1),ROW()-
ROW($D$1)+1))),"",INDEX($A$1:$A$100,SMALL(IF($B$1: $B$1000,ROW($B$1:$B
$100)-ROW($B$1)+1),ROW()-ROW($D$1)+1))))

This must be committed with Shift+Ctrl+Enter. You can copy down until
you get blanks.

HTH
Kostis Vezerides



On Jun 7, 7:07 pm, Jive wrote:
Hi

I have a complete product list with all the product numbers in Colum A, and
the quantities in Colum B.

On a separate sheet I have a smaller table which I would like to populate
with the products that have a quantity value next to them only.

i.e.

Product A 1
Product B 0
Product C 0
Product D 5
Product E 25
Product F 0
Product G 2

Becomes

Product A
Product D
Product E
Product G

I know of many ways to make a macro do the task for me but I really dislike
them and would prefer to use a function string.

Thanks for your help in advance

Jive

Creating a name list from if quantity value is in adjacent cel
 

You are welcome. Thanks for the feedback.

Kostis

On Jun 8, 11:24 am, Jive wrote:
Thank you

Was the first time i have used an Array formula and it works like a charm.

Easy to implement and exactly what i needed.

"vezerid" wrote:
Assuming you are starting your data in D1. Use the following *array*
formula.

=IF(ISERROR(SMALL(IF($B$1:$B$1000,ROW($B$1:$B$100 )-ROW($B$1)+1),ROW()-
ROW($D$1)+1))),"",INDEX($A$1:$A$100,SMALL(IF($B$1: $B$1000,ROW($B$1:$B
$100)-ROW($B$1)+1),ROW()-ROW($D$1)+1))))

This must be committed with Shift+Ctrl+Enter. You can copy down until
you get blanks.

HTH
Kostis Vezerides

On Jun 7, 7:07 pm, Jive wrote:
Hi

I have a complete product list with all the product numbers in Colum A, and
the quantities in Colum B.

On a separate sheet I have a smaller table which I would like to populate
with the products that have a quantity value next to them only.

i.e.

Product A 1
Product B 0
Product C 0
Product D 5
Product E 25
Product F 0
Product G 2

Becomes

Product A
Product D
Product E
Product G

I know of many ways to make a macro do the task for me but I really dislike
them and would prefer to use a function string.

Thanks for your help in advance

Jive

Creating a name list from if quantity value is in adjacent cell on
 

=IF(ISERR(SMALL(IF(quan0,ROW(INDIRECT("1:"&ROWS(q uan)))),ROWS($1:1))),"",INDEX(prod,SMALL(IF(quan0 ,ROW(INDIRECT("1:"&ROWS(quan)))),ROWS($1:1))))

ctrl+shift+enter, not just enter


"Jive" wrote:

Hi

I have a complete product list with all the product numbers in Colum A, and
the quantities in Colum B.

On a separate sheet I have a smaller table which I would like to populate
with the products that have a quantity value next to them only.

i.e.

Product A 1
Product B 0
Product C 0
Product D 5
Product E 25
Product F 0
Product G 2

Becomes

Product A
Product D
Product E
Product G

I know of many ways to make a macro do the task for me but I really dislike
them and would prefer to use a function string.

Thanks for your help in advance

Jive