| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter data in an adjacent cell based on a list selection? | Excel Worksheet Functions | |||
| Can I add a quantity to every cell in a column using a formula? | Excel Discussion (Misc queries) | |||
| Trying to generate a quantity from a list of doors with varying at | Excel Discussion (Misc queries) | |||
| Creating a drop down list to change decimal value in another cell | Excel Worksheet Functions | |||
| Creating drop down list for multiple cell ranges | Excel Discussion (Misc queries) | |||
Linear Mode
