Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide list from all columns marked with and "x"
I would like to build a list of "column labels" from all columns marked with
an "x" for a specific row. In the example below I want to received the results shown in column B. A B C D E F G Apples Oranges Beef Banana Carrot Fruit Apples, Oranges, Banana x x x Veggie Carrot x Meat Beef x Any help would be much appreciated..thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide list from all columns marked with and "x"
Select the 1x5 array of column labels and name it items
Select the 3x5 array of x's and name it arrayX Insert Name Define arrayB Refers to: =IF(arrayX="x",items,"") Select the three cells in column B and enter this array formula with Ctrl+Shift+Enter =CONCATENATE(INDEX(arrayB,,1),INDEX(arrayB,,2),IND EX(arrayB,,3), INDEX(arrayB,,4),INDEX(arrayB,,5)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide list from all columns marked with and "x"
Herbert,
Thanks, your solution is working as expected, however, I was wondering if there is away to enter the "concatenate" statement using looping logic instead of hardcoding each column? The number of items I will be tracking by column is around 75 and will be growing. The number of rows will grow as well as I add more people to track the items against. I assume I can create the arrays larger than needed to allow for growth but the "Concatenate" statement will be pretty lengthy as designed. Brent "Herbert Seidenberg" wrote: Select the 1x5 array of column labels and name it items Select the 3x5 array of x's and name it arrayX Insert Name Define arrayB Refers to: =IF(arrayX="x",items,"") Select the three cells in column B and enter this array formula with Ctrl+Shift+Enter =CONCATENATE(INDEX(arrayB,,1),INDEX(arrayB,,2),IND EX(arrayB,,3), INDEX(arrayB,,4),INDEX(arrayB,,5)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|