check for a non empty cells in an array

[sameershandilya]

Hi There,


I am trying to check for non empty cells in an array and want to return the corresponding column name. The cells I am checking are in 22*N format. There are multiple non-empty cells and thus multiple column names. I want to have all columns corresponding to the non-empty cells.


Could anyone help?


Thanks

Sameer

 

[Luke M]

Since you didn't give any cell references, I'll have to be a little vague in my response, but using a similar technique to the one discussed here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


If you're creating a vertical list, formula would look like:

=IF(COUNTA(YourArray)>ROWS($A$1:$A1),"",INDEX(RowWithColumnNames,SMALL(IF(YourArray<>"",COLUMN(YourArray)),ROW(A1))))


A horizontal list would be:

=IF(COUNTA(YourArray)>COLUMNS($A$1:$A1),"",INDEX(RowWithColumnNames,SMALL(IF(YourArray<>"",COLUMN(YourArray)),COLUMN(A1))))


Note that both of these are array formulas and need to be confirmed using Ctrl+Shift+Enter.

 

[sameershandilya]

Forming Press Blanking Press Lathe Milling Broach Saw Grinder


5215 5215

5215


5215

5215 5215


Thanks Luke. However It seems like this should work, but I am still stuck at it. I have pasted an example above. The number '5215' is the reference. If a cell has that number, I want to return the name of that columns above. The number could exist in the same column for multiple rows, but I want to return the column name only once.

Does this help?


Thanks

Sameer