Selecting all values in a column that meet a criterion and returning the value from the same row in

Eric

Not sure whether I understand your query as intended.
I'll assume as a manageable set of data as example:

{1,0.04,0.6;2,0.2,0.009;3,0.4,0.01;4,0.7,0.7;5,0.1,0.2;6,0.9,0.04}

This set occupies F2:H7. Translated to your situation, you'll need to insert a number of columns such that your data also starts in F from F2 on.

In A1:A2 enter what follows:

{"CRIT";0.5}

In A3 enter: =COUNT(F:F)+1

In B2 enter: =IF(ROW()>$A$3,"",IF(SUM(OR($G$2:$G$7>$A$2,$H$2:$H$7>$A$2))>=1,F2,0)) [ copy down this as far as needed ]

In C2:C3 enter what follows:

{"List1";"List2"} [ just labels ]

In D2 enter: =ADDRESS(ROW(B2),COLUMN(B2))&":"&ADDRESS(COUNT(B:B)+1,COLUMN(B2))

In D3 enter: =ADDRESS(ROW(E2),COLUMN(E2))&":"&ADDRESS(COUNT(B:B)+1,COLUMN(E2))

In E2 array-enter: =IF(ROW()-ROW(INDIRECT($D$3))+1>ROWS(INDIRECT($D$2))-COUNTIF(INDIRECT($D$2),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($D$2)<>0,ROW(INDIRECT($D$2)),ROW()+ROWS(INDIRECT($D$2)))),ROW()-ROW(INDIRECT($D$3))+1),COLUMN(INDIRECT($D$2))))) [ copy down this array formula as far as needed ]

In column B from B2 on, you see as result:

{1;0;0;4;0;6}

In column E from E2 on, you see, I hope, what you wanted:

{1;4;6;"";"";""} [ effectively: 1,4,6 ]

Aladin

=================================

Thanks and a second clarification

Aladin,

Thank you very much for your help. I'm not sure whether I understand my query either, LOL.
It seems as if you are pretty close in your design to what I was looking for with some exceptions that may not be worth discussing.

I followed your instructions and built a spreadsheet with the 'dummy' data. Unfortunately both Column (B) and Column (E) give me the same values
1.00
0.60
0.20
3.00
0.01
0.70
essentially the values from Column (F). Column (E) is also not blanking values <.5.

You've already put a lot into this and I'm sure I'll be able to put what you've shown me to good use, so thanks again, and there's certainly no need for you to pursue this further. However, if you are still interested, see below.

To clarify on my query (if only for morbid curiosity), let's say there are 4 columns (A,B,C,and D). Call Column (A) "Reference", call Column (B) "Stat result 1", Column (C) "Stat result 2", and Column (D) "Stat result 3". In each of the three "Stat" columns I am looking for values >.5. Each column has a fixed number of results/rows, but will have a variable number of results/rows >.5, and furthermore these values will occur at different rows for different columns. The output I want is the "reference" value from Column (A) for every row within each column at which there was a value >.5. Therefore the total number of output columns (call them "sig1", "sig2", and "sig3" should equal the number of "stat" columns (in this case 3) because I dont want the results from different columns "mixed".

I will attempt to describe the design with example data below:
columns: reference;stat1, stat2, stat3, sig1, sig2, sig3
numbers:
1000,.7,.2,.3,1000,2001,2112
2112,.3,.4,.6,5000,3000,8000
2001,.1,.8,.4," ",5000," "
5000,.6,.7,.3
3000,.3,.9,.1
8000,.4,.2,.7
Thanks again for your attention

Re: Thanks and a second clarification

Eric,

Here is your data set. It occupies A1:D6.

{1000,0.7,0.2,0.3;2112,0.3,0.4,0.6;2001,0.1,0.8,0.4;5000,0.6,0.7,0.3;3000,0.3,0.9,0.1;8000,0.4,0.2,0.7}

In E1 enter: =IF(B$1:B$6>0.5,$A$1:$A$6,0)

Copy this across to G1 then down as far as needed.

Here is the result that you see in E1:G6.

[1] {1000,0,0;0,0,2112;0,2001,0;5000,5000,0;0,3000,0;0,0,8000}

In fact, the whole thing is similar to what I described in my first post. The only difference is that we now have 3 result columns, each of which must be further processed to get ("no mixing"):

[2] {1000,2001,2112;5000,3000,8000;" ",5000," "}

Since I described how this can be done in my first post, I leave it to you. One caveat though: you'll need at least one additional column to apply the array-formula of the previous post.

Maybe you can use a manual procedure to eliminate the cells with zeroes to get [2].

Note. You said you had some problems in implementing the proc of the previous post. I can send you the relevant workbook if you so desire.

================== ,

plz send me the worksheet

Aladin,

Thanks again for your help, I'm still not getting through this quite right, so Id appreciate the worksheet if you get the time.

Ignore previous post, I got it! (finally)

If I reversed the greater than symbol on this line
In B2 enter: =IF(ROW()>$A$3,"",IF(SUM(OR($G$2:$G$7>$A$2,$H$2:$H$7>$A$2))>=1,F2,0))
to
In B2 enter: =IF(ROW()<$A$3,"",IF(SUM(OR($G$2:$G$7>$A$2,$H$2:$H$7>$A$2))>=1,F2,0))
then everything comes out- thanks again for all the work!