Provide list from all columns marked with and "x"

B

BAW

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
 
H

Herbert Seidenberg

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),INDEX(arrayB,,3),
INDEX(arrayB,,4),INDEX(arrayB,,5))
 
B

BAW

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top