Help creating list of names which satisfy critera

[kralc]

Can someone please help with providing a formula which generates a list of text values in multiple columns which satisfy a certain criteria (i.e. a '1' in the row) and ignores the blank cells.

Example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME1[/TD]
[TD]NAME2[/TD]
[TD]NAME3[/TD]
[TD]NAME4[/TD]
[TD]NAME5[/TD]
[TD]NAME6[/TD]
[TD]NAME7[/TD]
[TD]NAME8[/TD]
[TD]NAME9[/TD]
[TD]NAME10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

So creating a formula which would generate a list of Name1, Name5, Name8 and Name10. I will have to repeat this formula for 100+ rows in spreadsheet.

Sorry if this is really simple, but I've spent a lot of time googling to no avail.

Thanks

 

[admiral100]

Hi,

Try this:

Ctrl+Shift+Enter NOT just Enter

A4 =IFERROR(INDEX($A$1:$J$1,SMALL(IF($A$2:$J$2=1,COLUMN($A$2:$J$2)),COLUMNS($F$1:F1))),"")

[TABLE="width: 770"]
<colgroup><col width="70" span="11" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"]G[/TD]
[TD="class: xl63, width: 70"]H[/TD]
[TD="class: xl63, width: 70"]I[/TD]
[TD="class: xl63, width: 70"]J[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]NAME1[/TD]
[TD="class: xl63"]NAME2[/TD]
[TD="class: xl63"]NAME3[/TD]
[TD="class: xl63"]NAME4[/TD]
[TD="class: xl63"]NAME5[/TD]
[TD="class: xl63"]NAME6[/TD]
[TD="class: xl63"]NAME7[/TD]
[TD="class: xl63"]NAME8[/TD]
[TD="class: xl63"]NAME9[/TD]
[TD="class: xl63"]NAME10[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]NAME1[/TD]
[TD="class: xl63"]NAME5[/TD]
[TD="class: xl63"]NAME8[/TD]
[TD="class: xl63"]NAME10[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[kralc]

Thanks, but this doesn't seem to work. It seems to be listing names at random. Can you explain the significance of the last part of the COLUMNS function for F1? Perhaps this is where I'm going wrong...

 

[admiral100]

Did you type Ctrl+Shift+Enter NOT just Enter ?

There is no particular meaning to F you can type any other letter ...
It picks up the next small value, first , second and so on.